469,331 Members | 6,245 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

Indexed View Crashes ASP.NET App

I was looking to improve the performance of an ASP.NET application by
creating the an indexed view that could be used instead of some of the
root tables.

What I didn't realize is that it would affect any future conenctions
to the root tables.

This of course crashed the application on any type of insert, update,
or delte from the root tables.

Funny thing is when I removed the indexed view -- it didn't help.
I tried flipping the offending options to the opposite of way they
were set on the database and the errors wouldn't go away.

I ended up restoring the database from before my mess up to fix it.

I was hoping that the SQL experts out there might shed some light on
my problem before I try again.

Oct 12 '07 #1
1 1472
Jason Wilson (wi*****@ausrad.com) writes:
I was looking to improve the performance of an ASP.NET application by
creating the an indexed view that could be used instead of some of the
root tables.

What I didn't realize is that it would affect any future conenctions
to the root tables.

This of course crashed the application on any type of insert, update,
or delte from the root tables.

Funny thing is when I removed the indexed view -- it didn't help.
I tried flipping the offending options to the opposite of way they
were set on the database and the errors wouldn't go away.

I ended up restoring the database from before my mess up to fix it.

I was hoping that the SQL experts out there might shed some light on
my problem before I try again.
Since you do not include which version of SQL Server you are using, you
don't include any error messages, I will have to guess.

For indexed views to work, there are a couple of SET options that must
be in the correct posittion: QUOTED_IDENTIFIFER, ANSI_NULLS, ANSI_PADDING,
CONCAT_NULL_YIELDS_NULL, ANSI_WARNING, ARITHABORT (SQL 2000 only) and
NUMERIC_ROUNDABORT. They must all be on, but the last which must be off.

The first two are saved with stored procedures, and the run-time setting
does not apply. ANSI_PADDING is saved per table column. For the other
only run-time settings apply. A common error is that procedures have
been created with a tool that by default has QUOTED_IDENTIFIER or ANSI_NULLS
off. SQLCMD and OSQL has QUOTED_IDENTIFIER off by default. Enterprise
Manager in SQL 2000 has both off by default.

So my guess is that your stored procedures have been loaded through
any of these tools, and care has not been taking to use the correct
options. Why the error persisted when you removed the indexed view, I
don't know, but my guess is that you had in fact not removed the view
although you claim that you did.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Oct 13 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Avirneni | last post: by
3 posts views Thread by teddysnips | last post: by
1 post views Thread by Mike | last post: by
1 post views Thread by Mr,Goody | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by haryvincent176 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.