SQL OpenQuery incorrect syntax near string

Question

I am trying to run the following query in a stored procedure. When I try to run it with hardcoded values instead of variables it runs fine. But just when I try to enclose the whole query into a variable and execute it it gives me an error saying: Incorrect syntax near 'FIFO'.

I tried replacing single quotes with double quotes but of no use. Please Help!

DECLARE @TSQL nvarchar(max); Declare @STATION_CODE varchar(max); Declare @STATION_CODE_PREV varchar(max);

SET @STATION_CODE='1600020'; SET @STATION_CODE_PREV='1600007'; SET @TSQL='SELECT * FROM OPENQUERY (VFDB,''SELECT COUNT(TABLE_7.FIFO) AS FIFO_COUNT,TABLE_7.FIFO,TABLE_7.Day_Hour ,TABLE_7.Station_Code,''FIFO'' AS Element FROM  ( select CAST(CASE WHEN TABLE_6 .BCD_PRV2_EXIT=TABLE_5 .BCD_PRV2 THEN ''1'' ELSE ''0'' END AS INT) FIFO,    case when datepart(hour,TABLE_6.CREATION_DATE_E3 )<10 
      then
    ''0''
      +Right (Cast(datepart(hour,TABLE_6.CREATION_DATE_E3 + 100) As Varchar) ,2)+'':00 - ''+''0''
      +Right(Cast(datepart(hour,TABLE_6.CREATION_DATE_E3 + 100) As Varchar) ,2)+'':59'' 
        else

       Right (Cast(datepart(hour,TABLE_6.CREATION_DATE_E3 + 100) As Varchar) ,2)+'':00 - ''
      +Right(Cast(datepart(hour,TABLE_6.CREATION_DATE_E3 + 100) As Varchar) ,2)+'':59''

      end As Day_Hour ,TABLE_6.Station_Code As Station_Code  from

        (           select T1_ENTRY.RANK1_ENTRY AS T5_R1, T1_ENTRY.BCD_PRV1,T1_ENTRY.CREATION_DATE_E1,T2_ENTRY.RANK2_ENTRY , T2_ENTRY.BCD_PRV2 ,T2_ENTRY.CREATION_DATE_E2           from 
                (
                select MFG_UNIT_NUM as BCD_PRV1,CREATION_DATE AS CREATION_DATE_E1 ,ROW_NUMBER()over(order by Creation_Date) as RANK1_ENTRY
                FROM dbo.DAW_FN_STATS_TLDTLRAW(115, ''REFRIGERATOR'', NULL, '''''+ @STATION_CODE +''''', NULL,Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0),Dateadd(hh,1, Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0)))RAWDTL 
                ) T1_ENTRY          LEFT JOIN   
                (
                select MFG_UNIT_NUM as BCD_PRV2,CREATION_DATE AS CREATION_DATE_E2,ROW_NUMBER()over(order by Creation_Date) as RANK2_ENTRY
                FROM dbo.DAW_FN_STATS_TLDTLRAW(115, ''REFRIGERATOR'', NULL, '''''+ @STATION_CODE_PREV +''''', NULL,Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0),Dateadd(hh,1, Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0)))RAWDTL 
                ) T2_ENTRY          ON T1_ENTRY.RANK1_ENTRY=T2_ENTRY.RANK2_ENTRY-1
        ) TABLE_5

LEFT JOIN 

        (       select T3_EXIT.RANK1_EXIT AS T6_R1, T3_EXIT.BCD_PRV1_EXIT,T3_EXIT.CREATION_DATE_E3,T4_EXIT.RANK2_EXIT , T4_EXIT.BCD_PRV2_EXIT ,T4_EXIT.CREATION_DATE_E4,T3_EXIT.Station_Code            from 
                (
                select MFG_UNIT_NUM as BCD_PRV1_EXIT,CREATION_DATE AS CREATION_DATE_E3 ,ROW_NUMBER()over(order by Creation_Date) as RANK1_EXIT,STATION_CODE as STATION_CODE 
                FROM dbo.DAW_FN_STATS_TLDTLRAW(115,''REFRIGERATOR'', NULL, '''+@STATION_CODE+''',NULL,Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0),Dateadd(hh,1, Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0)))RAWDTL
                ) T3_EXIT           LEFT JOIN   
                (
                select MFG_UNIT_NUM as BCD_PRV2_EXIT,CREATION_DATE AS CREATION_DATE_E4,ROW_NUMBER()over(order by Creation_Date) as RANK2_EXIT,STATION_CODE as STATION_CODE 
                FROM dbo.DAW_FN_STATS_TLDTLRAW(115,''REFRIGERATOR'', NULL,'''+@STATION_CODE_PREV+''', NULL,Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0),Dateadd(hh,1, Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0)))RAWDTL
                        ) T4_EXIT           ON T3_EXIT.RANK1_EXIT=T4_EXIT.RANK2_EXIT-1       ) TABLE_6 ON TABLE_5.BCD_PRV2=TABLE_6.BCD_PRV2_EXIT   )TABLE_7 GROUP BY  TABLE_7.FIFO,TABLE_7.Day_Hour ,Table_7. STATION_CODE'')';

EXEC sp_executesql @TSQL;

That's why nested dynamic queries are discouraged... Try with 4 quotes (not kidding). — xlecoustillier
– xlecoustillier, Commented Sep 11, 2015 at 13:49
4 quotes around what? what is the other way to pass variables in open query? — Salik
– Salik, Commented Sep 11, 2015 at 13:51
this bit i'm guessing TABLE_7.Station_Code,''FIFO'' AS Element FROM ''''FIFO'''' — pancho018
– pancho018, Commented Sep 11, 2015 at 13:55
No, 4 quotes needed to be placed around ` ''FIFO'' AS Element`. Please post it as your answer so that i may accept it. thanks :) — Salik
– Salik, Commented Sep 11, 2015 at 14:12
everywhere applicable including 'FIFO' as Element. but not FROM FIFO — Salik
– Salik, Commented Sep 11, 2015 at 20:14

theweeknd · Accepted Answer · 2015-09-11 15:19:59Z

2

You already got your answer, so this is just a tip, how you can easily solve such problems in the future.

declare @sql_string varchar(100)
set @sql_string = 'SELECT * FROM OPENQUERY (VFDB,''SELECT * FROM foo WHERE bar = ''''i am a string'''';'')' 
select @sql_string --> This here will let you see how your string looks like

answered Sep 11, 2015 at 15:19

theweeknd

2871 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xlecoustillier · Accepted Answer · 2015-09-14 10:40:25Z

Let's say you have some dynamic SQL with a string:

'SELECT * FROM foo WHERE bar = ''i am a string'';'
--                             ^^             ^^
--                             ||             ||
--                             notice the double quotes

Now let's do it with OPENQUERY, inside dynamic SQL (your case):

'SELECT * FROM OPENQUERY (VFDB,''SELECT * FROM foo WHERE bar = ''''i am a string'''';'')' 
--                             ^^                              ^^^^
--                             ||                              ||||
--                             double quotes        escaped double quotes => 4 quotes

You have nested queries in a string, so each time you add a nesting level, you have to double the quotes to avoid closing the upper nesting level.

Strongly discouraged as it is a real pain to debug, as you can guess, not mentioning what a mess variables concatenation can be (but you already use 5 quotes for that, so you already know :) ).

My attempt on fixing the quotes (can't test it, so maybe I messed up at some point):

DECLARE @TSQL nvarchar(max); Declare @STATION_CODE varchar(max); Declare @STATION_CODE_PREV varchar(max);

SET @STATION_CODE='1600020'; SET @STATION_CODE_PREV='1600007'; SET @TSQL='SELECT * FROM OPENQUERY (VFDB,''''SELECT COUNT(TABLE_7.FIFO) AS FIFO_COUNT,TABLE_7.FIFO,TABLE_7.Day_Hour ,TABLE_7.Station_Code,''''FIFO'''' AS Element FROM  ( select CAST(CASE WHEN TABLE_6 .BCD_PRV2_EXIT=TABLE_5 .BCD_PRV2 THEN ''''1'''' ELSE ''''0'''' END AS INT) FIFO,    case when datepart(hour,TABLE_6.CREATION_DATE_E3 )<10 
      then
    ''''0''''
      +Right (Cast(datepart(hour,TABLE_6.CREATION_DATE_E3 + 100) As Varchar) ,2)+'''':00 - ''''+''''0''''
      +Right(Cast(datepart(hour,TABLE_6.CREATION_DATE_E3 + 100) As Varchar) ,2)+'''':59'''' 
        else

       Right (Cast(datepart(hour,TABLE_6.CREATION_DATE_E3 + 100) As Varchar) ,2)+'''':00 - ''''
      +Right(Cast(datepart(hour,TABLE_6.CREATION_DATE_E3 + 100) As Varchar) ,2)+'''':59''''

      end As Day_Hour ,TABLE_6.Station_Code As Station_Code  from

        (           select T1_ENTRY.RANK1_ENTRY AS T5_R1, T1_ENTRY.BCD_PRV1,T1_ENTRY.CREATION_DATE_E1,T2_ENTRY.RANK2_ENTRY , T2_ENTRY.BCD_PRV2 ,T2_ENTRY.CREATION_DATE_E2           from 
                (
                select MFG_UNIT_NUM as BCD_PRV1,CREATION_DATE AS CREATION_DATE_E1 ,ROW_NUMBER()over(order by Creation_Date) as RANK1_ENTRY
                FROM dbo.DAW_FN_STATS_TLDTLRAW(115, ''''REFRIGERATOR'''', NULL, '''''+ @STATION_CODE +''''', NULL,Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0),Dateadd(hh,1, Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0)))RAWDTL 
                ) T1_ENTRY          LEFT JOIN   
                (
                select MFG_UNIT_NUM as BCD_PRV2,CREATION_DATE AS CREATION_DATE_E2,ROW_NUMBER()over(order by Creation_Date) as RANK2_ENTRY
                FROM dbo.DAW_FN_STATS_TLDTLRAW(115, ''''REFRIGERATOR'''', NULL, '''''+ @STATION_CODE_PREV +''''', NULL,Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0),Dateadd(hh,1, Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0)))RAWDTL 
                ) T2_ENTRY          ON T1_ENTRY.RANK1_ENTRY=T2_ENTRY.RANK2_ENTRY-1
        ) TABLE_5

LEFT JOIN 

        (       select T3_EXIT.RANK1_EXIT AS T6_R1, T3_EXIT.BCD_PRV1_EXIT,T3_EXIT.CREATION_DATE_E3,T4_EXIT.RANK2_EXIT , T4_EXIT.BCD_PRV2_EXIT ,T4_EXIT.CREATION_DATE_E4,T3_EXIT.Station_Code            from 
                (
                select MFG_UNIT_NUM as BCD_PRV1_EXIT,CREATION_DATE AS CREATION_DATE_E3 ,ROW_NUMBER()over(order by Creation_Date) as RANK1_EXIT,STATION_CODE as STATION_CODE 
                FROM dbo.DAW_FN_STATS_TLDTLRAW(115,''''REFRIGERATOR'''', NULL, '''''+@STATION_CODE+''''',NULL,Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0),Dateadd(hh,1, Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0)))RAWDTL
                ) T3_EXIT           LEFT JOIN   
                (
                select MFG_UNIT_NUM as BCD_PRV2_EXIT,CREATION_DATE AS CREATION_DATE_E4,ROW_NUMBER()over(order by Creation_Date) as RANK2_EXIT,STATION_CODE as STATION_CODE 
                FROM dbo.DAW_FN_STATS_TLDTLRAW(115,''''REFRIGERATOR'''', NULL,'''''+@STATION_CODE_PREV+''''', NULL,Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0),Dateadd(hh,1, Dateadd(HOUR, Datediff(HOUR,0,GETDATE()),0)))RAWDTL
                        ) T4_EXIT           ON T3_EXIT.RANK1_EXIT=T4_EXIT.RANK2_EXIT-1       ) TABLE_6 ON TABLE_5.BCD_PRV2=TABLE_6.BCD_PRV2_EXIT   )TABLE_7 GROUP BY  TABLE_7.FIFO,TABLE_7.Day_Hour ,Table_7. STATION_CODE'')';

EXEC sp_executesql @TSQL;

Collectives™ on Stack Overflow

SQL OpenQuery incorrect syntax near string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related