Grr! Indexed Views and WIERD behavior

• BillyWilly

  SSCrazy

  Points: 2304

  More actions

  November 6, 2002 at 11:03 am

  #79044

  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 ???

• Simon Sabin

  SSCrazy Eights

  Points: 8142

  More actions

  November 6, 2002 at 12:00 pm

  #440526

  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