InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 06:04:29 GMT20http://www.sqlservercentral.com/Forums/Topic1379664-391-1.aspxthis is how our ERP works with the dynamic sql statements.i thought that maybe someone have a quick results for this kind of issues.THX.Thu, 01 Nov 2012 09:42:48 GMTMad-Doghttp://www.sqlservercentral.com/Forums/Topic1379664-391-1.aspx[quote][b]Mad-Dog (11/1/2012)[/b][hr]Hi,is there any quick way to find the problem in this query?the error is:string or binary data would be truncatedthe query:exec sp_executesql [/quote]Why are you using dynamic sql for this?Thu, 01 Nov 2012 07:40:24 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1379664-391-1.aspxIt would also help if we could see the DDL (CREATE TABLE statement) for the table involved.Thu, 01 Nov 2012 06:54:19 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1379664-391-1.aspxthese issues are notoriously hard to dig for...there's a connect item out there stating why not add the column name of the violating size issue to the error message.because this is a parameterized query, what you need is right there in front of you...you just have to become familiar with parsing it out:this part:[quote]@P1 varchar(8),@P2 bigint,@P3 varchar(1),@P4 varchar(1),@P5 varchar(1),@P6 bigint,@P7 bigint,@P8 bigint,@P9 varchar(8),@P10 varchar(6),@P11 varchar(1),@P12 varchar(1),@P13 float,@P14 float,@P15 float,@P16 float,@P17 float,@P18 varchar(9),@P19 varchar(8),@P20 varchar(1),@P21 varchar(1),@P22 varchar(7),@P23 varchar(1),@P24 varchar(1),@P25 varchar(1),@P26 varchar(1),@P27 varchar(1),@P28 varchar(1),@P29 varchar(1),@P30 varchar(1),@P31 varchar(1),@P32 varchar(1),@P33 varchar(1),@P34 float,@P35 float,@P36 float,@P37 float,@P38 varchar(1),@P39 varchar(1),@P40 varchar(1),@P41 varchar(1),@P42 varchar(1),@P43 varchar(1),@P44 bigint,@P45 bigint','07030492',2,'','A','',0,12621600,10516320,'30/12/07','111111','','',112000,0,0,0,0,'189604.21','49158.47','','','UNKNOWN','','','','','','','','','','','',0,0,0,0,'','','','','','',0,1[/quote]has both the parameters, the param sizes,and the values. and the insert statement has the column names...a little copy/paste and formatting lets you look at it like what I've pasted below.what you need to do is compare it to the column definitions...and it should be easy, you KNOW it's a text column of some type, there's only 6 varchars that are not varchar(1) being passeds, so compare the parameters to table definition, and you should be able to visually find the column quickly..according to my copy/pastes, there should be 45 values, 45 parameters, but i only saw 42 columns in what you pasted.[code]'07030492', clasik.dbo.LOADFNC.FNCNUM , @P1 varchar(8),2, clasik.dbo.LOADFNC.LINE , @P2 bigint,'', clasik.dbo.LOADFNC.FNCPATNAME , @P3 varchar(1),'A', clasik.dbo.LOADFNC.IVNUM , @P4 varchar(1),'', clasik.dbo.LOADFNC.FNCREF2 , @P5 varchar(1),0, clasik.dbo.LOADFNC.BALDATE , @P6 bigint,12621600, clasik.dbo.LOADFNC.CURDATE , @P7 bigint,10516320, clasik.dbo.LOADFNC.FNCDATE , @P8 bigint,'30/12/07', clasik.dbo.LOADFNC.ACCNAME , @P9 varchar(8),'111111', clasik.dbo.LOADFNC.IACCNAME , @P10 varchar(6),'', clasik.dbo.LOADFNC.BUDCODE , @P11 varchar(1),'', clasik.dbo.LOADFNC.COSTCNAME , @P12 varchar(1),112000, clasik.dbo.LOADFNC.SUM1 , @P13 float,0, clasik.dbo.LOADFNC.SUM2 , @P14 float,0, clasik.dbo.LOADFNC.EXCHANGE2 , @P15 float,0, clasik.dbo.LOADFNC.SUM3 , @P16 float,0, clasik.dbo.LOADFNC.EXCHANGE3 , @P17 float,'189604.21', clasik.dbo.LOADFNC.CODE3 , @P18 varchar(9),'49158.47', clasik.dbo.LOADFNC.DEBIT , @P19 varchar(8),'', clasik.dbo.LOADFNC.DNAME , @P20 varchar(1),'', clasik.dbo.LOADFNC.TEXT , @P21 varchar(1),'UNKNOWN', clasik.dbo.LOADFNC.CASHFLOWCODE , @P22 varchar(7),'', clasik.dbo.LOADFNC.FNCICODE , @P23 varchar(1),'', clasik.dbo.LOADFNC.FNCIREF1 , @P24 varchar(1),'', clasik.dbo.LOADFNC.FNCIREF2 , @P25 varchar(1),'', clasik.dbo.LOADFNC.DETAILS , @P26 varchar(1),'', clasik.dbo.LOADFNC.COSTCNAME2 , @P27 varchar(1),'', clasik.dbo.LOADFNC.COSTCNAME3 , @P28 varchar(1),'', clasik.dbo.LOADFNC.COSTCNAME4 , @P29 varchar(1),'', clasik.dbo.LOADFNC.COSTCNAME5 , @P30 varchar(1),'', clasik.dbo.LOADFNC.QUANT1 , @P31 varchar(1),'', clasik.dbo.LOADFNC.SUM4 , @P32 varchar(1),'', clasik.dbo.LOADFNC.SUM5 , @P33 varchar(1),0, clasik.dbo.LOADFNC.EXCHANGE5 , @P34 float,0, clasik.dbo.LOADFNC.CODE5 , @P35 float,0, clasik.dbo.LOADFNC.DOCNO , @P36 float,0, clasik.dbo.LOADFNC.WBS , @P37 float,'', clasik.dbo.LOADFNC.CUSTNAME , @P38 varchar(1),'', clasik.dbo.LOADFNC.BRANCHNAME , @P39 varchar(1),'', clasik.dbo.LOADFNC.VATTYPE , @P40 varchar(1),'', clasik.dbo.LOADFNC.IVCOUNT , @P41 varchar(1),'', clasik.dbo.LOADFNC.T$USER @P42 varchar(1),'', @P43 varchar(1),0, @P44 bigint,1 @P45 bigint[/code]Thu, 01 Nov 2012 05:52:50 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1379664-391-1.aspxHi,obviously, there is a problem with trying to input a big peg in a small hole - when I need to figure out which column is the offending one, I would normally hardcode known accepted values for each column and then (1 by 1) replace the hard coded values with the params and see at which point it throws up the error.Alternatively, you could do some data profiling on your input data and compare the max sizes against your column definitions (of the target table).You could also first get the data into a temp table to aid you with the data profiling.HTH,BThu, 01 Nov 2012 04:57:13 GMTbleroyhttp://www.sqlservercentral.com/Forums/Topic1379664-391-1.aspxHi,is there any quick way to find the problem in this query?the error is:string or binary data would be truncatedthe query:exec sp_executesql N'insert into clasik.dbo.LOADFNC ( clasik.dbo.LOADFNC.FNCNUM , clasik.dbo.LOADFNC.LINE , clasik.dbo.LOADFNC.FNCPATNAME , clasik.dbo.LOADFNC.IVNUM , clasik.dbo.LOADFNC.FNCREF2 , clasik.dbo.LOADFNC.BALDATE , clasik.dbo.LOADFNC.CURDATE , clasik.dbo.LOADFNC.FNCDATE , clasik.dbo.LOADFNC.ACCNAME , clasik.dbo.LOADFNC.IACCNAME , clasik.dbo.LOADFNC.BUDCODE , clasik.dbo.LOADFNC.COSTCNAME , clasik.dbo.LOADFNC.SUM1 , clasik.dbo.LOADFNC.SUM2 , clasik.dbo.LOADFNC.EXCHANGE2 , clasik.dbo.LOADFNC.SUM3 , clasik.dbo.LOADFNC.EXCHANGE3 , clasik.dbo.LOADFNC.CODE3 , clasik.dbo.LOADFNC.DEBIT , clasik.dbo.LOADFNC.DNAME , clasik.dbo.LOADFNC.TEXT , clasik.dbo.LOADFNC.CASHFLOWCODE , clasik.dbo.LOADFNC.FNCICODE , clasik.dbo.LOADFNC.FNCIREF1 , clasik.dbo.LOADFNC.FNCIREF2 , clasik.dbo.LOADFNC.DETAILS , clasik.dbo.LOADFNC.COSTCNAME2 , clasik.dbo.LOADFNC.COSTCNAME3 , clasik.dbo.LOADFNC.COSTCNAME4 , clasik.dbo.LOADFNC.COSTCNAME5 , clasik.dbo.LOADFNC.QUANT1 , clasik.dbo.LOADFNC.SUM4 , clasik.dbo.LOADFNC.SUM5 , clasik.dbo.LOADFNC.EXCHANGE5 , clasik.dbo.LOADFNC.CODE5 , clasik.dbo.LOADFNC.DOCNO , clasik.dbo.LOADFNC.WBS , clasik.dbo.LOADFNC.CUSTNAME , clasik.dbo.LOADFNC.BRANCHNAME , clasik.dbo.LOADFNC.VATTYPE , clasik.dbo.LOADFNC.IVCOUNT , clasik.dbo.LOADFNC.T$USER )values ( rtrim(substring( ltrim(rtrim( @P1 )) , 1, 8)) , convert( int , str( @P2 , 6, 0)) , rtrim(substring( ltrim(rtrim( @P3 )) , 1, 5)) , rtrim(substring( ltrim(rtrim( @P4 )) , 1, 8)) , rtrim(substring( ltrim(rtrim( @P5 )) , 1, 8)) , @P6 / 1440 * 1440 , @P7 / 1440 * 1440 , @P8 / 1440 * 1440 , rtrim(substring( ltrim(rtrim( @P9 )) , 1, 16)) , rtrim(substring( ltrim(rtrim( @P10 )) , 1, 16)) , rtrim(substring( ltrim(rtrim( @P11 )) , 1, 24)) , rtrim(substring( ltrim(rtrim( @P12 )) , 1, 16)) , convert(float, str(round( convert(decimal(25,9), @P13 ) , 2), 16, 2)) , convert(float, str(round( convert(decimal(25,9), @P14 ) , 2), 16, 2)) , convert(float, str(round( convert(decimal(25,9), @P15 ) , 4), 10, 4)) , convert(float, str(round( convert(decimal(25,9), @P16 ) , 2), 16, 2)) , convert(float, str(round( convert(decimal(25,9), @P17 ) , 4), 10, 4)) , rtrim(substring( ltrim(rtrim( @P18 )) , 1, 3)) , ltrim(rtrim( @P19 )) , rtrim(substring( case when ( ( ltrim(rtrim( @P20 )) = @P21 ) ) then ( @P22 ) else ( ltrim(rtrim( @P23 )) ) end , 1, 8)) , rtrim(substring(reverse( ltrim(rtrim( @P24 )) ) , 1, 32)) , rtrim(substring( ltrim(rtrim( @P25 )) , 1, 8)) , rtrim(substring( ltrim(rtrim( @P26 )) , 1, 8)) , rtrim(substring( ltrim(rtrim( @P27 )) , 1, 8)) , rtrim(substring( ltrim(rtrim( @P28 )) , 1, 8)) , rtrim(substring(reverse( ltrim(rtrim( @P29 )) ) , 1, 24)) , rtrim(substring( ltrim(rtrim( @P30 )) , 1, 16)) , rtrim(substring( ltrim(rtrim( @P31 )) , 1, 16)) , rtrim(substring( ltrim(rtrim( @P32 )) , 1, 16)) , rtrim(substring( ltrim(rtrim( @P33 )) , 1, 16)) , convert(float, str(round( convert(decimal(25,9), @P34 ) , 2), 16, 2)) , convert(float, str(round( convert(decimal(25,9), @P35 ) , 2), 16, 2)) , convert(float, str(round( convert(decimal(25,9), @P36 ) , 2), 16, 2)) , convert(float, str(round( convert(decimal(25,9), @P37 ) , 4), 10, 4)) , rtrim(substring( ltrim(rtrim( @P38 )) , 1, 3)) , rtrim(substring( ltrim(rtrim( @P39 )) , 1, 20)) , rtrim(substring( ltrim(rtrim( @P40 )) , 1, 24)) , rtrim(substring( ltrim(rtrim( @P41 )) , 1, 16)) , rtrim(substring( ltrim(rtrim( @P42 )) , 1, 6)) , ltrim(rtrim( @P43 )) , convert( int , str( @P44 , 13, 0)) , convert( int , str( @P45 , 13, 0)) )',N'@P1 varchar(8),@P2 bigint,@P3 varchar(1),@P4 varchar(1),@P5 varchar(1),@P6 bigint,@P7 bigint,@P8 bigint,@P9 varchar(8),@P10 varchar(6),@P11 varchar(1),@P12 varchar(1),@P13 float,@P14 float,@P15 float,@P16 float,@P17 float,@P18 varchar(9),@P19 varchar(8),@P20 varchar(1),@P21 varchar(1),@P22 varchar(7),@P23 varchar(1),@P24 varchar(1),@P25 varchar(1),@P26 varchar(1),@P27 varchar(1),@P28 varchar(1),@P29 varchar(1),@P30 varchar(1),@P31 varchar(1),@P32 varchar(1),@P33 varchar(1),@P34 float,@P35 float,@P36 float,@P37 float,@P38 varchar(1),@P39 varchar(1),@P40 varchar(1),@P41 varchar(1),@P42 varchar(1),@P43 varchar(1),@P44 bigint,@P45 bigint','07030492',2,'','A','',0,12621600,10516320,'30/12/07','111111','','',112000,0,0,0,0,'189604.21','49158.47','','','UNKNOWN','','','','','','','','','','','',0,0,0,0,'','','','','','',0,1Thu, 01 Nov 2012 02:11:42 GMTMad-Dog