String or binary data would be truncated

[TZone]

Is there anything I can put in a stored procedure so instead of proc falling
over because of "String or binary data would be truncated" the offending
records are just truncated?

Thanks for any help!

[Erland Sommarskog]

Re: String or binary data would be truncated

TZone (TZone@optus.co m.au) writes:[color=blue]
> Is there anything I can put in a stored procedure so instead of proc
> falling over because of "String or binary data would be truncated" the
> offending records are just truncated?[/color]

Yes, you can include SET ANSI_WARNINGS OFF.

Two caveats:

o ANSI_WARNINGS must be on for queries that involves linked servers,
indexed views and indexes on computed columns. Thus setting
ANSI_WARNINGS OFF can cause the query to fail to run much slower.
o Putting SET ANSI_WARNINGS OFF in a procedure will trigger a
recompilation of the procedure, so it might be better to set it
before calling the procedure.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp