Systems Engineering and RDBMS

Archive for October 9th, 2007

EVENTDATA() function in SQL Server 2005

Posted by decipherinfosys on October 9, 2007

Normally the EVENTDATA() function is called when an event notification is fired. It returns information about the server or database events. In Transact-SQL, it has limited but very important usage. As far as Transact-SQL goes, it can be used only in DDL triggers or logon triggers which was introduced in SP2 of SQL Server 2005.

Two things to keep in mind when using EVENTDATA() function are:

• It will return values only when it is directly referenced in DDL or Logon trigger. If it is used outside of the trigger scope, it returns null. This holds true for any routine called from trigger too.
• When transaction that calls EVENTDATA() gets committed or rolled back, data returned by EVENTDATA() becomes invalid.

EVENTDATA() returns an XML value. Most of the time when called from DDL trigger or Logon trigger, XML schema returned by function contains following element values along with other element values which gets changed based on the event type.

• SPID
• EventType
• Time of the event
• ServerName

Let us create a simple DDL trigger at database level which prevents creation of a stored procedure. Connect to the appropriate database using SQL Server management studio.

CREATE TRIGGER TEST_TRIG
ON DATABASE
FOR CREATE_PROCEDURE
AS

SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/EventType)[1]‘,’nvarchar(100)’) as Type,
EVENTDATA().value(‘(/EVENT_INSTANCE/SPID)[1]‘,’nvarchar(100)’) as SPID,
EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]‘,’nvarchar(100)’) as Object_Name,
EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectType)[1]‘,’nvarchar(100)’) as Object_Type

RAISERROR (‘User cannot create new procedure in the database.’, 16, 1)
ROLLBACK

GO

Let us try to create a stored procedure now. Issue following command and see the error and information returned by EVENTDATA() function.

CREATE PROCEDURE TEST_PROC
AS
BEGIN
SELECT GETDATE()
END
GO

Here is the error returned by DDL trigger. It also returns information from EVENTDATA() function.

Type SPID Object_Name Object_Type
——————- —— ————- ———–
CREATE_PROCEDURE 52 TEST_PROC PROCEDURE

(1 row(s) affected)

Msg 50000, Level 16, State 1, Procedure TEST_TRIG, Line 12
User cannot create new procedure in the database.
Msg 3609, Level 16, State 2, Procedure TEST_PROC, Line 4
The transaction ended in the trigger. The batch has been aborted.

Now drop and re-create the trigger. This time we will call EVENTDATA() function, once we rollback the transaction. Change the trigger as shown below. Look at the syntax of dropping the DDL trigger. We need to specify whther it is on the database or on the server. If you omit the ‘ON’ clause, you will get the error that either trigger does not exist or you do not have any permission.

DROP TRIGGER TEST_TRIG ON DATABASE
GO

CREATE TRIGGER TEST_TRIG
ON DATABASE
FOR CREATE_PROCEDURE
AS

RAISERROR (‘User cannot create new procedure in the database.’, 16, 1)
ROLLBACK

SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/EventType)[1]‘,’nvarchar(100)’) as Type,
EVENTDATA().value(‘(/EVENT_INSTANCE/SPID)[1]‘,’nvarchar(100)’) as SPID,
EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]‘,’nvarchar(100)’) as Object_Name,
EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectType)[1]‘,’nvarchar(100)’) as Object_Type

GO

Now try to create stored procedure again by issuing statement shown above. Following is the error text.

Msg 50000, Level 16, State 1, Procedure TEST_TRIG, Line 7
User cannot create new procedure in the database.

Type SPID Object_Name Object_Type
——————- —— ————- ———–
NULL NULL NULL NULL

(1 row(s) affected)

Msg 3609, Level 16, State 2, Procedure TEST_PROC, Line 4
The transaction ended in the trigger. The batch has been aborted.

Results shown above clearly indicates that it is important to use EVENTDATA() function before committing or rolling back the transaction. Once the transaction is rolled back,  the values returned by the EVENTDATA() function became invalid and hence it returns null.

Posted in SQL Server | 3 Comments »

Daylight Savings Time Change in November in USA

Posted by decipherinfosys on October 9, 2007

In the USA, the daylight savings time will end on 4th of November. Chances are that you must have already applied the critical patches already when the DST changes were made in March this year. We had covered those in our blog posts at that time – exchange and DST, Oracle, SQL Server and DB2 LUW, Microsoft products effected by DST and Windows Mobile Devices.

So, hopefully all your critical systems already have all these set up properly.  For Microsoft based systems, if you are not sure how it effects your systems, take a look at the MSFT site:

http://support.microsoft.com/gp/cp_dst

Posted in Technology | 2 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers