Only enable the string truncation prevention of ANSI_WARNINGS

[David Sharp]

I'm working with some long standing VB/SQL Server applications and for
the second time we've suffered from having the parameters to a stored
procedure call get silently truncated now that the data field has got
much larger than when the code was developed all those years ago. This
is always very hard to debug and I'd really like SQL Server to throw
an error when this happens.

I don't feel confident enablying the full ANSI_WARNINGS as it is
likely to affect lots of functionality in the database in
unanticipated ways.

What I'd like to be able to do is enable only the ANSI check for the
string data getting truncated but haven't been able to find a way to
do this. Is it possible?

Cheers
Dave

[Simon Hayes]

"David Sharp" <da**@daveandca z.freeserve.co. uk> wrote in message
news:ca******** *************** ***@posting.goo gle.com...

I'm working with some long standing VB/SQL Server applications and for
the second time we've suffered from having the parameters to a stored
procedure call get silently truncated now that the data field has got
much larger than when the code was developed all those years ago. This
is always very hard to debug and I'd really like SQL Server to throw
an error when this happens.

I don't feel confident enablying the full ANSI_WARNINGS as it is
likely to affect lots of functionality in the database in
unanticipated ways.

What I'd like to be able to do is enable only the ANSI check for the
string data getting truncated but haven't been able to find a way to
do this. Is it possible?

Cheers
Dave

There's no 'subset' of ANSI_WARNINGS which will only raise an error on
string truncation, so your best bet is to fix your application, either by
making the stored proc parameter longer, or by validating the input in the
front end.

If neither of those are possible, then there aren't many options left,
except perhaps to raise an error if the parameter value is the same size as
the maximum possible size of the parameter data type. So if you have
char(20), assume that only values up to 19 characters are valid. But it
would be much better to fix the problem at the source, and validate the
input.

Simon

[Erland Sommarskog]

David Sharp (da**@daveandca z.freeserve.co. uk) writes:

I'm working with some long standing VB/SQL Server applications and for
the second time we've suffered from having the parameters to a stored
procedure call get silently truncated now that the data field has got
much larger than when the code was developed all those years ago. This
is always very hard to debug and I'd really like SQL Server to throw
an error when this happens.

I don't feel confident enablying the full ANSI_WARNINGS as it is
likely to affect lots of functionality in the database in
unanticipated ways.

What I'd like to be able to do is enable only the ANSI check for the
string data getting truncated but haven't been able to find a way to
do this. Is it possible?

To add to what Simon said, you would probably have any use for ANSI_WARNINGS
anyway. ANSI_WARNINGS produces an error if you try to assign a column
a value which is too long. However, variable and parameter assignment
still truncates silently, even with ANSI_WARNINGS ON.

I would however encourage you to switch to ANSI_WARNINGS for other reasons.
This setting is required is some contexts, more precisely in distributed
queries and when you used indexed views and indexed computed columns.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

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

[David Sharp]

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* **************@ 127.0.0.1>...

I would however encourage you to switch to ANSI_WARNINGS for other reasons.
This setting is required is some contexts, more precisely in distributed
queries and when you used indexed views and indexed computed columns.

Thanks for your help. Sounds like there's no short cut to identify
when this happens. We'll have to physically go through and make sure
it is checked for in each sproc.

Cheers
Dave