I'm experiencing a problem trying to insert into a table with a computed column with an index on it.

I'm not trying to set the value of the column in question, I'm just trying to insert into the same table.

I searched the forum and found some other cases of this, but it doesn't look like they were resolved as the issue was unable to be reproduced in most cases, or the original poster never responded.

I am able to reproduce this consistently on a SQL Server 2000 database.

If I do nothing, the SET ARITHABORT ON is not present in the statement it tries to run.

If I call "DataAccessAdapter.SetArithAbortFlag(true)" before the save, it DOES change the SQL to include SET ARITHABORT ON but I still get the error anyway, and I am at a loss to explain this.

If I subclass my own DataAccessAdapter and force it to execute "SET ARITHABORT ON" on the same connection as a seperate call before I call the save, the insert does succeed, but I don't think this is an ideal solution.

I have attached a zip file containing my generated DAL, 1 unit test, and a backup of the database in question so you can see this in action.

Details: Latest version of 2.5 final, downloaded today. SQL Server 2000 Adapter Scenario Nothing customized or complicated in any way.

I've left out the stack trace and emitted SQ in this case because they seem very redundant with the information above, but I'll be happy to provide them if they'll be of value.

Odd.

What we do is simply wrap the query with aborton/off statements: SET ARITHABORT ON;<query>;SET ARITHABORT OFF;

This is required for inserts in tables which which are part of indexed views (and apparently also for tables which have indexes on computed columns).

The framework does have a mechanism to execute scalar queries before and after the real command, which I could utilize to execute these statements, but it's a bit of a pain (also because these statements don't return anything)

The issue is that the flag is a static flag. So executing scalar queries in front of every insert is IMHO not the ideal situation as well. With the wrapping of the query, it's not really a problem, the query isn't really affected.

What's odd to me is that SET ARITHABORT OFF; as a separate statement in the query has no effect, as it's actually the same as sending another command over the wire.

Just went back and double checked because I was pretty tired when I made my initial post and I wasn't as sure this morning, but I can confirm that this does not work on SQL 2000:

exec sp_executesql N'SET ARITHABORT ON;insert into address (Id, ZipCode) values (newid(), ''909090909'');SET ARITHABORT OFF'

but that this does work:

SET ARITHABORT ON exec sp_executesql N'insert into address (Id, ZipCode) values (newid(), ''909090909'')' SET ARITHABORT OFF

I understand why you don't want to add an option that effectively has to issue two extra round-trip commands per intentional edit based on the flag.

Is there some way you could let us set a flag that would just have it turned on (and left on) at a connection level? I think having it on has been the default in my query analyzer for years and I don't think it's caused me any problems yet.

Hmm.

Well, you can set it on the server level. Enterprise manager -> right click server -> properties -> Connections tab -> Arithmetic abort -> check the checkbox.

But that's of course for the complete server.

If you want it at the connection level, you could override in a derived class of DataAccessAdapter the method OpenConnection().

You then call first the base' method, and then call GetActiveConnection() to obtain the active connection and to create a command which executes Set ARITHABORT ON and execute it.

What's weird is that no matter what you do: if you execute a command, it's always packed inside exec sp_executesql N'... '

so also the ARITHABORT ON.

I've went one of the routes you suggested by subclassing the adapter and issuing the command myself, but I'm running into an issue when the adapter has started a transaction.

What's proper way for me to get a command / associate it with the current connections transaction? I see an IsInTransaction, but I can't find a way to grab ahold of it.

joliver wrote:

I've went one of the routes you suggested by subclassing the adapter and issuing the command myself, but I'm running into an issue when the adapter has started a transaction.

What's proper way for me to get a command / associate it with the current connections transaction? I see an IsInTransaction, but I can't find a way to grab ahold of it.

You need to set the command's transaction I suppose?

If you create your SET ARITHABORT ON query as an ActionQuery instance (which is a class in the framework and which exposes an IDbCommand), you can simply call: base.WireTransaction(query) to wire it to the active transaction in the adapter

So: ActionQuery q = new ActionQuery(cmd, con); base.WireTransaction(q); q.Execute();

Thanks, that worked. I'm not thrilled with the resolution, but inserts and updates to the table in question happen so infrequently for this application that I'm content leaving it like this for now.