"String or binary data would be truncated" and field specifications

Hi all,

i have "String or binary data would be truncated" error when i try to execute an insert statment.

can i find witch field is affected by this error? (for return it to the user)

thank's all

If possible it would be far better to truncate the string to the maximum allowable length within the client application (and warn the user if necessary) before passing it to SQL Server for insertion into the database.

For debug purposes you could run SQL Profiler to witness the values of the parameters being passed into the stored procedure then work through the SQL code and locate where the error is being caused.

Chris

|||

this is not possible because client and db musn't be linked (db can be modified). i don't know futur size of these fields.

it's a feature for my users, indicating whitch field is too long

|||

As far as I am aware, there's no way to determine which column's length has been exceeded.

You should add code into your stored procedure to check the lengths of variables before inserting their values into your tables, raising an error if necessary - see the example below.

Again I stress that it would be better to modify the client application's code to either warn the user or to limit the number of characters they can enter into a field.

Chris

Code Snippet

--This batch will fail with the SQL Server error message

DECLARE @.MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @.MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @.MyParameter = REPLICATE('Z', 52)

INSERT INTO @.MyTable(MyValue)

VALUES (@.MyParameter)

GO

--This batch will fail with a custom error message

DECLARE @.MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @.MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @.MyParameter = REPLICATE('Z', 52)

IF LEN(@.MyParameter) > 10

BEGIN

RAISERROR('You attempted to insert too many characters into MyTable.MyValue.', 16, 1)

RETURN

END

ELSE

BEGIN

INSERT INTO @.MyTable(MyValue)

VALUES (@.MyParameter)

END

GO