Solved

how to read error msg from sql server

Posted on 2014-11-30
13
128 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 47

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.net and sql server 4 36
Connecting to multiple databases to create a Dashboard 5 26
sql server query 6 9
syntax sql error 2 13
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

810 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