Microsoft KB Archive/306230

= INF: How to Check the ANSI Settings Under Parsing Time =

Article ID: 306230

Article Last Modified on 10/30/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q306230



SUMMARY
SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within a batch or stored procedure do not affect that batch or stored procedure. Instead, the settings that are used for statements inside the batch or stored procedure are the settings that are in effect when the batch or stored procedure is created.



MORE INFORMATION
The point at which an ANSI setting takes effect depends upon whether the setting is a parse-time setting or an execute-time setting. A parse-time setting takes effect during parsing, as the setting is encountered in text, without regard to the control of flow statements. An execute-time setting takes effect during the execution of the code in which the execute-time setting is specified.

The execution-time setting is determined by the settings for the current connection.

The parse-time setting is stored on disk, which you can view by using one of the following methods.

The steps that follow show you how to view the parsed-time ANSI_NULLS and QUOTED_IDENTIFIER settings for stored procedures, triggers and view objects.   In the SQL Server Query Analyzer, create the following stored procedure: set ansi_nulls on go set quoted_identifier on go

use pubs go create proc test_settings as print 'The SP test ANSI settings'   Then, use the OBJECTPROPERTY function: select objectproperty(object_id('Test_settings'),'ExecIsQuotedIdentOn') AS 'Quoted identifier', objectproperty(object_id('Test_settings'),'ExecIsAnsiNullsOn') as 'Ansi nulls'   Create a Microsoft Visual Basic script to use SQL Distributed Management Objects (SQL-DMO). A SQL-DMO object provides an interface that allows you to examine the Ansi_Nulls and Quoted_Identifier Status for stored procedures, triggers and view objects. 'file: Storedprocedure.vbs 'purpose: demonstrate SQLDMO to retrieve proc property

dim oSQLServer dim db dim oStoredProcedure

set oSQLServer = WScript.CreateObject(&quot;SQLDMO.SQLServer&quot;) oSQLServer.Connect &quot; &quot;, &quot;sa&quot;, &quot; &quot;

set db = oSQLServer.databases(&quot;pubs&quot;)

set oStoredProcedure = db.StoredProcedures(&quot;test_settings&quot;, &quot;dbo&quot;)

WScript.echo &quot;stored procedure's ANSINULL And Quoted Identifier: &quot; & oStoredProcedure.AnsiNullsStatus & &quot; &quot; & oStoredProcedure.QuotedIdentifierStatus 

Keywords: kbinfo KB306230

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.