SDU Tools: Fixing ANSI Nulls and Quoted Identifier settings for Stored Procedures

When you create a SQL Server stored procedure, some session-related settings are part of the configuration of the procedure. The two most common problems with this are related to ANSI_NULLS and QUOTED_IDENTIFIER.

When procedures are scripted, SQL Server tools also script the settings for the session, but what often happens is that the person creating the procedure, just executes the CREATE PROC statement and doesn’t execute the SET statements before it. When they do this, you can end up with procedures that don’t have the settings you expect.

So in our free SDU Tools for developers and DBAs, we added a tool that can be used to fix this situation. It’s called SetAnsiNullsQuotedIdentifierForStoredProcedures. It’s a long name but hopefully describes exactly what it does. It can turn either setting on or off.

It takes the following parameters:

@DatabaseName sysname -> Database name for the procedures to be altered @SchemaName sysname -> Schema name for the procedure to be altered or ALL @ProcedureName sysname -> Procedure name for the procedure to be altered or ALL @NeedsAnsiNulls bit -> Should ANSI_NULLS be turned on? @NeedsQuotedIdentifier bit -> Should QUOTED_IDENTIFIER be turned on? @IHaveABackup bit -> Don’t do this without a backup (just in case)

Find out more

You can see it in action in the video here:

_FbvkZQ

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

2019-07-10