quickly parse the query for the problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » quickly parse the query for the problem

quickly parse the query for the problem

Rate Topic

Display Mode

Topic Options

Author

Message

Mad-Dog

Posted Thursday, November 01, 2012 2:11 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:06 AM
Points: 283, Visits: 943

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

Post #1379664

bleroy

Posted Thursday, November 01, 2012 4:57 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:59 PM
Points: 137, Visits: 546

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

Post #1379732

Lowell

Posted Thursday, November 01, 2012 5:52 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 11:27 AM
Points: 11,622, Visits: 27,683

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1379759

Lynn Pettis

Posted Thursday, November 01, 2012 6:54 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 21,600, Visits: 27,422

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

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1379787

ChrisM@Work

Posted Thursday, November 01, 2012 7:40 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 5,608, Visits: 10,970

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1379809

Mad-Dog

Posted Thursday, November 01, 2012 9:42 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:06 AM
Points: 283, Visits: 943

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.

Post #1379895

« Prev Topic | Next Topic »

Permissions