Sql ANSI Padding: ANSI

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. 
 
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). 
 
Hope this helps. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
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--

2012年2月9日星期四

ANSI_DEFAULTS

没有评论:

发表评论

Sql ANSI Padding

Blog Archive

About Me