SA0091 : Setting the QUOTED_IDENTIFIERS or ANSI_NULLS options inside stored procedure, trigger or function will have no effect

The topic describes the SA0091 analysis rule.

Message

Setting the QUOTED_IDENTIFIERS or ANSI_NULLS options inside stored procedure, trigger or function will have no effect

Description

The rule checks the usage of SET statement for QUOTED_IDENTIFIERS or ANSI_NULLS inside stored procedure body.

These options are ignored when set inside the body of stored procedures, trigger or functions.

How to fix

Remove SET QUOTED_IDENTIFIERS or SET ANSI_NULLS statements that appear inside stored procedure body.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
20 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE PROCEDURE test_proc AS
 2
 3SET QUOTED_IDENTIFIER OFF 
 4SET ANSI_NULLS OFF 
 5SET ARITHABORT OFF 
 6SET ANSI_NULL_DFLT_ON OFF
 7SET ANSI_DEFAULTS OFF
 8SET ANSI_WARNINGS OFF 
 9SET ANSI_PADDING OFF
10SET CONCAT_NULL_YIELDS_NULL OFF
11SET NUMERIC_ROUNDABORT ON 
12SET NOCOUNT ON 
13SET ROWCOUNT 100 
14SET XACT_ABORT ON 
15SET IMPLICIT_TRANSACTIONS ON
16SET ARITHIGNORE OFF 
17SET LOCK_TIMEOUT 1
18SET FMTONLY ON 
19SET NOEXEC ON
20SET PARSEONLY OFF
21
22SELECT au_lname, au_fname, au_id from authors
23WHERE au_lname like 'L%'
24--Option (Keep Plan)
Analysis Results
  Message Line Column
1 SA0091 : Setting the QUOTED_IDENTIFIER option will have no effect when done inside stored procedure, trigger or function. 3 4
2 SA0091 : Setting the ANSI_NULLS option will have no effect when done inside stored procedure, trigger or function. 4 4
See Also

Other Resources

Analysis Rules