UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

Last Post 10 Nov 2009 09:17 AM by najmh. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
najmh
New Member
New Member

--
10 Nov 2009 08:39 AM
Hi All,
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
GO
USE [MyDB]
GO

/****** 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 )
GO

CREATE NONCLUSTERED INDEX [IDX_Memb_LastLoginQuarterly] ON [dbo].[aspnet_Membership]
(
[LastLoginDate] ASC
)
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]
GO
aspnet_Membership table come with membership provider. For details see the link below:
http://msdn.microsoft.com/en-us/lib...78949.aspx

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.

Regards,
Najm
najmh
New Member
New Member

--
10 Nov 2009 09:17 AM
I have varified that this error is thrown due to fact that proc is built with SET QUOTED_IDENTIFIER OFF
Just wanted to share that just in case someone else faced similar situation
You are not authorized to post a reply.

Acceptable Use Policy