Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Change the Settings of a Database Object Using Powershell

By Ravi Kumar SV,

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:

Total article views: 1981 | Views in the last 30 days: 5
 
Related Articles
ARTICLE

Stairway to SQL PowerShell Level 4: Objects in SQL PowerShell

This far, we have learned about installation and setup of the PowerShell environment. You should now...

FORUM

dynamic object(database/schema/obj) in stored procedure

how to reference multiple database/objects in stored procedure (parameterization)

FORUM

Which Stored Procedure changed

Which Stored Procedure changed

FORUM

How to change object owner......

Change Object Owner

ARTICLE

Powershell Database Backup Script

Learn how to write a script for regular database backups using Powershell and SMO.

Tags
ansi_nulls    
powershell    
quoted_identifier    
textmode    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones