Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

how to read error msg from sql server

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
bfuchs
Asked:
bfuchs
1 Solution
 
chaauCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
bfuchsAuthor Commented:
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
 
bfuchsAuthor Commented:
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
 
chaauCommented:
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
 
bfuchsAuthor Commented:
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
 
chaauCommented:
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
 
bfuchsAuthor Commented:
I got it to work,
Thanks a million!
0
 
chaauCommented:
The real question is: did it show you the column(s) where you are trying to insert excessive data?
0
 
bfuchsAuthor Commented:
Yes of course it did.
0
 
bfuchsAuthor Commented:
My question is, why cant those sites (that Ray posted) show something short and useful like this?f
0
 
chaauCommented:
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now