October 28, 2010 at 10:34 am
NOTE: I thought I had inquired about this back in 2009 but a search of the site did not return any hits so if I did ask and just could not locate the post please let me know a link or article ID I can use to locate that item
The problem is how does one use the power of DDL Triggers when the SET OPTIONS in use conflict with those required by SQL Server to use DDL's? I'm trying to setup a DDL for DDL_DATABASE_LEVEL_EVENTS to catch any and all DDL actions but the darn thing fails because the primary management software we use sets 3 of the DB SET OPTIONS to the opposite of what is apparently required to use DDLS.
Even if I set the 3 options ON at the DB LEVEL, the application turns these off when it makes the connection and if I understand correctly that negates what I have set these DB Options to. The 3 SET OPTIONS are
ARITHABORT
ANSI_WARNINGS
CONCAT_NULL_YIELDS_NULL
The app sets all 3 of these to OFF when it makes a connection to teh DB. From my testing in Query Analyzer it looks like all 3 of these must be ON for DDL Triggers to work and not just set to ON at the DB Options level but in any connection as well.
Any suggestions or thoughts on how to work this kind of scenario? I can't just tell the app to not turn these off because the software vendor has said in the past (we've raised issues with some of these settings before, years ago) they must have these set as they are for their app to work properly.
Kindest Regards,
Just say No to Facebook!October 28, 2010 at 11:18 am
Those options have to be ON when you create the trigger, not when it's called.
I just tested that. I have a DDL trigger as:
USE [CVB_CW]
GO
/****** Object: DdlTrigger [DDLLog] Script Date: 10/28/2010 13:17:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [DDLLog]
on database
for ddl_database_level_events
as
set nocount on;
if object_id(N'DBA.dbo.DDLLog','U') is not null
insert into DBA.dbo.DDLLog (LogEntry, SourceDB)
select eventdata(), db_name();
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [DDLLog] ON DATABASE
So I ran this:
SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
CREATE TABLE dbo.DropMe (
ID INT);
GO
DROP TABLE dbo.DropMe;
The create and drop events were logged, even with the connection settings changed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2010 at 2:08 pm
Thanks GSquared for replying; your answer has made me realize a key piece of the puzzle was left out of my initial post and thats because I did not relaize it was the real reaosn why my DDL Trigger was failing.
The diff between your example and what I'm doing is that my INSERT is poulling from the EVENTDATA() function like below. I guess the SET OPTIONS pertains tehn to the use of EVENTDATA() and not the use of DDL Triggers.
If this is true do you know how to get the same data that EVENTDATA() returns?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [DDLLog]
on database
for ddl_database_level_events
as
set nocount on;
DECLARE @data XML
DECLARE @SPID NVARCHAR(6)
DECLARE @LoginName NVARCHAR(100)
DECLARE @HostName NVARCHAR(100)
DECLARE @EventType VARCHAR(255)
DECLARE @ObjectType VARCHAR(255)
DECLARE @ObjectName VARCHAR(255)
DECLARE @EventText VARCHAR(Max)
DECLARE @sMsg VARCHAR(Max)
DECLARE @UserName VARCHAR(50)
DECLARE @PostTime NVARCHAR(24)
DECLARE @CurrentUser VARCHAR(50)
SET @data = EventData()
IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') <> 'CREATE_STATISTICS'
BEGIN
SELECT @HostName = Host_Name()
SELECT @UserName = Original_Login()
SELECT @CurrentUser = User_Name()
SELECT @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
SELECT @SPID = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(6)')
SELECT @ObjectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)')
SELECT @ObjectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)')
SELECT @PostTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar(24)')
SELECT @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)')
SELECT @EventText = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(Max)')
SELECT @Eventtext = LTrim(RTrim(Replace(@EventText,'','')))
INSERT INTO DBA.dbo.DDLCHANGELOG (InsertionDate, HostName, SPID, CurrentUser, LoginName, UserName, EventType, ObjectType, ObjectName, tSQL)
VALUES (@PostTime, @HostName, @SPID, @CurrentUser, @LoginName, @UserName, @EventType, @ObjectType, @ObjectName, @EventText)
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [DDLLog] ON DATABASE
Kindest Regards,
Just say No to Facebook!October 28, 2010 at 2:12 pm
Actually, if you look, you'll notice that my trigger uses the EventData() function. I just log the whole XML blob, instead of parsing it in the trigger.
Part of my philosophy on triggers is they should commit as fast as possible and not hold up the calling process any longer than needed. Parsing XML in a trigger seems like a good way to waste time, to me. So I don't bother. Far more efficient to parse it at query-time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2010 at 2:42 pm
GSquared (10/28/2010)
Actually, if you look, you'll notice that my trigger uses the EventData() function. I just log the whole XML blob, instead of parsing it in the trigger.Part of my philosophy on triggers is they should commit as fast as possible and not hold up the calling process any longer than needed. Parsing XML in a trigger seems like a good way to waste time, to me. So I don't bother. Far more efficient to parse it at query-time.
Yeah on a second and much closer look I see that now. Now to revist this and find out why its not wokring for me.
BTW - I hijacked the code from a BOL sample about a year ago and am just now getting back to trying to figure out whjy it didn't work then. I never considered dumpng the whole of what EVENTDATA() retruns and then using T-SQL to slice & dice it. I don't suppose you have an example (dont' waste time putting one together) of hwo you'd slice up the dat from EVENTDAT() after its in your table?
Kindest Regards,
Just say No to Facebook!October 28, 2010 at 3:19 pm
Thanks for posting all the details and source as it made de-bugging and finding a solution much easier.
I found that adding to the trigger "SET CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS ON;" immediately after "set nocount on;", does allow the trigger to function without error.
ARITHABORT setting did not seem to have affect.
SQL = Scarcely Qualifies as a Language
October 29, 2010 at 6:53 am
Here's an example of querying the XML column if you just log the whole function result like I do:
SELECT
LogEntry.value('(/EVENT_INSTANCE/EventType/text())[1]', 'varchar(100)') AS EventType,
LogEntry.value('(/EVENT_INSTANCE/LoginName/text())[1]', 'varchar(100)') AS LoginName,
LogEntry.value('(/EVENT_INSTANCE/DatabaseName/text())[1]', 'varchar(100)') AS DatabaseName,
LogEntry.value('(/EVENT_INSTANCE/SchemaName/text())[1]', 'varchar(100)') AS SchemaName,
LogEntry.value('(/EVENT_INSTANCE/ObjectName/text())[1]', 'varchar(100)') AS ObjectName,
SourceDB
FROM
dbo.DDLLog
WHERE
LogEntry.exist('/EVENT_INSTANCE/LoginName[text()[1] = "sa"]') = 1 ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 29, 2010 at 10:33 am
GSquared,
Would you mind posting the DDL to create the DDLLog tabel you're using in your examples? I still can;t get mine to work even though yours does but I think I may have found the problem and seein the DDL to create the DDLLog table you are using should help verify this.
Thanks
Kindest Regards,
Just say No to Facebook!October 29, 2010 at 11:00 am
USE [DBA]
GO
/****** Object: Table [dbo].[DDLLog] Script Date: 10/29/2010 13:00:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DDLLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LogDate] [datetime] NOT NULL DEFAULT (getdate()),
[LogEntry] [xml] NOT NULL,
[SourceDB] [sysname] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 29, 2010 at 3:01 pm
GSquared,
So after hours of fiddling with this and comparing your sample to mine I have found the problem or reason why yorus works and mine doesn't. The bottom line is that you can't reference or use an XML Variable inside the trigger.
Your sample dumps the results of EVENTDATA() into a Table that has an XML column that you then can slice & dice thru standard T-SQL. Mine dumos the contents of EVENTDTA() into an XML Variable (as shown in the BOL Sample which is why I did it this way) and then assign parts of that XML Variable to other non-xml variables (mostly VARCHARs) and then I use those as the values in my INSERT INTO.
So you can't do this within a DDL Trigger:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER DB_DDL_LogDDLEvents_TrgWD ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON;
DECLARE @data XML
DECLARE @EventType VARCHAR(255)
DECLARE @EventText VARCHAR(Max)
SET @data = EventData()
BEGIN
SELECT @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
SELECT @EventText = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(Max)')
SELECT @Eventtext = LTrim(RTrim(Replace(@EventText,'','')))
INSERT INTO dbo.DDL_CHANGELOG( EventType, tSQL)
VALUES ( @EventType, @EventText )
END
GO
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
But YOU can do this in a DDL Trigger:
IF Exists (SELECT * FROM SYS.OBJECTS WHERE Object_ID = Object_ID(N'DB_DDL_LogDDLEvents_TrgWD') AND Type in (N'U'))
DROP TRIGGER DB_DDL_LogDDLEvents_TrgWD ON DATABASE
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER DB_DDL_LogDDLEvents_TrgWD ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON;
BEGIN
insert into DBA.dbo.DDLLog (LogEntry, SourceDB)
select eventdata(), db_name();
END
GO
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
And it appears that the SET OPTIONS are the factor for the one that doesn;t work. If the 3 SET OPTIONS are ON then the first one that uses an XML Variable does work.
Go figure. While this may sound totally logcial to a SQL-XML Guru to me an XML novice, this was an incredible waset of valuable time. By that I mean I wasted far more time then I shoudl have had to. I appreciate GSquareds help b/c I may never have figured this out otherwise.
If anyone has any thoughts on the matter I'd love to hear them. Currently I've got no chocie but to move to using 2 tables instead of one since the EVENTDATA() results have to be dmped to a staging table first.
Thanks again.
Kindest Regards,
Just say No to Facebook!November 2, 2010 at 8:00 am
I have to ask: Why do you need a staging table and a final table?
Why not do like I did, and just dump into a table with an XML column?
If you need it "pre-parsed", throw an XML index on it, and create a view that queries the XML into the format you need. The XML index will give it the performance you need, and the view will "pre-parse" it into rows and columns for you, without the need for a separate table.
If you prefer a staging solution, it should be easy enough to have a job parse the XML into one for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply