THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Maria Zakourdaev

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.

image

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.

CREATE DATABASE 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 NULL,

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)');

 

 

BEGIN TRY

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

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

END TRY

BEGIN CATCH

      

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

        ROLLBACK;

        RAISERROR (@error,18,1) WITH LOG

        

END CATCH

GO

 

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

 

image

 

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.

 

BEGIN TRY

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

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

END TRY

 

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 NULL,

      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

Published Tuesday, September 2, 2014 5:03 PM by Maria Zakourdaev

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

cinahcaM madA said:

Lack of a schema qualifier results in an AV?!? Definitely a bug...

September 2, 2014 9:48 AM
 

LazyDBA247 said:

it's a joy to brainstorming with u,

as usual :)

September 3, 2014 8:32 AM
 

ALZDBA said:

Great find ! Thank you for sharing it !

September 4, 2014 2:25 AM
 

Maria Zakourdaev said:

ALZDBA, Thank you for reading and for your feedback!

September 4, 2014 2:57 AM
 

Maria Zakourdaev said:

LazyDBA247/Yoni - thank you for your help :-)

September 4, 2014 3:03 AM
 

Michael Zilberstein said:

I guess, several hours of your life spared much more hours to other people :-). Anyway, if it generates memory dump - it is definitely bug. No _SQL_ error should generate dump in a normally functioning system. I think, you should open a Connect item.

September 8, 2014 7:43 AM
 

Joe Fleming said:

Nice catch Maria, and great info.  We are just l ooking into leveraging the SEQUENCE type so I have forwarded this on to our development area.  Thankfully, our standards require schema qualification of all create statements, so we should be OK as long aas everyone follows the standards.

September 8, 2014 10:01 AM
 

SQLDBA said:

Great Catch...!

September 8, 2014 2:11 PM
 

Richard said:

Nice catch, but the EXEC code with string concatenation isn't really a good idea. Remind me to create a login in your database called "Robert');DROP TABLE TestDB.dbo.TestAuditTable;--"! :)

September 9, 2014 9:52 AM
 

Maria Zakourdaev said:

Naturally, we wouldn't have implemented the script with the string concatenation :-) that was just a desperate try to avoid the Exception.

September 9, 2014 10:09 AM

Leave a Comment

(required) 
(required) 
Submit

About Maria Zakourdaev

The shortest word in the English language that contains the letters: abcdef is… feedback! Let me know if I have touched something that is alive in the cosmos.
Privacy Statement