Having a strange problem. I am trying to pull data from a legacy MySQL system into a SQL Server 2012 database. The issue is that when I try to execute the query as dynamic sql, I get an error; but when I print out the variable value and then run it, it works fine.DECLARE @BeginPullDATE VARCHAR(20) ,@TSQL VARCHAR(8000);set @BeginPullDATE = '2012-11-01'; SELECT @TSQL = 'SELECT RECNUM, VENDNO, PARTNO, CUSTNO, EVENTNO, [DATE], EXTADJ, VARMOV, VARSAL, VARPUR, INFLATTAG, INFLATSTK, EXTRECON, TS, BRANCH, PARENTCLASS, CLASS, CATEGORY, SKEY, DOCKEY, IKEY, [EVENT] FROM OPENQUERY(mysql, ''select * from sys.msevents WHERE DATE > ''''' + @BeginPullDATE + ''''' limit 100'')' PRINT @TSQL; EXEC @TSQL;
The PRINT @TSQL line gives me the result SELECT RECNUM, VENDNO, PARTNO, CUSTNO, EVENTNO, [DATE], EXTADJ, VARMOV, VARSAL, VARPUR, INFLATTAG, INFLATSTK, EXTRECON, TS, BRANCH, PARENTCLASS, CLASS, CATEGORY, SKEY, DOCKEY, IKEY, [EVENT] FROM OPENQUERY(mysql, 'select * from sys.msevents WHERE DATE > ''2012-11-01'' limit 100')
When I copy the print output into a query window in SMSS and run it, it works. But it doesn't work when I use EXEC (@TSQL).Anyone have any idea why the query will work when run as a query but not as dynamic sql?StonebreakerThe greatest obstacle to discovery is not ignorance - it is the illusion of knowledge. -Daniel Boorstin