Error 102 in Openquery

Question

I’m making this mistake:

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 should I pass the @SQL_LINKED to the Openquery ?

Answer 1

Excess of apostrophes in the WHERE clause.

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

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

for

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

---

I suggest that you first test the mounting 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 the command is being mounted correctly, evaluate whether the 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;

Answer 2

Hello,

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

Getting Like this : DECLARE @SQL_LINKED VARCHAR(7000) SET @SQL_LINKED =

You failed to declare your local variable @SQL_LINKED, in your code you just assigned a value to it, but without specifying the type of it.

See the following link :

https://docs.microsoft.com/pt-br/sql/t-sql/language-elements/declare-local-variable-transact-sql