Solved

how to read error msg from sql server

Posted on 2014-11-30
13
118 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
13 Comments
 
LVL 24

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 7

Expert Comment

by:Ray
ID: 40473063
0
 
LVL 45

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
 
LVL 3

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 3

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 24

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

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 24

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 3

Author Closing Comment

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

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 3

Author Comment

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

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 24

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now