Hi,

I am using SQL Server 2000.  I know that it is bad practice to truncate
data as it is lost but please could someone answer the following:

1. Can ANSI_WARNINGS be set to OFF on a table-specific basis, or is it
database-specific?  Basically, I want to be able to INSERT/UPDATE
certain tables by setting ANSI_WARNINGS to OFF just before the
INSERT/UPDATE then set it back to ON straight afterwards.  The reason
being, other users will be inserting/updating other tables at
potentially the same time as me and I do not want truncating to be
allowed on those tables.

2. I am using ASP/VBScript to insert/update the database.  Do I need to
simply include the words "SET ANSI_WARNINGS OFF" as part of the SQL
string that I execute, or do I need to execute this command first, then
execute the insert/update second?

3. If I set the value to OFF, will it remain OFF until someone sets it
back to ON?

Many thanks.

Jimmy

thatsMaBoy (jimmyfishb***@yahoo.co.uk) writes:
It's session-specific.

You can use SET ANSI_WARNINGS OFF to turn off warnings temporarily.
However, beware that if you do this in a query batch, the batch will
be recompile when the setting changes. The same applies if you use
the command in a stored procedure.

You don't need to have a seprate batch, but from what I said above, it's
better to send it separately for the sake of performance.

The effect of SET options lasts until the end of the scope. That is,
if you issue it in a stored procedure, the effect is reverted when
the procedure exits. If you issue it directly from the client, the
effect will last until you disconnect or issue a new SET command.


All this said: how difficult is to use substring to ensure that
truncation does not happen? Then you don't need to fiddle with
ANSI_WARNINGS off.

VIEW's ORDER BY columns necessary in column list
sql query - sp
FOREIGN KEY that REFERENCES a server login for a database?
connect to oracle database
User-defined function not show up after being created