SQL SELECT * FROM OPENROWSET with variable

Question

I try to pass a variable to a SELECT statement in OPENROWSET, but I get an error all the time

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 + ''') DECLARE @dDateTIME DATE SET @dDateTIME = (SELECT SalesDate FROM dbo.SalesDate) DECLARE @SQL NVARCHAR(1024) = 'SELECT ID, TranDate, ProductID, CostValue, SalesValue, QtySold, FROM tblSales WHERE TranDate=''' + CAST(@dDateTIME AS VARCHAR(64)) + '''' DECLARE @RunSQL NVARCHAR(max) SET @RunSQL= 'SELECT * FROM OPENROWSET (''MSDASQL'', ''dsn=mydsn;uid=myid;pwd=mypwd;'',''EXEC @SQL'')'` 

What syntax do I use to apply to @SQL ?

The error I am getting is:

Error: OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Pervasive] [ODBC client interface] [LNA] [Pervasive] [ODBC engine interface] Predicate error: TranDate = '(SELECT @dDATETIME) '"

Answer 1

Your variable does not concatenate with the string (its ''' to close the string with ' ) to fix this (and perform the necessary type conversion):

 DECLARE @SQL NVARCHAR(1024) = 'SELECT ID, TranDate, ProductID, CostValue, SalesValue, QtySold, FROM tblSales WHERE TranDate=''' + CAST(@dDateTIME AS VARCHAR(64)) + '''' 

In addition, you cannot use an expression or variable with OPENROWSET , so you will need to call it through EXEC() / sp_executeSQL , see Using a variable in an OPENROWSET query

Answer 2

You can dynamically create a SQL statement and execute this command.

 DECLARE @dDateTIME DATE, @RunSQL NVARCHAR(max) SET @dDateTIME = (SELECT SalesDate FROM dbo.SalesDate) SELECT @RunSQL = '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=''''' + CONVERT(nvarchar, @dDateTIME, 112) + ''''''')' --PRINT @RunSQL EXEC sp_executesql @RunSQL