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.
About once or twice a day we started seeing the following error in our Health Monitoring logs:
Process ID: 3804
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE
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 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:
- create database
- alter database, set enable_broker
- alter database, set QUOTED_IDENTIFIER
- install aspnet_regsql.exe tables
- install application tables, view, sprocs, etc . . .
That is it, I hope this helps you out in your adventures!
Write a comment
You need to login to post comments!