0

I am using LinkedServer in a dynamic query to access an IBM Informix DB directly via SQL Server. I am unable to figure out why is the dynamic query not executing in ms sql server while its perfectly fine at informix. Please advise I am stuck badly.

THE SCRIPT:

 DECLARE @TSQL varchar(8000), @VAR varchar(MAX)
 SELECT  @VAR = '2017-10-13 00:00:00'
 SELECT  @TSQL = 'SELECT * FROM OPENQUERY ( MyLinkServer,''SELECT
                  ccd.transfer 
           FROM ( SELECT * FROM tableA AS ac 
           WHERE ac.startdatetime >= '''+@VAR+''') as acd
           INNER JOIN( SELECT * FROM tableB where active=''t'') AS r ON
           r.resourceid=acd.resourceid '')'


  **EXEC(@TSQL) -- GIVES ERROR.**

THE PRINT OF TSQL :

Error Print

Baseer Ul Hassan
  • 27
  • 1
  • 13
  • Change EXEC to SELECT and inspect output. – Lee Oct 13 '17 at 20:30
  • Possible duplicate of [How do I escape a single quote in SQL Server?](https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) – Liam Jan 29 '18 at 11:21

1 Answers1

0

The problem was in following portion : 

INNER JOIN( SELECT * FROM tableB where active=''t'')

I added extra single quotes (') like active = ''''t''''. This resolved the problem and it works like a chime :) . Thanks Everyone

Liam
  • 25,247
  • 27
  • 110
  • 174
Baseer Ul Hassan
  • 27
  • 1
  • 13