SQL Question
Incorrect syntax near '+ replicate('. Unclosed quotation mark after the character string ') order by 1'
I have an stored procedure(SP) that run an big query and it's works fine when I execute or debug it with defaults parameters, in Microsoft SQL Server Management Studio 2014. The issue is the next. When this SP it's executed from SSRS in the tab "Preview" on VS 2008's Reporting Service tool, and I set the same defaults parameters, this error appear:
Incorrect syntax near '+ replicate('. Unclosed quotation mark after the character string ') order by 1'.
This is my entire code:
set @vlcc_condicitmp = 'where (dfechamovi between convert(datetime,'''+@vlcc_dfechainic+''',103) and convert(datetime,'''+ @vlcc_dfechafina+''',103))'
set @vlcc_condici6 = ' order by '+@vlcc_orden
set @sentsql1_expresion = 'SELECT inmovinven.cnumproduc,Cast ('' '' as char) as nconsresol,inmovinven.ccodmovinv,'+
'inmovinven.ctipodocum,inmovinven.cllavedocu,'+
'inmovinven.dfechamovi,inmovinven.cunimedida,'+
'inmovinven.ncantidad,inmovinven.ccodbodega,'+
'inmovinven.ccodbodego,inmovinven.npreciouni,'+
'inmovinven.npreciotot,inmovinven.nexistante,'+
'inmovinven.nexistdesp,inmovinven.nprecioant,'+
'inmovinven.npreciodes,inmovinven.ctipasient,'+
'inmovinven.cnumasient,inmovinven.ccuentacon,'+
'inmovinven.cdescripci,cpdocument.cnumdocume,inproducto.cnumeserie,'+
'inproducto.cnomproduc,inproducto.ccateprodu,inproducto.clocalizac,'+
'fadocument.cnumdocume as cnumfactur,intipmovin.ctipoperad, '+
'inmovinven.ccodigousu,intipmovin.cdesmovimi,inmovinven.cconseajus,'' '' as cnomunidad '+
'FROM #inmovintmp as inmovinven LEFT JOIN '+@basdeDatos+'.dbo.'+'cpdocument ON '+
'inmovinven.cllavedocu = cpdocument.cllavedocu INNER JOIN '+
+@basdeDatos+'.dbo.'+'inproducto ON inmovinven.cnumproduc = inproducto.cnumproduc '+
'LEFT JOIN '+@basdeDatos+'.dbo.'+'fadocument on inmovinven.cllavedocu = ''VE''+ replicate('''+'0'+''',15-len(fadocument.nconsdocum))+ltrim(str(fadocument.nconsdocum)) '+
'inner join '+@basdeDatos+'.dbo.'+'intipmovin on inmovinven.ccodmovinv = intipmovin.ccodmovinv '+ @vlcc_condicitmp + @vlcc_condici1 +' and inmovinven.ncantidad > 0'
set @sentsql2_expresion = 'SELECT inhmovinve.cnumproduc,Cast ('' '' as char) as nconsresol,inhmovinve.ccodmovinv,'+
'inhmovinve.ctipodocum,inhmovinve.cllavedocu,'+
'inhmovinve.dfechamovi,inhmovinve.cunimedida,'+
'inhmovinve.ncantidad,inhmovinve.ccodbodega,'+
'inhmovinve.ccodbodego,inhmovinve.npreciouni,'+
'inhmovinve.npreciotot,inhmovinve.nexistante,'+
'inhmovinve.nexistdesp,inhmovinve.nprecioant,'+
'inhmovinve.npreciodes,inhmovinve.ctipasient,'+
'inhmovinve.cnumasient,inhmovinve.ccuentacon,'+
'inhmovinve.cdescripci,cpdocument.cnumdocume,inproducto.cnumeserie,'+
'inproducto.cnomproduc,inproducto.ccateprodu,inproducto.clocalizac,'+
'fahdocumen.cnumdocume as cnumfactur,'+
'intipmovin.ctipoperad,inhmovinve.ccodigousu,intipmovin.cdesmovimi,inhmovinve.cconseajus,'''' as cnomunidad '+
'FROM #inhmovitmp as inhmovinve LEFT JOIN '+@basdeDatos+'.dbo.'+'cpdocument ON '+
'inhmovinve.cllavedocu = cpdocument.cllavedocu INNER JOIN '+
+@basdeDatos+'.dbo.'+'inproducto ON inhmovinve.cnumproduc = inproducto.cnumproduc '+
'LEFT JOIN '+@basdeDatos+'.dbo.'+'fahdocumen on inhmovinve.cllavedocu = ''VE''+ replicate('''+'0'+''',15-len(fahdocumen.nconsdocum))+ltrim(str(fahdocumen.nconsdocum)) '+
'inner join '+@basdeDatos+'.dbo.'+'intipmovin on inhmovinve.ccodmovinv = intipmovin.ccodmovinv ' + @vlcc_condicitmp + @vlcc_condici2 +' and inhmovinve.ncantidad > 0'
IF (@vlcc_ctipoperad = 'I' OR @vlcc_ctipoperad = 'T')
BEGIN
SET @sentsql3_expresion = 'SELECT inmovinven.cnumproduc,Cast ('' '' as char) as nconsresol,inmovinven.ccodmovinv,'+
'inmovinven.ctipodocum, inmovinven.cllavedocu,'+
'inmovinven.dfechamovi, inmovinven.cunimedida,'+
'inmovinven.ncantidad as ncantida,inmovinven.ccodbodega as ccodbodega,'+
'inmovinven.ccodbodego, inmovinven.npreciouni,'+
'inmovinven.npreciotot, inmovinven.nexistante,'+
'inmovinven.nexistdesp, inmovinven.nprecioant,'+
'inmovinven.npreciodes, inmovinven.ctipasient,'+
'inmovinven.cnumasient, inmovinven.ccuentacon,'+
'inmovinven.cdescripci, cpdocument.cnumdocume,inproducto.cnumeserie,'+
'inproducto.cnomproduc, inproducto.ccateprodu,inproducto.clocalizac,'+
'fadocument.cnumdocume as cnumfactur, intipmovin.ctipoperad,'+
'inmovinven.ccodigousu,intipmovin.cdesmovimi,inmovinven.cconseajus,'' '' as cnomunidad '+
'FROM #inmovintmp as inmovinven LEFT JOIN '+@basdeDatos+'.dbo.'+'cpdocument ON '+
'inmovinven.cllavedocu = cpdocument.cllavedocu INNER JOIN '
+@basdeDatos+'.dbo.'+'inproducto ON inmovinven.cnumproduc = inproducto.cnumproduc '+
'LEFT JOIN '+@basdeDatos+'.dbo.'+'fadocument on inmovinven.cllavedocu = ''VE''+ replicate('''+'0'+''',15-len(fadocument.nconsdocum))+ltrim(str(fadocument.nconsdocum)) '+
'inner join '+@basdeDatos+'.dbo.'+'intipmovin on inmovinven.ccodmovinv = intipmovin.ccodmovinv '+
+ @vlcc_condicitmp + @vlcc_condici3+' AND inmovinven.ccodbodego <> '' '' and inmovinven.ncantidad < 0'
END
ELSE
BEGIN
SET @sentsql3_expresion = 'SELECT inmovinven.cnumproduc,Cast ('' '' as char) as nconsresol,'+@basdeDatos+'.dbo.'+'inmovinven.ccodmovinv,'+
'inmovinven.ctipodocum, inmovinven.cllavedocu,'+
'inmovinven.dfechamovi, inmovinven.cunimedida,'+
'inmovinven.ncantidad * -1 as ncantidad, inmovinven.ccodbodego as ccodbodega,'+
'inmovinven.ccodbodego, inmovinven.npreciouni,'+
'inmovinven.npreciotot, inmovinven.nexistante,'+
'inmovinven.nexistdesp, inmovinven.nprecioant,'+
'inmovinven.npreciodes, inmovinven.ctipasient,'+
'inmovinven.cnumasient, inmovinven.ccuentacon,'+
'inmovinven.cdescripci, cpdocument.cnumdocume, inproducto.cnumeserie,'+
'inproducto.cnomproduc, inproducto.ccateprodu, inproducto.clocalizac,'+
'fadocument.cnumdocume as cnumfactur, intipmovin.ctipoperad,'+
'inmovinven.ccodigousu, intipmovin.cdesmovimi,inmovinven.cconseajus,'''' as cnomunidad '+
'FROM #inmovintmp as inmovinven LEFT JOIN '+@basdeDatos+'.dbo.'+'cpdocument ON '+
'inmovinven.cllavedocu = cpdocument.cllavedocu INNER JOIN '+
+@basdeDatos+'.dbo.'+'inproducto ON inmovinven.cnumproduc = inproducto.cnumproduc '+
'LEFT JOIN '+@basdeDatos+'.dbo.'+'fadocument on inmovinven.cllavedocu = ''VE''+ replicate('''+'0'+''',15-len(fadocument.nconsdocum))+ltrim(str(fadocument.nconsdocum)) '+
'inner join '+@basdeDatos+'.dbo.'+'intipmovin on inmovinven.ccodmovinv = intipmovin.ccodmovinv '+
+@vlcc_condicitmp + @vlcc_condici1 +' AND inmovinven.ccodbodego <> '' '''
END
IF (@vlcc_ctipoperad = 'I' OR @vlcc_ctipoperad = 'T')
BEGIN
SET @sentsql4_expresion ='SELECT inhmovinve.cnumproduc,Cast ('' '' as char) as nconsresol, inhmovinve.ccodmovinv,'+
'inhmovinve.ctipodocum, inhmovinve.cllavedocu,'+
'inhmovinve.dfechamovi, inhmovinve.cunimedida,'+
'inhmovinve.ncantidad as ncantidad, inhmovinve.ccodbodega as ccodbodega,'+
'inhmovinve.ccodbodego, inhmovinve.npreciouni,'+
'inhmovinve.npreciotot, inhmovinve.nexistante,'+
'inhmovinve.nexistdesp, inhmovinve.nprecioant,'+
'inhmovinve.npreciodes, inhmovinve.ctipasient,'+
'inhmovinve.cnumasient, inhmovinve.ccuentacon,'+
'inhmovinve.cdescripci, cpdocument.cnumdocume, inproducto.cnumeserie,'+
'inproducto.cnomproduc, inproducto.ccateprodu, inproducto.clocalizac,'+
'fahdocumen.cnumdocume as cnumfactur,'+
'intipmovin.ctipoperad, inhmovinve.ccodigousu, intipmovin.cdesmovimi, inhmovinve.cconseajus,'' '' as cnomunidad '+
'FROM #inhmovitmp as inhmovinve LEFT JOIN '+@basdeDatos+'.dbo.'+'cpdocument ON '+
'inhmovinve.cllavedocu = cpdocument.cllavedocu INNER JOIN '+
'inproducto ON inhmovinve.cnumproduc = inproducto.cnumproduc '+
'LEFT JOIN '+@basdeDatos+'.dbo.'+'fahdocumen on inhmovinve.cllavedocu = ''VE''+ replicate('''+'0'+''',15-len(fahdocumen.nconsdocum))+ltrim(str(fahdocumen.nconsdocum)) '+
'inner join '+@basdeDatos+'.dbo.'+'intipmovin on inhmovinve.ccodmovinv = intipmovin.ccodmovinv '+
+ @vlcc_condicitmp +@vlcc_condici4 +' AND inhmovinve.ccodbodego <> '' '' AND inhmovinve.ncantidad < 0'
END
ELSE
BEGIN
SET @sentsql4_expresion = 'SELECT inhmovinve.cnumproduc,Cast ('' '' as char) as nconsresol, inhmovinve.ccodmovinv,'+
'inhmovinve.ctipodocum, inhmovinve.cllavedocu,'+
'inhmovinve.dfechamovi, inhmovinve.cunimedida,'+
'inhmovinve.ncantidad * - 1 as ncantidad, inhmovinve.ccodbodego as ccodbodega,'+
'inhmovinve.ccodbodego, inhmovinve.npreciouni, '+
'inhmovinve.npreciotot, inhmovinve.nexistante, '+
'inhmovinve.nexistdesp, inhmovinve.nprecioant, '+
'inhmovinve.npreciodes, inhmovinve.ctipasient, '+
'inhmovinve.cnumasient, inhmovinve.ccuentacon, '+
'inhmovinve.cdescripci, cpdocument.cnumdocume, inproducto.cnumeserie,'+
'inproducto.cnomproduc, inproducto.ccateprodu, inproducto.clocalizac,'+
'fahdocumen.cnumdocume as cnumfactur,'+
'intipmovin.ctipoperad, inhmovinve.ccodigousu, intipmovin.cdesmovimi, inhmovinve.cconseajus,'' as cnomunidad '+
'FROM #inhmovitmp as inhmovinve LEFT JOIN '+@basdeDatos+'.dbo.'+'cpdocument ON '+
'inhmovinve.cllavedocu = cpdocument.cllavedocu INNER JOIN '+
+@basdeDatos+'.dbo.'+'inproducto ON inhmovinve.cnumproduc = inproducto.cnumproduc '+
'LEFT JOIN '+@basdeDatos+'.dbo.'+'fahdocumen on inhmovinve.cllavedocu = ''VE''+ replicate('''+'0'+''',15-len(fahdocumen.nconsdocum))+ltrim(str(fahdocumen.nconsdocum)) '+
'inner join '+@basdeDatos+'.dbo.'+'intipmovin on inhmovinve.ccodmovinv = intipmovin.ccodmovinv '+
+ @vlcc_condicitmp + @vlcc_condici2 + ' AND inhmovinve.ccodbodego <> '' '''
END
IF(@vlcc_chkmovdestino ='S')
BEGIN
SET @sentsql_expresion = @sentsql1_expresion + ' union all (' + @sentsql2_expresion + ')' + @vlcc_condici6
END
ELSE
BEGIN
SET @sentsql_expresion = @sentsql1_expresion + ' union all (' + @sentsql2_expresion + ') union all (' + @sentsql3_expresion + ') union all (' + @sentsql4_expresion + ')' + @vlcc_condici6
END
SET @sentsql2_expresion = 'DROP TABLE #inmovintmp'
SET @sentsql3_expresion = 'DROP TABLE #inhmovitmp'
exec sp_executesql @sentsql_expresion
IF @@ROWCOUNT <= 0
BEGIN
exec sp_executesql @sentsql2_expresion
exec sp_executesql @sentsql3_expresion
set @vlci_errorState=1
SET @vlci_errorSeverity=16
SET @cMensajeExc ='No se puede mostrar el reporte'
SET @nIdLogExcAG = -1
RAISERROR (@cMensajeExc,@vlci_errorSeverity,@vlci_errorState)
return
END
exec sp_executesql @sentsql2_expresion
exec sp_executesql @sentsql3_expresion
Please help me, I try everything and the error still there. I try playing the skipped quotes (') thinking that I missing something, but nothing. Thanks
Answer
The solution is send the right parameters and don't forget anyone. I do the next:
- Open Profiler tool in the tab "Tools" in SQL Server Management Studio.
- Run the Report in the tab "Preview".
- Catch the sql sentence called with it's parameters and values.
- Run the SP with the this parameters
After that, SQL Server Management Studio shows the same error that SSRS and I figured out that the problems were in the parameters. Then, I check the parameters and see that I was putting one of them with an empty string.This is an example image.
Thanks for yours quickly answers
Source (Stackoverflow)