Error: 1934 due to DDL trigger trgLogDDLEvent

Question

Error: 1934 due to DDL trigger trgLogDDLEvent

vijred

Say Hey Kid

Points: 681
More actions
January 27, 2014 at 4:05 am

#281639

Hi,
When I try to add/remove user login to specific database from SSMS UI, I get Error: 1934 error but works fine if I execute the query(add user command) from SQL Query window.
Disabling <trgLogDDLEvent> trigger on the database helps to address the problem as a workaround. Can someone help me to find the root cause?
FYI:
Error Message:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for User 'DOMAINNAME\UESRNAME'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.5292.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
CONDITIONAL 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. (Microsoft SQL Server, Error: 1934)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5069&EvtSrc=MSSQLServer&EvtID=1934&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Trigger:
USE [DATABASE_NAME]
GO
/****** Object: DdlTrigger [trgLogDDLEvent] Script Date: 01/27/2014 05:56:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [trgLogDDLEvent] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
<> 'CREATE_STATISTICS'
INSERT INTO admindb.dbo.DDLChangeLog
(
EventType,
ObjectName,
ObjectType,
tsql,
DataBaseName,
HostName
)
VALUES (
@data.value('(/EVENT_INSTANCE/EventType)[1]',
'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]',
'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]',
'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'nvarchar(max)'),
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]',
'nvarchar(100)'),
HOST_NAME()
) ;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trgLogDDLEvent] ON DATABASE
-Vijred (http://vijredblog.wordpress.com)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

durai nagarajan SSCoach Points: 15778 More actions · Answer 1

I hope 'ARITHABORT' comes in when there is two or more server involvement , are you sure the DDL inserts the data in the same server?

Regards
Durai Nagarajan

vijred Say Hey Kid Points: 681 More actions · Answer 2

Yes, it is on the same server but on a different database.

Thanks,

Vijay

-Vijred (http://vijredblog.wordpress.com)

durai nagarajan SSCoach Points: 15778 More actions · Answer 3

then try running statements with

set arithabort on and set arithabort off

Regards
Durai Nagarajan

vijred Say Hey Kid Points: 681 More actions · Answer 4

Thank you Durai for your time and help!

Yes, you are correct ARITHABORT was ON for my SQL Query window but was disabled on SSMS session.

FYI:

Used following query to verify the settings:

SELECT SESSIONPROPERTY('ARITHABORT') AS ArithAbortSetting

select @@OPTIONS & 64

-Vijred (http://vijredblog.wordpress.com)

durai nagarajan SSCoach Points: 15778 More actions · Answer 5

durai nagarajan

SSCoach

Points: 15778

January 28, 2014 at 4:48 am

#1683864

Welcome happy to help

Regards
Durai Nagarajan