November 6, 2002 at 11:03 am
Hello...
I'm getting VERY odd behavior ever since I added an Indexed view to my system.
If I take the exact code from within a Stored Procedure and run it in Query Analyzer - all works fine.
Now, I take the code and put it into a Stored Procedure (no paratmers - nothing new except the line CREATE PROCEDURE X AS) and I get this error when running the SP:
Server: Msg 1934, Level 16, State 1, Procedure X, Line 11
UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS.'.
Now, if I remove the Index from my view - the SP works fine AND the direct Query Analyzer work fine.
This is driving me INSANE !!!!
I don't think Microsoft did a very good job with Indexed Views. I cannot find good documentation and LOTS of quirks.
Anyone seen this behavior ???
November 6, 2002 at 12:00 pm
This is something I don't believe may people take notice off. If you look at the scripts generated by EM they contain alot of SET statements.
You need to ensure your objects are created with correct SET statements. More of the complex functionality in 2000 requires you to control these.
So recreate your view and SP but make sure you have issues SET ANSI_NULLS ON or OFF (which ever you want)
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply