I
Isaac Majek
Guest
Please I need help passing a parameter in SSIS into an 'OPENQUERY', to test the query I'm using the script below but getting errors:
Script:
DECLARE @TSQL varchar(8000), @Date varchar(11)
SELECT @Date = '28 Nov 2018'
SELECT @TSQL = 'SELECT * FROM OPENQUERY([TEST], ''SELECT * FROM PUB.TEST
WHERE Test_Date >= ''''' + @Date + ''''''')'
EXEC (@TSQL)
Error:
OLE DB provider "MSDASQL" for linked server "TEST" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Invalid date string (7497)".
Msg 7321, Level 16, State 2, Line 4
An error occurred while preparing the query "SELECT * FROM PUB.TEST
WHERE Test_Date >= '28 Nov 2018'" for execution against OLE DB provider "MSDASQL" for linked server "TEST".
Script in SSIS OLE DB Source should look like this:
DECLARE @TSQL varchar(8000), @Date varchar(11)
SELECT @Date = ?
SELECT @TSQL = 'SELECT * FROM OPENQUERY([TEST], ''SELECT * FROM PUB.TEST
WHERE Test_Date >= ''''' + @Date + ''''''')'
EXEC (@TSQL)
Continue reading...
Script:
DECLARE @TSQL varchar(8000), @Date varchar(11)
SELECT @Date = '28 Nov 2018'
SELECT @TSQL = 'SELECT * FROM OPENQUERY([TEST], ''SELECT * FROM PUB.TEST
WHERE Test_Date >= ''''' + @Date + ''''''')'
EXEC (@TSQL)
Error:
OLE DB provider "MSDASQL" for linked server "TEST" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Invalid date string (7497)".
Msg 7321, Level 16, State 2, Line 4
An error occurred while preparing the query "SELECT * FROM PUB.TEST
WHERE Test_Date >= '28 Nov 2018'" for execution against OLE DB provider "MSDASQL" for linked server "TEST".
Script in SSIS OLE DB Source should look like this:
DECLARE @TSQL varchar(8000), @Date varchar(11)
SELECT @Date = ?
SELECT @TSQL = 'SELECT * FROM OPENQUERY([TEST], ''SELECT * FROM PUB.TEST
WHERE Test_Date >= ''''' + @Date + ''''''')'
EXEC (@TSQL)
Continue reading...