ANSI_DEFAULTS

I have a 6.5 and 2000 db that allows over flow of data to be entered into the
fields, but I have another 2000 db that does not allow over flow of data to
be entered. How can I check the ansi_defaults in the db's? I went through
the book ol but it didn't explain how to see those values. Only how to set
it using the SET option. Which was SET ANSI_DEFAULTS off go. Do you do
this in qa? Does it set it for the whole db or just my session? I need to
change it for all logins. Thanks.
New SQL Server DBA
SET
<http://msdn.microsoft.com/library/en...t-set_40c4.asp>
options are applicable to the current session only (i.e. for the current
connection between the SQL client and the SQL server). A couple of them
(ANSI_NULLS
<http://msdn.microsoft.com/library/en...t-set_9rec.asp>
and ANSI_WARNINGS
<http://msdn.microsoft.com/library/en...t-set_6d2r.asp>)
have defaults that can be changed at the database level with sp_dboption
<http://msdn.microsoft.com/library/en...da-di_8c32.asp>
but they are still session options and can be changed by the user for
his/her session after the client connection has been established.
To see the current set of session options use DBCC USEROPTIONS
<http://msdn.microsoft.com/library/en..._dbcc_4vhv.asp>. As
you've already read, you change session options with the SET statement
(eg. SET ANSI_NULLS ON). These are all T-SQL commands so you need to
run them in your favourite T-SQL parser/client, such as Query Analyser
for instance. If you've written an application that talks to a SQL
server and you wish to play with session options for the connections
from the application, then you'll need to get the client app to send the
appropriate SET statements to the server when it establishes a
connection to the SQL Server (i.e. after the SQL login handshake).
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Newbie wrote:

>I have a 6.5 and 2000 db that allows over flow of data to be entered into the
>fields, but I have another 2000 db that does not allow over flow of data to
>be entered. How can I check the ansi_defaults in the db's? I went through
>the book ol but it didn't explain how to see those values. Only how to set
>it using the SET option. Which was SET ANSI_DEFAULTS off go. Do you do
>this in qa? Does it set it for the whole db or just my session? I need to
>change it for all logins. Thanks.
>