Choosing SEQUENCE instead of IDENTITY? Watch your step.

On the nice sunny day getting this error message can be really frustrating. In fact, it is frustrating on any day. Especially if you are doing something really simple.

Most of you at this moment yell at your monitor “DDL Trigger!!!!” . Yes. I have a simple DDL trigger for auditing purposes.

If you use the script below for the auditing table, any DDL statement will get pretty red notification to discard the results.

CREATEDATABASE TestDB;

USE TestDB;

CREATE SEQUENCE GetNextNumber AS int START WITH 1 INCREMENT BY 1;

CREATE TABLE dbo.TestAuditTable(

       ID            int NOT NULL DEFAULT NEXT VALUE FOR GetNextNumber PRIMARY KEY,

       ChangeDate    datetime,

       ObjectName    sysname,

       LoginName     sysname);

You see, the table is pretty basic, I even use SEQUENCE instead of IDENTITY. Which is, in fact, the main problem.

CREATE TRIGGER TRG_DDLServer

ON  ALL server

For   

DDL_EVENTS

AS

DECLARE @error varchar(256);

DECLARE @LoginName nvarchar(max);

DECLARE @ObjectName nvarchar(max);

SELECT @ObjectName =EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'),

        @LoginName =EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)');

BEGINTRY

              INSERTINTO TestDB.dbo.TestAuditTable(ChangeDate,ObjectName,LoginName)

              VALUES(getdate(),@ObjectName,@LoginName);

ENDTRY

BEGINCATCH

        SET @error ='Failed execute DDL trigger:'+ERROR_MESSAGE()

        ROLLBACK;

        RAISERROR (@error,18,1)WITHLOG

 ENDCATCH

GO

In addition to the unclear error message, you get a nice memory dump in Error Log logged each time some DDL comes through.

Searching for the problem, I have spent few hours of my life giving various types of permissions on the Sequence object to the user that I am working with and to the guest user, adding WITH EXECUTE to the Trigger and many other things.  Regular inserts to the Audit table worked. I even had created an INSERT TRIGGER and INSTEAD OF INSERT TRIGGER and any of this worked perfectly. Apart from the insert from the DDL trigger.

My stubborn friend Yoni Nakache after a long fight discovered that if you change execution context by using EXEC or sp_executesql over the insert statement inside DDL trigger, DDL will not produce the error anymore.

BEGINTRY

EXEC ('INSERT INTO TestDB.dbo.TestAuditTable (ChangeDate,ObjectName,LoginName)

        VALUES(getdate(),'''+@ObjectName+''','''+@LoginName+''');')

ENDTRY

More brainstorming around the subject has revealed the truth.

Server side DDL triggers do not have a default schema. It is a must to add Schema name to the Sequence call inside the Table Default definition. I admit, I frequently forget adding schema name if I am using the dbo schema.

CREATE TABLE dbo.TestAuditTable(

       ID            int NOT NULL DEFAULT NEXT VALUE FOR dbo.GetNextNumber PRIMARY KEY,

       ChangeDate    datetime,

       ObjectName    sysname,
       LoginName     sysname);

The fact that SQL Server does not produce the correct error message looks like a bug. QA plan for Sequence feature did not contain DDL triggers. Neither on the SQL Server 2012 not on SQL Server 2014.

May all your errors turn into experiences that will cause you to evolve.

Yours,

Maria