SQL Server: Incorrect SET options on a stored procedure error

We had to put out a another fire at work when a Stored Procedure that was not modified in ages started to fail. Following is the error that was caught by the application.

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.

As specified in the error above, there was something wrong with the SET options. After a little bit of research I figured out that if the SET options are not correctly defined, this error could occur. Especially when a filtered index is added to a table, sql server requires it to be created with SET QUOTED_IDENTIFIER setting as ON. Take a look at the following blog post to recreate this error.

First attempt at fixing the error by SET ing the correct options on the stored procedure did not help the cause:

SET NUMERIC_ROUNDABORT OFF
GO

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

ALTER procedure [dbo].[STORED PROCEDURE NAME]

Solution: Apparently a new index was added to a table, was causing the issue. This index was interfering with a row insert on this table. Disabling the filtered index fixed the issue. This to me seems like a temporary solution, we still have to figure out how to make the filtered index work for this table.

SQL Server – Transparent Data Encryption – TDE

Transparent data encryption (TDE) is a new feature in SQL Server 2008 that can be used to encrypt the storage of an entire database. TDE encrypts the data using a symmetric key called the database encryption key.

Protect the database encryption key by using an asymmetric key stored in an extensible key management (EKM) module. EKM provider needs to be contacted to get a HSM device.  According to Microsoft, implementation of TDE does not have any impact on the through-put to the database in the SQL Server 2008.

Reference:

1. MSDN Online.

SQL Server 2008: Ole Automation

Ole Automation in SQL Server 2008 by default is turned off for security reasons. When OLE Automation Procedures are enabled, a call to sp_OACreate will start the OLE shared execution environment.

Executing the following script will enable Ole Automation

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO