"DROP INDEX failed because the following SET options have incorrect settings: 'ARITHABORT

Hi,
I've got a big import routine that brings a lot of data in raw tables into
another database - the routine is about 2000 lines long. I have a DTS
package that first brings the data in from CSV files and other sources, then
it runs this sproc to bring the data into the other database, then finally
it truncates the logs, and shrinks the databases. In the sproc, I EXEC a
sproc in the target database which drops all indexes on the target database,
start a transaction, bring all of the data in, then EXEC another sproc on
the target database to recreate the indexes again. Like I said, it's a
monster! :)
This sproc worked flawlessly for about six months, but now when I run the
DTS package it fails on the sproc step with the error mentioned in the
Subject line. The table on which this is failing has a clustered unique
index and 8 other standard indexes, but there are no indexes on calculated
columns or indexed views or anything like that. I am able to run the sproc
on the data from Query Analyzer with no errors, and I have tried DROPping
INDEXes with ARITHABORT on and ARITHABORT off, with no complaints.
Can anybody help out with this?
Thanks, JimDo you have any hypothetical indexes created by the ITW ?
select * from sysindexes where name like 'hind_%'
If you have no "real" indexes on computed columns or views its woeth
dropping any hypothetical ones and seeing it that helps
It may be worth explicitly setting the correct set options in your sproc
anyway to be on the safe side
set ANSI_PADDING,ANSI_WARNINGS,
CONCAT_NULL_YIELDS_NULL,ARITHABORT,
QUOTED_IDENTIFIER,ANSI_NULLS on
set NUMERIC_ROUNDABORT off
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jim" <jim@.x.com> wrote in message
news:erLkiH%23JEHA.3276@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've got a big import routine that brings a lot of data in raw tables into
> another database - the routine is about 2000 lines long. I have a DTS
> package that first brings the data in from CSV files and other sources,
then
> it runs this sproc to bring the data into the other database, then finally
> it truncates the logs, and shrinks the databases. In the sproc, I EXEC a
> sproc in the target database which drops all indexes on the target
database,
> start a transaction, bring all of the data in, then EXEC another sproc on
> the target database to recreate the indexes again. Like I said, it's a
> monster! :)
> This sproc worked flawlessly for about six months, but now when I run the
> DTS package it fails on the sproc step with the error mentioned in the
> Subject line. The table on which this is failing has a clustered unique
> index and 8 other standard indexes, but there are no indexes on calculated
> columns or indexed views or anything like that. I am able to run the
sproc
> on the data from Query Analyzer with no errors, and I have tried DROPping
> INDEXes with ARITHABORT on and ARITHABORT off, with no complaints.
> Can anybody help out with this?
> Thanks, Jim
>