SA0206 : The sp_configure store procedure executed with a deprecated option

The topic describes the SA0206 analysis rule.

Message

The sp_configure store procedure executed with a deprecated option

Description

The rule checks T-SQL code for EXECUTE statements of the sp_configure stored procedure, where the configuration option is for a deprecated feature,

These deprecated features options configuring feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server.

The following options are deprecated:

– ‘allow updates’

– ‘locks’

– ‘open objects’

– ‘set working set size’

– ‘priority boost’

– ‘remote proc trans’

– ‘c2 audit mode’

– ‘default trace enabled’

How to fix

Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

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

Deprecated Features, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1sys.sp_configure @configname ='allow updates'
 2
 3USE AdventureWorks2012;
 4
 5DECLARE  @result INT
 6EXEC @result = dbo.uspGetWhereUsedProductID  819, '20050225';
 7
 8EXECUTE  dbo.uspGetWhereUsedProductID  819, '20050225';
 9EXEC sp_configure 'show advanced options', 1;  
10EXECUTE sp_configure @configname ='allow updates'
11EXECUTE   sp_configure 'locks', 20000;  
12EXEC sp_configure 'open objects' , 20000
13
14EXEC sp_configure 'set working set size', 123
15
16EXEC sp_configure @configname ='priority boost', 1 ;  
17EXEC sp_configure 'remote proc trans', 1
18EXEC sp_configure @configname = 'c2 audit mode',1
19
20EXEC sys.sp_configure @configname = 'default trace enabled', 1
21
22EXEC sp_configure @configname = 'some other option'
Analysis Results
  Message Line Column
1 SA0206 : This option is still present in the sp_configure stored procedure, although its functionality is unavailable in SQL Server. The setting has no effect. 1 30
2 SA0206 : This option is still present in the sp_configure stored procedure, although its functionality is unavailable in SQL Server. The setting has no effect. 10 34
3 SA0206 : The configudation option is now automatically configured. Setting has no effect. 11 23
4 SA0206 : This option is still present in the sp_configure stored procedure, but its functionality is unavailable in Microsoft SQL Server. 12 18
5 SA0206 : This option is still present in the sp_configure stored procedure, but its functionality is unavailable in Microsoft SQL Server. 14 18
6 SA0206 : The priority boost option is deprecated. Use the Windows start /high … program.exe option instead. 16 31
7 SA0206 : The remote proc trans option is deprecated. 17 18
8 SA0206 : The c2 audit mode option is deprecated. Use common criteria compliance enabled option instead. 18 32
9 SA0206 : The default trace enabled option is deprecated and may be removed in a future version of Microsoft SQL Server. Use Extended Events instead. 20 36
See Also

Other Resources

Analysis Rules