INF: How to Check the ANSI Settings Under Parsing Time (306230)

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.

1. 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'
   					

2. Then, use the OBJECTPROPERTY function:

   select objectproperty(object_id('Test_settings'),'ExecIsQuotedIdentOn') AS 
   'Quoted identifier', 
   objectproperty(object_id('Test_settings'),'ExecIsAnsiNullsOn') as 'Ansi 
   nulls'
   					

3. 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("SQLDMO.SQLServer")
   oSQLServer.Connect "<server name>", "sa", "<password>"
   
   set db = oSQLServer.databases("pubs")
   
   set oStoredProcedure = db.StoredProcedures("test_settings", "dbo") 
   
   
   WScript.echo "stored procedure's ANSINULL And Quoted Identifier: " 
     & oStoredProcedure.AnsiNullsStatus & " " & oStoredProcedure.QuotedIdentifierStatus