Error 102 in OpenQuery

Question

Error 102 in OpenQuery

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I'm having this error:

Incorrect syntax next to '@SQL_LINKED'. Error 102. SQLSTATE 42000. Severity 15. MsgState 1. Line 78.

When trying to run this script:

 SET @SQL_LINKED = 'SELECT CAST(v.produto AS VARCHAR(20))   AS CAIXA,
 v.formula                      AS FORMULA,       
 coalesce(v.COMP_B,0)           AS CHB01,        
 coalesce(v.COMP_C,0)           AS CHC01,        
 coalesce(v.COMP_BC,0)          AS CHBC01,        
 coalesce(v.COMP_E,0)           AS CHE01,
 coalesce(v.COMP_BB,0)          AS CHBB01,        
 coalesce(v.COMP_BE,0)          AS CHBE01,        
 coalesce(v.COMP_AC,0)          AS CHAC01,        
 coalesce(v.COMP_EC,0)          AS CHEC01, 
 coalesce(v.COMP_A,0)           AS CHA01,        
 coalesce(v.COMP_D,0)           AS CHD01,        
 coalesce(v.COMP_DC,0)          AS CHDC01     
 FROM produtos_calc_l v 
 WHERE v.produto = '''''+@CodCai+''''' 
 AND v.formula NOT LIKE ''%D%'''  
 INSERT INTO ##tempvincos (codref, C, A, B,D, caixa, formula, chb01, chc01, 
 chbc01, che01, chbb01, chbe01, chac01, chec01, cha01, chd01, chdc01 )
 SELECT @CODREF, @ALTURA,  @COMPRIM,  @LARGURA, 0,   Vinco.* FROM   (SELECT 
 *  FROM   Openquery("server", @SQL_LINKED) ) AS Vinco

How do I pass the @SQL_LINKED to the OpenQuery ?

sql sql-server

asked by anonymous 03.01.2018 / 03:37

2 answers

Array declaration in javascript [duplicate] Null Context When Generating PDF

score 0 · Answer 1

Hello,

Try to put DECLARE @SQL_LINKED VARCHAR(7000) before SET @SQL_LINKED =

Getting It: DECLARE @SQL_LINKED VARCHAR(7000) SET @SQL_LINKED =

You did not declare your local variable @SQL_LINKED , in your code you just assigned a value to it, but without specifying its type.

See the following link:

p>

score 0 · Answer 2

Too many apostrophes in the WHERE clause.

If the column v.product is also declared as string , replace

WHERE v.produto = '''''+@CodCai+'''''

by

WHERE v.produto = ''' + @CodCai + '''

I suggest that you first test the assembly of the contents of the @SQL_LINKED variable. Do something like

-- código #1
SET @SQL_LINKED = 'SELECT CAST(v.produto AS VARCHAR(20))   AS CAIXA,
 v.formula                      AS FORMULA,       
 ...
 WHERE v.produto = ''' + @CodCai + '''
 AND v.formula NOT LIKE ''%D%'';';

PRINT @SQL_LINKED;

When you are sure that the command is being mounted correctly, evaluate whether remote execution is correct:

-- código #2
SET @SQL_LINKED = 'SELECT CAST(v.produto AS VARCHAR(20))   AS CAIXA,
     v.formula                      AS FORMULA,       
     ...
     WHERE v.produto = ''' + @CodCai + '''
     AND v.formula NOT LIKE ''%D%'';';

SELECT top (10) Vinco.*
  from Openquery("INFOBOX", @SQL_LINKED) as Vinco;