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 + ''')
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 get is:
The error is :OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Error in predicate: TranDate = '(SELECT @dDateTIME)' "
Your variable is not being concatenated to the string (its '''
to close a string with a '
) to correct 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)) + ''''
Additionally you cannot use an expression or variable with OPENROWSET
so you are going to need to call it via EXEC()
/sp_executeSQL
, see; Using a Variable in OPENROWSET Query