In this article, I will show how to change the QUOTED_IDENTIFIER and ANSI_NULLS settings of a database object using Powershell. We may be required to change these settings without changing the contents/definition of a database object. For example, when you add a computed column in a table, it  will impact the stored procedures referring to this table. If you have the above setting OFF, this requires all the objects that were created with these settings ON to be recompiled.

Suppose you receive a requirement to add a persisted computed column in one table and once you add this column in the table, the stored procedure or the trigger that performs an INSERT on this table fails with the following error:

Msg 1934, Level 16, State 1, Procedure usp_Proc1, Line 29

INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

The above error says that the ANSI_NULLS and QUOTED_IDENTIFIER settings are OFF, but should be set to ON to get rid of this error. There are many ways we can change these settings from OFF to ON, but I chose Powershell. Now I will show how to write a Powershell script to change these settings to ON.

Below are the simple four steps to write the Powershell script:

1. Add a reference to the .Net SMO Assembly.

   [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

2. Create a new SMO server object, which makes connection to your SQL Server instance. Replace “SERVER1” with the relevant server name.

   $svr = new-object ("Microsoft.SqlServer.Management.Smo.Server") "SERVER1"

3. Create a new database variable referring to the database used to change the settings of the database objects. Replace “Database1” with the relevant database name.

   $db = $svr.Databases["Database1"]

4. Start a foreach loop to connect to all the stored procedures in the database. For each object check if the object has QUOTED_IDENTIFIER and ANSI_NULLS are set to OFF (False). If so, change the setting to ON (True). After changing the setting to ON, change the TextMode propery to False to specify that the text header is not editable and finally alter the objects using Alter() method of the stored procedure object.

   foreach ($sp in $db.StoredProcedures) {
       if ($sp.IsSystemObject -eq $False) {
           if ($sp.QuotedIdentifierStatus -eq $False) {
               $sp.QuotedIdentifierStatus = $True;
           }
           if ($sp.AnsiNullsStatus -eq $False) {
               $sp.AnsiNullsStatus = $True;
           }
           $sp.TextMode = $False
           $sp.Alter()
       }
   }

5. Step 4 can be repeated for triggers or other database objects by referring to the objects inside the same database object variable ($db).

I can write the same script in VB or C#, but I found Powershell to be a simple tool for writing this type of script. This is easier for the Administrator who deploys the scripts in the live server.

You may be interested in these reference links on Powershell:

• http://technet.microsoft.com/en-us/library/hh857337.aspx
• http://technet.microsoft.com/en-us/scriptcenter/dd742419.aspx