SQL SELECT * FROM OPENROWSET with Variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL SELECT * FROM OPENROWSET with Variable

Author

Topic

Imelda
Starting Member

3 Posts

Posted - 2013-05-17 : 07:15:00

I am trying to pass a variable into a SELECT statement in OPENROWSET but I keep getting an error `DECLARE @dDateTIME DATE

SET @dDateTIME = (SELECT SalesDate FROM dbo.SalesDate)

INSERT INTO dbo.tblSales SELECT * FROM OPENROWSET('MSDASQL', 'dsn=mydsn;uid=myid;pwd=mypwd;', 'SELECT ID, TranDate, ProductID, CostValue, SalesValue, QtySold, FROM tblSales WHERE TranDate='' + @dDateTIME + ''')`

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 07:19:19

you should be using sp_executesql for this

see

http://msdn.microsoft.com/en-us/library/ms175170(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-17 : 07:21:34

Check this?

TranDate=''' + @dDateTIME + ''')'

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources