I am creating a filtered index and I get the following error:
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'
here is how I have defined it:
SET QUOTED_IDENTIFIER ON
/****** Object: Index [IDX_Memb_LastLoginQuarterly] Script Date: 11/10/2009 11:52:11 ******/
DROP INDEX [IDX_Memb_LastLoginQuarterly] ON [dbo].[aspnet_Membership] WITH ( ONLINE = OFF )
CREATE NONCLUSTERED INDEX [IDX_Memb_LastLoginQuarterly] ON [dbo].[aspnet_Membership]
INCLUDE ( [UserId])
WHERE ([LastLoginDate]>='2009-09-01' AND [LastLoginDate]<='2009-12-31')
WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
aspnet_Membership table come with membership provider. For details see the link below:
Our website fails, when a user tries to login.
Any thoughts? This table is growing rapidly and I wanted to take advantage of filtered indexed.
I am able to create the index and after the creation all login fail.
Another fact, I think membership provider created stored procedures are using 'QUOTED_IDENTIFIER' OFF i.e. SET QUOTED_IDENTIFIER OFF.
We have other indexes and we don't have problem with updates and inserts. This problem only occurs when I add a filtered index.
Thanks in advance for your time and input.