Error message "String or Binary data would be trunicated", " The statement has been terminated"

[Jeff Magouirk]

Dear All,

I have written an update trigger that should write a message to an audit
table When I try to update any field in the table I recieve the following
error message -

[ODBC Sql Server Driver] [Sql Server] Stirng or Binary data would be
trunicated
[ODBC Sql Server Driver] [Sql Server] The statement has been termined.

I have looked and what has been suggested is to use the response.write len()
to check the length of field that I am updating. Being fairly new to
SQL-Server, I do not know how to do this. Any help will be most welcome.

Thanks,

Jeff

[Erland Sommarskog]

[posted and mailed. please reply in news]

Jeff Magouirk (ma*******@njc.org) writes:

I have written an update trigger that should write a message to an audit
table When I try to update any field in the table I recieve the following
error message -

[ODBC Sql Server Driver] [Sql Server] Stirng or Binary data would be
trunicated
[ODBC Sql Server Driver] [Sql Server] The statement has been termined.

I have looked and what has been suggested is to use the response.write
len() to check the length of field that I am updating. Being fairly new
to SQL-Server, I do not know how to do this. Any help will be most
welcome.

The message means that you try to squeeze in a string into a column which
does not fit the string.

Judging from your description, the problem is in the trigger, so that is
probably the code you need to scrutinize. Response.write len() sounds
like somethnig webbish, so that is not likely to help you.

A common error is to declare a column as varchar, without specifying
any length. This results in a default of 1. In difference to client
languages, a string always has a max length in SQL. (Which has to do
with the fact that the string is to be persisted on disk.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Steve Troxell]

Jeff Magouirk wrote:

I have looked and what has been suggested is to use the
response.write len() to check the length of field that I am updating.
Being fairly new to SQL-Server, I do not know how to do this. Any
help will be most welcome.

Response.Write is an ASP thing, not a SQL thing. You can't reference
Response.Write within a trigger. Post your trigger and let's see what you
have. The error indicates that you are trying to put a string into a field
and the string is longer than the field is declared to be. You can suppress
the error with SET ANSI_WARNINGS OFF, but the data itself will still be
truncated to fit the field.

--
Steve Troxell