June 28, 2010 at 2:26 am
Just wondering if anyone else had encountered this issue...
I have an indexed view, which I created with the ANSI_NULLS setting ON - I have checked again in the script I used to create it, and it clearly says that. In fact, if it hadn't been set to ON, then I believe that the index creation would have failed.
I have a weekly maintenance plan which is now failing in its reindexing because - surprise - the ANSI_NULLS setting is now OFF on this view...
This wouldn't normally be much of a problem - I could just recreate it, but in this case it's a complete pain in the neck, because the reason I need an indexed view is so that I can replicate it onto a number of other servers. Thus in order to turn the setting back on, I have to drop and recreate all the replication.
Any ideas anyone? Is it perhaps a bug, or have I done something I shouldn't somehow?
When I recreate it, I'll have to keep a close eye on the ANSI_NULLS setting throughout...
Thanks,
Rachel
June 28, 2010 at 9:10 am
The plot thickens...
I fixed the problem in our live database. I then took a backup and restored it as a different database. When I looked at the restored copy, the ANSI_NULLS setting on the indexed view had been turned off again...
How can this happen?!
July 2, 2010 at 9:42 am
I'm at a complete loss as to what's going on. I fixed the view on Monday, and set my weekly index rebuild to try again on Monday night, and it all worked fine.
Assumed everything was OK, but I just went and had a look again today, and the ANSI_NULLS setting has been turned off again. Thus ensuring that my weekly plan will fail again this weekend unless I recreate it again.
I can't be doing this every week...
Any ideas please??
October 22, 2010 at 4:39 am
In case anyone is interested, this turned out to be a bug, and MS have made a hotfix for it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply