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
The solution is send the right parameters and don't forget anyone. I do the next:
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