Enterprise Software Development

Resolution to QUOTED_IDENTIFIER Error when using SqlCacheDependancy’s

At work we are building a medium sized ASP.NET web application.

We are using ASP.NET forms authentication for the membership. I then decided to implement a custom role provider (implementing ProviderBase) based on our [Employee] application table. Implementing this was really easy! Along the way I realized that out database was going to get hammered unless I implemented some kind of caching. Fortunate for me ASP.NET has a great application cache framework.

Enter SqlCacheDependancy : Were running this on SQL 2005, so I set ENABLE_BROKER (which enables the Service Broker) on my database.

About once or twice a day we started seeing the following error in our Health Monitoring logs:

Process information:
Process ID: 3804
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE

Exception information:
Exception type: System.Data.SqlClient.SqlException
Exception message: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

Since many of the tables are created by aspnet_regsql.exe . I didn’t want to monkey around with the MS tables.

You need to set the QUOTED_IDENTIFIER option at the database level BEFORE you install the aspnet tables.

ALTER DATABASE [db] SET QUOTED_IDENTIFIER ON WITH NO_WAIT

note on the NO_WAIT option: (from msdn)

NO_WAIT

NO_WAIT checks for connections before attempting to change the database state and causes the ALTER DATABASE statement to fail if certain connections exist. When the transition is to SINGLE_USER mode, the ALTER DATABASE statement fails if any other connections exist. When the transition is to RESTRICTED_USER mode, the ALTER DATABASE statement fails if any unqualified connections exist.

 

So here is the order in which I build the database:

1. create database
2. alter database, set enable_broker
3. alter database, set QUOTED_IDENTIFIER
4. install aspnet_regsql.exe tables
5. install application tables, view, sprocs, etc . . .

That is it, I hope this helps you out in your adventures!