Hi There
This one has bothered me ever since sql server 2000.
When you do an insert into a table with literally hundreds of char or varchar columns and you get the error that the insert failed due to data loss/truncation on a column.
Is there anyway in 2005 to actually find out what column ? Since there are hundreds is is literally a long process of going though each column 1 by 1 manually.
The database engine surely MUST know what column this occurred on so why can it not tell you which column the truncation occurred on ?
Can this be done in 2005 if not will this information be available in 2008 ?
Thanx
Nope. It is not available on any version (2000,2005 & 2008).
The SQL Server message will be more generic than the specific, bcs the error caused by the engine treat all the object same, there is no special error handler written for object based.
Here you want to throw an error – for the table with specific column name. These are data definition & data error. These can be controlled.
|||Hi Manivannan
Thank you for the reply, please could you elaborate on
"Here you want to throw an error - for the table with specific column name"
How exactly would one do that, as far as i know the try catch metod will return the same error without a column name ?
Thanx
|||You can check insert operations like in following example :
The test table:
CREATE TABLE [dbo].[Atable](
[varchar](5) NULL,
[varchar](5) NULL
) ON [secondary]
use following sp :
create procedure CheckInsert
@.i varchar(5000),@.j varchar(5000)
as
BEGIN
DECLARE @.COLLENGTH int
declare @.GoodRow bit
set @.COLLENGTH=0
set @.goodrow=1
select @.COLLENGTH =(SELECT sys.columns.max_length
FROM sys.columns INNER JOIN
sys.tables ON sys.columns.object_id = sys.tables.object_id
WHERE (sys.tables.name = N'ATABLE') and (sys.columns.name='a'))
if len(@.i)>@.collength
begin
print 'a has a big value'
print @.i
set @.goodrow=0
end
-- ...
if @.goodrow=1
INSERT INTO [test].[dbo].[Atable](,) VALUES (@.i,@.j)
END
if you run :
exec checkinsert '12345678','12'
the output is:
a has a big value
12345678
|||Hi ggciubuc
Thanx for the reply.
Correct me if i am wrong but your proc will only check if you are inserting a value bigger than the max length of the largest column of a table.
So for example if i have a table with a hundred varchar columns most if which are over 100 in length , but my insert is inserting a char(6) value in to 1 of 30 char(5) columns it will still be very difficult to find the problem column. And your sql will not pick it up.
You sql will only find the issue if you are exceeding the length of your max char lenghth column, not any columns smaller than the max char length.
Thanx
|||First you can optimize my sp creating a function let's say LengthColumn that return the max length of the column,
before insert you can verify all your 30 parameters and you can write a string by concatenating message like
'a has a big value'
and finally raise an error that write in event log
I thought your problem is , I quote
"When you do an insert into a table with literally hundreds of char or varchar columns and you get the error that the insert failed due to data loss/truncation on a column.
"
In your last post you say
"You sql will only find the issue if you are exceeding the length of your max char lenghth column, not any columns smaller than the max char length."
I think is not a problem vis-a-vis "data loss/truncation on a column".
Anyway in my code you can verify that length:
if len(@.i)>@.collength
begin
...
using
if len(@.i) < @.collength
begin
...
|||Hi
Yes you are correct, the stored proc can be modified to check column by column.
It is just time consuming, i guess my main point was i thought it would be a simple things for the DB engine to actually return the column that the truncation was hapeening on, or some sort of easy way to figure out the column.
Even with your code it would be different for each table, and i have the issue of a result set of thousands of rows , so i dont know which insert is causing the problem.Therefore i would not know what parameters of which insert to pass to the store proc.
So it is a bit more complicated then i originally explained, bottom line there is no way sql server will tell you which column insert exceeded the length of the column , i was hoping there was an easy work around.
Thanx
