Indexed Views

I am looking for a little insight. I am using an SQL Server database created by a third party vendor. There are certain columns in a given table that I query for quite often. To speed things up, I created an indexed view.

Now I can no longer insert into the base table. Attempting an insert causes a SQL error stating that the system properties ARITHABORT and NUMERIC_ROUNDABORT are incorrect. If I remove the index from my view, the inserts work just fine.

Can somebody provide some insight as to why this happens and how I might be able to correct it (keep in mind that the DB was setup by a third party, so I cannot change too much of the underlying setup without possibly compromising their functionality).SEE BOL
indexed views --> SET Options That Affect Results

These six SET options must be set to ON:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
The NUMERIC_ROUNDABORT option must be set to OFF.

all INSERT, UPDATE, and DELETE operations must have the same setting of CONCAT_NULL_YIELDS_NULL ON as the connection that created the index.
( I think this also applies for the other settings)

The above applies to indexed views or computed columns; do you have a computed column in the table or the view? If so, I would try removing it from the view and see if the error goes away.

Tim S|||always be careful screwing with the backend of someone elses closed software. I would copy the database to somewhere you can query against it without fear of messing things up.|||I have no computed columns. If I am understanding this right, any session issuing INSERT, UPDATE, or DELETE queries must have same session properties as the session that created the index. Does this mean that if the base table (and it's indexes) were created by one session, but the indexed view was created with a different session (with different session properties), there will always be an error when trying to query the base table?

For example, in my case it seems that the base table was created with the session property "ARITHABORT" turned off. Indexed views, however, require ARITHABORT to be on. Does this mean that I cannot create an indexed view on that base table since the view's index will always require ARITHABORT to be on and the base table index will always require it to be off?

SEE BOL
all INSERT, UPDATE, and DELETE operations must have the same setting of CONCAT_NULL_YIELDS_NULL ON as the connection that created the index.
( I think this also applies for the other settings)

The above applies to indexed views or computed columns; do you have a computed column in the table or the view? If so, I would try removing it from the view and see if the error goes away.

Tim S|||This isn't a case of backend software per se. It is a mutually shared database, but it was configured by them. They put their data in, we put ours in, and then we generate reports.

Either way, yes, we always backup stuff before messing with it.

always be careful screwing with the backend of someone elses closed software. I would copy the database to somewhere you can query against it without fear of messing things up.|||Yes, but the point being, you'd be way better served to copy a nightly dump and create another database for reporting...

preferably on another box, or at least a separate instance...|||That would be a great solution if the data was not needed in real time.

Yes, but the point being, you'd be way better served to copy a nightly dump and create another database for reporting...

preferably on another box, or at least a separate instance...|||replication?