I have a 6.5 and 2000 db that allows over flow of data to be entered into th
e
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 DBASET
<http://msdn.microsoft.com/library/e...et-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/e...et-set_9rec.asp>
and ANSI_WARNINGS
<http://msdn.microsoft.com/library/e...et-set_6d2r.asp> )
have defaults that can be changed at the database level with sp_dboption
<http://msdn.microsoft.com/library/e..._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/e...s_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 t
he
>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.
>
2012年2月9日星期四
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.
>
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.
>
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 DBAThis is a multi-part message in MIME format.
--080709080600030700010802
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
SET
<http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-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-us/tsqlref/ts_set-set_9rec.asp>
and ANSI_WARNINGS
<http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_6d2r.asp>)
have defaults that can be changed at the database level with sp_dboption
<http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_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-us/tsqlref/ts_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.
>
--080709080600030700010802
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt><a
href="http://links.10026.com/?link=SET</a>">http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_40c4.asp">SET</a>
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 (<a
href="http://links.10026.com/?link=ANSI_NULLS</a>">http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_9rec.asp">ANSI_NULLS</a>
and <a
href="http://links.10026.com/?link=ANSI_WARNINGS</a>)">http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_6d2r.asp">ANSI_WARNINGS</a>)
have defaults that can be changed at the database level with <a
href="http://links.10026.com/?link=sp_dboption</a>">http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_da-di_8c32.asp">sp_dboption</a>
but they are still session options and can be changed by the user for
his/her session after the client connection has been established.<br>
<br>
To see the current set of session options use <a
href="http://links.10026.com/?link=DBCC">http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_4vhv.asp">DBCC
USEROPTIONS</a>. 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).<br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Newbie wrote:
<blockquote cite="midD2CC4D79-B040-47C4-ACDF-FF70C68D90F9@.microsoft.com"
type="cite">
<pre wrap="">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.
</pre>
</blockquote>
</body>
</html>
--080709080600030700010802--
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 DBAThis is a multi-part message in MIME format.
--080709080600030700010802
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
SET
<http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-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-us/tsqlref/ts_set-set_9rec.asp>
and ANSI_WARNINGS
<http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_6d2r.asp>)
have defaults that can be changed at the database level with sp_dboption
<http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_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-us/tsqlref/ts_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.
>
--080709080600030700010802
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt><a
href="http://links.10026.com/?link=SET</a>">http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_40c4.asp">SET</a>
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 (<a
href="http://links.10026.com/?link=ANSI_NULLS</a>">http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_9rec.asp">ANSI_NULLS</a>
and <a
href="http://links.10026.com/?link=ANSI_WARNINGS</a>)">http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_6d2r.asp">ANSI_WARNINGS</a>)
have defaults that can be changed at the database level with <a
href="http://links.10026.com/?link=sp_dboption</a>">http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_da-di_8c32.asp">sp_dboption</a>
but they are still session options and can be changed by the user for
his/her session after the client connection has been established.<br>
<br>
To see the current set of session options use <a
href="http://links.10026.com/?link=DBCC">http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_4vhv.asp">DBCC
USEROPTIONS</a>. 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).<br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Newbie wrote:
<blockquote cite="midD2CC4D79-B040-47C4-ACDF-FF70C68D90F9@.microsoft.com"
type="cite">
<pre wrap="">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.
</pre>
</blockquote>
</body>
</html>
--080709080600030700010802--
ANSI Questions
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. Does it set it for the whole db or just my session? I need to
change it for all logins to be consistant. ThanksLOOK UP @.@.Options in BOL - Here's a script that makes use of it.
begin
-----------------------
-- Declarations
-----------------------
declare
@.Err Int,
@.RetVal Int,
@.ProcName sysname
declare @.Definitions table (
BitMask int,
OptionName nvarchar(25),
Description nvarchar(500)
)
-----------------------
-- Initialization
-----------------------
Set @.Err = 0 /* 0 ==> OK */
Set @.ProcName = 'DisplaySetOptions Script'
insert into @.Definitions (
BitMask, OptionName, Description
)
select 1, 'DISABLE_DEF_CNST_CHK', 'Controls interim or deferred constraint checking.'
union all select 2, 'IMPLICIT_TRANSACTIONS', 'Controls whether a transaction is started implicitly when a statement is executed.'
union all select 4, 'CURSOR_CLOSE_ON_COMMIT', 'Controls behavior of cursors after a commit operation has been performed.'
union all select 8, 'ANSI_WARNINGS', 'Controls truncation and NULL in aggregate warnings.'
union all select 16, 'ANSI_PADDING', 'Controls padding of fixed-length variables.'
union all select 32, 'ANSI_NULLS', 'Controls NULL handling when using equality operators.'
union all select 64, 'ARITHABORT', 'Terminates a query when an overflow or divide-by-zero error occurs during query execution.'
union all select 128, 'ARITHIGNORE', 'Returns NULL when an overflow or divide-by-zero error occurs during a query.'
union all select 256, 'QUOTED_IDENTIFIER', 'Differentiates between single and double quotation marks when evaluating an expression.'
union all select 512, 'NOCOUNT', 'Turns off the message returned at the end of each statement that states how many rows were affected.'
union all select 1024, 'ANSI_NULL_DFLT_ON', 'Alters the session''s behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.'
union all select 2048, 'ANSI_NULL_DFLT_OFF', 'Alters the session''s behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined not to allow nulls.'
union all select 4096, 'CONCAT_NULL_YIELDS_NULL', 'Returns NULL when concatenating a NULL value with a string.'
union all select 8192, 'NUMERIC_ROUNDABORT', 'Generates an error when a loss of precision occurs in an expression.'
union all select 16384, 'XACT_ABORT', 'Rolls back a transaction if a Transact- SQL statement raises a run-time error.'
-----------------------
-- Body of Procedure
-----------------------
select OptionName,
case when BitMask & @.@.Options = BitMask then 'ON' else '--' end Value,
Description
from @.Definitions
order by OptionName
if @.@.error <> 0
begin
set @.Err = -1 /* -1 ==> Error: ... */
goto CommonExit
end
-----------------------
-- Clean-Up and Exit
-----------------------
CommonExit:
print '@.Err = ' + cast(@.Err as varchar(10))
end
go
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. Does it set it for the whole db or just my session? I need to
change it for all logins to be consistant. ThanksLOOK UP @.@.Options in BOL - Here's a script that makes use of it.
begin
-----------------------
-- Declarations
-----------------------
declare
@.Err Int,
@.RetVal Int,
@.ProcName sysname
declare @.Definitions table (
BitMask int,
OptionName nvarchar(25),
Description nvarchar(500)
)
-----------------------
-- Initialization
-----------------------
Set @.Err = 0 /* 0 ==> OK */
Set @.ProcName = 'DisplaySetOptions Script'
insert into @.Definitions (
BitMask, OptionName, Description
)
select 1, 'DISABLE_DEF_CNST_CHK', 'Controls interim or deferred constraint checking.'
union all select 2, 'IMPLICIT_TRANSACTIONS', 'Controls whether a transaction is started implicitly when a statement is executed.'
union all select 4, 'CURSOR_CLOSE_ON_COMMIT', 'Controls behavior of cursors after a commit operation has been performed.'
union all select 8, 'ANSI_WARNINGS', 'Controls truncation and NULL in aggregate warnings.'
union all select 16, 'ANSI_PADDING', 'Controls padding of fixed-length variables.'
union all select 32, 'ANSI_NULLS', 'Controls NULL handling when using equality operators.'
union all select 64, 'ARITHABORT', 'Terminates a query when an overflow or divide-by-zero error occurs during query execution.'
union all select 128, 'ARITHIGNORE', 'Returns NULL when an overflow or divide-by-zero error occurs during a query.'
union all select 256, 'QUOTED_IDENTIFIER', 'Differentiates between single and double quotation marks when evaluating an expression.'
union all select 512, 'NOCOUNT', 'Turns off the message returned at the end of each statement that states how many rows were affected.'
union all select 1024, 'ANSI_NULL_DFLT_ON', 'Alters the session''s behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.'
union all select 2048, 'ANSI_NULL_DFLT_OFF', 'Alters the session''s behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined not to allow nulls.'
union all select 4096, 'CONCAT_NULL_YIELDS_NULL', 'Returns NULL when concatenating a NULL value with a string.'
union all select 8192, 'NUMERIC_ROUNDABORT', 'Generates an error when a loss of precision occurs in an expression.'
union all select 16384, 'XACT_ABORT', 'Rolls back a transaction if a Transact- SQL statement raises a run-time error.'
-----------------------
-- Body of Procedure
-----------------------
select OptionName,
case when BitMask & @.@.Options = BitMask then 'ON' else '--' end Value,
Description
from @.Definitions
order by OptionName
if @.@.error <> 0
begin
set @.Err = -1 /* -1 ==> Error: ... */
goto CommonExit
end
-----------------------
-- Clean-Up and Exit
-----------------------
CommonExit:
print '@.Err = ' + cast(@.Err as varchar(10))
end
go
ANSI Questions
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. Does it set
it for the whole db or just my session? I need to
change it for all logins to be consistant. Thanks.
New SQL Server DBA
Hi,
"SET commands are just activated only for that specific session not for the
entire database."
For database level see the ALTER DATABASE and use below options . This will
set database level.
ANSI_NULL_DEFAULT { ON | OFF }
| ANSI_NULLS { ON | OFF }
| ANSI_PADDING { ON | OFF }
| ANSI_WARNINGS { ON | OFF }
| ARITHABORT { ON | OFF }
Thanks
Hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:2B1DC564-7FC0-40DF-A0AE-292D8840EDEE@.microsoft.com...
>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. Does it
> set
> it for the whole db or just my session? I need to
> change it for all logins to be consistant. Thanks.
> --
> New SQL Server DBA
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. Does it set
it for the whole db or just my session? I need to
change it for all logins to be consistant. Thanks.
New SQL Server DBA
Hi,
"SET commands are just activated only for that specific session not for the
entire database."
For database level see the ALTER DATABASE and use below options . This will
set database level.
ANSI_NULL_DEFAULT { ON | OFF }
| ANSI_NULLS { ON | OFF }
| ANSI_PADDING { ON | OFF }
| ANSI_WARNINGS { ON | OFF }
| ARITHABORT { ON | OFF }
Thanks
Hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:2B1DC564-7FC0-40DF-A0AE-292D8840EDEE@.microsoft.com...
>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. Does it
> set
> it for the whole db or just my session? I need to
> change it for all logins to be consistant. Thanks.
> --
> New SQL Server DBA
ANSI Questions
I have a 6.5 and 2000 db that allows over flow of data to be entered into th
e
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. Does it set
it for the whole db or just my session? I need to
change it for all logins to be consistant. Thanks.
New SQL Server DBAHi,
"SET commands are just activated only for that specific session not for the
entire database."
For database level see the ALTER DATABASE and use below options . This will
set database level.
ANSI_NULL_DEFAULT { ON | OFF }
| ANSI_NULLS { ON | OFF }
| ANSI_PADDING { ON | OFF }
| ANSI_WARNINGS { ON | OFF }
| ARITHABORT { ON | OFF }
Thanks
Hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:2B1DC564-7FC0-40DF-A0AE-292D8840EDEE@.microsoft.com...
>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. Does it
> set
> it for the whole db or just my session? I need to
> change it for all logins to be consistant. Thanks.
> --
> New SQL Server DBA
e
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. Does it set
it for the whole db or just my session? I need to
change it for all logins to be consistant. Thanks.
New SQL Server DBAHi,
"SET commands are just activated only for that specific session not for the
entire database."
For database level see the ALTER DATABASE and use below options . This will
set database level.
ANSI_NULL_DEFAULT { ON | OFF }
| ANSI_NULLS { ON | OFF }
| ANSI_PADDING { ON | OFF }
| ANSI_WARNINGS { ON | OFF }
| ARITHABORT { ON | OFF }
Thanks
Hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:2B1DC564-7FC0-40DF-A0AE-292D8840EDEE@.microsoft.com...
>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. Does it
> set
> it for the whole db or just my session? I need to
> change it for all logins to be consistant. Thanks.
> --
> New SQL Server DBA
ANSI Questions
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. Does it set
it for the whole db or just my session? I need to
change it for all logins to be consistant. Thanks.
--
New SQL Server DBAHi,
"SET commands are just activated only for that specific session not for the
entire database."
For database level see the ALTER DATABASE and use below options . This will
set database level.
ANSI_NULL_DEFAULT { ON | OFF }
| ANSI_NULLS { ON | OFF }
| ANSI_PADDING { ON | OFF }
| ANSI_WARNINGS { ON | OFF }
| ARITHABORT { ON | OFF }
Thanks
Hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:2B1DC564-7FC0-40DF-A0AE-292D8840EDEE@.microsoft.com...
>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. Does it
> set
> it for the whole db or just my session? I need to
> change it for all logins to be consistant. Thanks.
> --
> New SQL Server DBA
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. Does it set
it for the whole db or just my session? I need to
change it for all logins to be consistant. Thanks.
--
New SQL Server DBAHi,
"SET commands are just activated only for that specific session not for the
entire database."
For database level see the ALTER DATABASE and use below options . This will
set database level.
ANSI_NULL_DEFAULT { ON | OFF }
| ANSI_NULLS { ON | OFF }
| ANSI_PADDING { ON | OFF }
| ANSI_WARNINGS { ON | OFF }
| ARITHABORT { ON | OFF }
Thanks
Hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:2B1DC564-7FC0-40DF-A0AE-292D8840EDEE@.microsoft.com...
>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. Does it
> set
> it for the whole db or just my session? I need to
> change it for all logins to be consistant. Thanks.
> --
> New SQL Server DBA
订阅：
博文 (Atom)