OLE DB ARITHABORT error with indexed computed columns

[jasonleske]

Instead of using Full-Text indices, which I don't like to manage, we've tried to use seperate tables that contain recordID, the word, a count of the word in the parent field and computed column which is the CHECKSUM() of the word column. I indexed the checksum column with a clustered index.

Works great in Query Analyser. But when the ASP page calls it, I get this message:

Microsoft OLE DB Provider for SQL Server (0x80040E14)
INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'.

Same for updates and deletes. The question is how should these SET settings be done? Any ideas would be greatly welcomed.

Thanks
Jason

[wileyjack]

Make sure:
ANSI_PADDING,
ANSI_NULLS,
ANSI_WARNINGS,
ARITHABORT,
CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
are all SET ON
AND
NUMERIC_ROUNDABORT is set to OFF

Query Analyzer (Tools, Options, Connection Properties) is probably turning them on/off for you that's why it's working within QA. In your stored procedure just add
SET [option] [ON][OFF] (ex. SET ARITHABORT ON )
before your INSERT, UPDATE or DELETE statement.

Good Luck!

WiLeYjAcK

[jasonleske]

Thank you wileyjack,

Adding the SET options to the stored proc doesn't change the result.
I've dropped and recompiled the proc setting the options in the same execute (different batch) as creating the proc. And I have inserted the options into the proc, still to no avail.

It seems I have to change the SET options for the API (ADO is this case) doing the connection as these settings override what is in the stored proc. The problem is I haven't found anywhere how you successfully do that. Any other ideas?

[jasonleske]

Got it to work. Firstly, removed the transaction from my insert proc (this was last thing I did to make it work), in the vb ADO code, as soon as the connection object is opened I placed this line

oConnection.Execute = "SET ARITHABORT ON"

No sure why I had to remove the transaction from inside the insert proc, but doing this and adding the above vb code solves the ARITHABORT problem. There is not much on the web for this problem, so I hope this solution helps someone.