Solved

how to read error msg from sql server

Posted on 2014-11-30
13
134 Views
Last Modified: 2014-12-02
Hi Experts,
I am trying to execute an insert sql statement that contains allot of fields, and getting the following message
"
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
Is there a way to determine which field/value is causing this?
0
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40473062
It is one of the nastiest habit of SQL Server to let you know that a data will not fit into a column. It is amazing that they have not fixed it in the later version, because I have seen it back in Sybase days.

Anyway, the easiest way to find it is by converting your "insert into" statement to "select into #temp" statement. When you insert data to the temp table you can compare the column sizes using a simple join, like this:

-- convert your existing statement
-- INSERT INTO DestTable (col1, col2, col3)
-- SELECT col1, col2, col3 FROM SourceTable
-- to this statement:
SELECT col1, col2, col3 
INTO #Temp FROM SourceTable;

-- Compare column types and sizes
select *
from tempdb.sys.columns as TempCols
INNER outer join MyDb.sys.columns as RealCols
  on TempCols.name = RealCols.name
  and TempCols.object_id = Object_ID(N'tempdb..#Temp')
  and RealCols.object_id = Object_ID(N'MyDb.dbo.DestTable)
where  RealCols.system_type_id <> TempCols.system_type_id
or RealCols.max_length < TempCols.max_length ; 

DROP TABLE #Temp;

Open in new window

Please note that the query above will check unmatched column by type (i.e. varchar vs nvarchar) and size
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40473243
String or binary data would be truncated.
At least you know that it's a column with string or binary datatype. I know isn't a big help but you can reduce the number of fields to verify.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:bfuchs
ID: 40474618
Hi Experts,

First of all thanks for taking the time to read and reply..

@chaau,
I need to modify your code a little to select values instead of records from table, I tried insert into #Temp values(...) and got error message Msg 208, Level 16, State 0, Line 1
Invalid object name '#Temp'.
Also what other things do I need to change in your code in order to match my scenario (like my db, table, column names etc.)?
@Ray,
I will look into and let you know.

@Vitor,
The problem here is that 90% of my fields here are nvarchar, so this sql indication would not be too much of a help.

Thanks,
Ben
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40477074
Hi Experts,

Can someone help me out with this, how to make chaau's suggestion work?

@Ray,
Those sites are suggesting something way above my sql skills, I would really need something simple like chaau's.

Thanks,
Ben
0
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 40477311
There you go: with a bit of change your INSERT INTO .... VALUES query can be converted to a SELECT INTO query:

-- convert your existing statement
-- INSERT INTO DestTable (col1, col2, col3)
-- VALUES('abc', 'cde', 'xyz')
-- to this statement:
SELECT 'acb' col1, 'cde' col2, 'xyz' col3 
INTO #Temp;

-- Compare column types and sizes
select *
from tempdb.sys.columns as TempCols
INNER outer join MyDb.sys.columns as RealCols
  on TempCols.name = RealCols.name
  and TempCols.object_id = Object_ID(N'tempdb..#Temp')
  and RealCols.object_id = Object_ID(N'MyDb.dbo.DestTable)
where  RealCols.system_type_id <> TempCols.system_type_id
or RealCols.max_length < TempCols.max_length ; 

DROP TABLE #Temp;

Open in new window

Basically, you take each value from the VALUES() clause and cut-n-paste it before the column name in your INSERT INTO() clause and put the SELECT in front
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40477433
Hi chaau,

The first part worked,
now about the select statement, I guess have to change N'MyDb.dbo.DestTable to my database and table name (and add missing apostrophe to the end)  correct me if I am wrong.
I tried that and got the following error:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'outer'.
Let me know,

Thanks,
Ben
0
 
LVL 25

Expert Comment

by:chaau
ID: 40477472
Try this code:

-- Compare column types and sizes
select *
from tempdb.sys.columns as TempCols
INNER join MyDb.sys.columns as RealCols
  on TempCols.name = RealCols.name
  and TempCols.object_id = Object_ID(N'tempdb..#Temp')
  and RealCols.object_id = Object_ID(N'MyDb.dbo.DestTable')
where  RealCols.system_type_id <> TempCols.system_type_id
or RealCols.max_length < TempCols.max_length ; 

DROP TABLE #Temp;

Open in new window

You need to change 'MyDb.dbo.DestTable' as well as MyDb.sys.columns. The MyDb should be the database where your DestTable table is
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 40477478
I got it to work,
Thanks a million!
0
 
LVL 25

Expert Comment

by:chaau
ID: 40477482
The real question is: did it show you the column(s) where you are trying to insert excessive data?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40477491
Yes of course it did.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40477499
My question is, why cant those sites (that Ray posted) show something short and useful like this?f
0
 
LVL 25

Expert Comment

by:chaau
ID: 40477510
I think the first site is actually quite good. It will create a stored procedure that will let you reuse in future. You give it an actual SQL INSERT statement and it will parse it to figure out what columns are used and what data is inserted. The stored procedure my look big, but its usage is actually easy. I recommend you re-visit the links (especially the first one) and re-read it. You can do this in your spare time
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question