0

When I exec this

SET @Qry =

    'SELECT [LMITNO] INTO ##TABLETEMP' + LTRIM(RTRIM(STR(@@SPID))) + '     
     FROM OPENQUERY(MAA,''SELECT DISTINCT [LMITNO] FROM MAA.ESMEL3.MVXDDTPRD.MILOMA 
     WHERE [LMITNO] LIKE ' + '''' + @ChemicalPreFix + '''' + ''')'

I get this. 

    SELECT [LMITNO] INTO ##TABLETEMP91 FROM 
    OPENQUERY(MAA,'SELECT DISTINCT [LMITNO] 
    FROM MAA.ESMEL3.MVXDDTPRD.MILOMA WHERE [LMITNO] LIKE ''CHE%''')

But the error I get is that there is incorrect syntax near CHE%. However the syntax looks okay to me. What's wrong?

Jack Reilly
  • 158
  • 2
  • 14

2 Answers2

2

Your query looks right, except for the inclusion of the server name in the from statement, It should read as:

SET @Qry =

    'SELECT [LMITNO] INTO ##TABLETEMP' + LTRIM(RTRIM(STR(@@SPID))) + '     
     FROM OPENQUERY(MAA,''SELECT DISTINCT [LMITNO] FROM ESMEL3.MVXDDTPRD.MILOMA 
     WHERE [LMITNO] LIKE ' + '''' + @ChemicalPreFix + '''' + ''')'
Gerald R Spangler
  • 34
  • 3
1

Please try this :

SET @Qry =

'SELECT [LMITNO] INTO ##TABLETEMP' + LTRIM(RTRIM(STR(@@SPID))) + '     
 FROM OPENQUERY(MAA,''SELECT DISTINCT [LMITNO] FROM MAA.ESMEL3.MVXDDTPRD.MILOMA 
 WHERE [LMITNO] LIKE ' + '''''' + @ChemicalPreFix + '''''' + ''')'

It gives the below query :

SELECT [LMITNO] INTO ##TABLETEMP53 FROM 
OPENQUERY(MAA,'SELECT DISTINCT [LMITNO] FROM  MAA.ESMEL3.MVXDDTPRD.MILOMA 
WHERE [LMITNO] LIKE ''CHE%''')

It is parsed successfully. Extra quotes are required for the variable in the SELECT query as it is dynamic SQL.

Sam
  • 11
  • 2