Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

quickly parse the query for the problem Expand / Collapse
Author
Message
Posted Thursday, November 1, 2012 2:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, October 12, 2014 1:52 PM
Points: 293, Visits: 974
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
Posted Thursday, November 1, 2012 4:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:22 AM
Points: 167, Visits: 691
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
Posted Thursday, November 1, 2012 5:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
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
Posted Thursday, November 1, 2012 6:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:02 PM
Points: 20,734, Visits: 32,515
It would also help if we could see the DDL (CREATE TABLE statement) for the table involved.



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
Posted Thursday, November 1, 2012 7:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Posted Thursday, November 1, 2012 9:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, October 12, 2014 1:52 PM
Points: 293, Visits: 974
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 »

Add to briefcase

Permissions Expand / Collapse