Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: CrystalReports
Dear all,
 
please solve this error while run the crystal report with the procedure.
 
Database connection error 'ADO ERROR CODE 0x80040e07'
desc: syntax error while converting varchar value.
 
i use S.P below:
 
ALTER PROCEDURE dbo.StoredProcedure2
	@DYS int,
	@comapny varchar(50)
 
AS
	/* SET NOCOUNT ON */
	
declare @dbname varchar(50)
DECLARE @EXECQ AS VARCHAR(8000)
 
set @dbname=''
 
if @comapny='FMCPL'
Begin
set @dbname='munim002'
End
 
else if @comapny='RBE'
begin
set @dbname='munim008'
End
 
else if @comapny='SDCC'
begin
set @dbname='munim005'
end 
 
else if @comapny='SBPL'
begin
set @dbname='munim004'
end 
 
else 
begin
set @dbname='munim017'
end
 
SET @EXECQ='SELECT '''+@comapny+''' as company,[sl].itname AS itemname,SUM([sl].qtyin) AS qtyin, SUM([sl].qtyout) AS qtyout,SUM([sl].qtyin)- SUM([sl].qtyout) as qty,[sl].unit AS unit, MAX([sl].docdt) AS maxdt,'+@dbname+'.dbo.itmast.material, 
'+@dbname+'.dbo.itgroup.dtldesc,'+@dbname+'.dbo.gdmast.gdname
FROM '+@dbname+'.dbo.[StockLederView] sl INNER JOIN
'+@dbname+'.dbo.itmast ON [sl].itcode = '+@dbname+'.dbo.itmast.itcode INNER JOIN
'+@dbname+'.dbo.itgroup ON '+@dbname+'.dbo.itmast.itgroup ='+@dbname+'.dbo.itgroup.code INNER JOIN
'+@dbname+'.dbo.gdmast ON [sl].gdcode ='+@dbname+'.dbo.gdmast.gdcode
GROUP BY [sl].itname, [sl].unit,'+@dbname+'.dbo.itmast.material,'+@dbname+'.dbo.itgroup.dtldesc,'+@dbname+'.dbo.gdmast.gdname
HAVING  SUM([sl].qtyin)- SUM([sl].qtyout) > 0 and (MAX([sl].DOCdt) <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() -@DYS)))
ORDER BY [sl].itname'
 
--print @EXECQ
--set @execq='select itname,SUM(qtyin)from '+@dbname+'.dbo.StockLederView'
Exec (@execq)
Posted 11-Oct-12 23:52pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try this..hope it work...
 
ALTER PROCEDURE dbo.StoredProcedure2
	@DYS varchar(5),
	@comapny varchar(50)
 
AS
	/* SET NOCOUNT ON */
	
declare @dbname varchar(50)
DECLARE @EXECQ AS VARCHAR(8000)
 
set @dbname=''
 
if @comapny='FMCPL'
Begin
set @dbname='munim002'
End
 
else if @comapny='RBE'
begin
set @dbname='munim008'
End
 
else if @comapny='SDCC'
begin
set @dbname='munim005'
end 
 
else if @comapny='SBPL'
begin
set @dbname='munim004'
end 
 
else 
begin
set @dbname='munim017'
end
 
SET @EXECQ='SELECT '''+@comapny+''' as company,[sl].itname AS itemname,SUM([sl].qtyin) AS qtyin, SUM([sl].qtyout) AS qtyout,SUM([sl].qtyin)- SUM([sl].qtyout) as qty,[sl].unit AS unit, MAX([sl].docdt) AS maxdt,'+@dbname+'.dbo.itmast.material, 
'+@dbname+'.dbo.itgroup.dtldesc,'+@dbname+'.dbo.gdmast.gdname
FROM '+@dbname+'.dbo.[StockLederView] sl INNER JOIN
'+@dbname+'.dbo.itmast ON [sl].itcode = '+@dbname+'.dbo.itmast.itcode INNER JOIN
'+@dbname+'.dbo.itgroup ON '+@dbname+'.dbo.itmast.itgroup ='+@dbname+'.dbo.itgroup.code INNER JOIN
'+@dbname+'.dbo.gdmast ON [sl].gdcode ='+@dbname+'.dbo.gdmast.gdcode
GROUP BY [sl].itname, [sl].unit,'+@dbname+'.dbo.itmast.material,'+@dbname+'.dbo.itgroup.dtldesc,'+@dbname+'.dbo.gdmast.gdname
HAVING  SUM([sl].qtyin)- SUM([sl].qtyout) > 0 and (MAX([sl].DOCdt) <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() - '+ @DYS +')))
ORDER BY [sl].itname'
 
--print @EXECQ
--set @execq='select itname,SUM(qtyin)from '+@dbname+'.dbo.StockLederView'
 

Thanks
  Permalink  
Comments
AshishChaudha at 12-Oct-12 4:58am
   
Yours always welcome.
ridoy at 12-Oct-12 5:00am
   
+5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 275
1 DamithSL 265
2 CPallini 235
3 Maciej Los 185
4 George Jonsson 170
0 OriginalGriff 5,415
1 DamithSL 4,422
2 Maciej Los 3,820
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,911


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 12 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100