arithabort with drop table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

arithabort with drop table

Author

Topic

jaynichols
Starting Member

18 Posts

Posted - 2008-10-07 : 15:09:38

I am importing data with SSIS. My first operation is to execute a t-sql drop table. The second operation is to execute a t-sql to create the table. The third operation is to copy the data from a .csv to the previously created sql table. When attempting to drop the table this is the error: [Execute SQL Task] Error: Executing the query "drop table [SDB].[dbo].[POHDR]" failed with the following error: "SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Dirt biking forever!

jaynichols
Starting Member

18 Posts

Posted - 2008-10-07 : 15:44:31

I think I found the problem. I reciently started logging DDL db events with the following code. Once I remove this trigger, the above problems disappear. However the same code is used on all the other db's and no problems. The only other item of possible interest is the db owner is no longer on the network. We use AD.

/****** Object: DdlTrigger [SDB] Script Date: 10/07/2008 14:26:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [SDB] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
DECLARE @cmd NVARCHAR(1000)
DECLARE @posttime NVARCHAR(24)
DECLARE @spid NVARCHAR(6)
DECLARE @loginname NVARCHAR(100)
DECLARE @hostname NVARCHAR(100)
SET @data = EVENTDATA()
SET @cmd = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(1000)')
SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))
SET @posttime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(24)')
SET @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(6)')
SET @loginname = @data.value('(/EVENT_INSTANCE/LoginName)[1]',
'NVARCHAR(100)')
SET @hostname = HOST_NAME()
INSERT INTO AdminAudit.dbo.SDB_AuditLog(Command, PostTime,HostName,LoginName)
VALUES(@cmd, @posttime, @hostname, @loginname)

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [SDB] ON DATABASE

Dirt biking forever!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources