quickly parse the query for the problem

Question

quickly parse the query for the problem

Mad-Dog

SSCertifiable

Points: 6117
More actions
November 1, 2012 at 2:11 am

#270350

Hi,
is there any quick way to find the problem in this query?
the error is:string or binary data would be truncated
the 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,1

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

bleroy Hall of Fame Points: 3052 More actions · Answer 1

Hi,

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,

B

Lowell SSC Guru Points: 323518 More actions · Answer 2

these 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:

@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

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.

'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

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

It would also help if we could see the DDL (CREATE TABLE statement) for the table involved.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Mad-Dog (11/1/2012)
Hi,
is there any quick way to find the problem in this query?
the error is:string or binary data would be truncated
the query:
exec sp_executesql

Why are you using dynamic sql for this?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 5

this 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.