Solved

how to read error msg from sql server

Posted on 2014-11-30
13
130 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 48

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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 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
 
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 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 4

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

821 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