Hi :
I have created the followign ddl trigger to log all the ddl activities. It is workign fine for all the sucessfully created objects.
The problem is, how do i log errors also. Like if i am trying to create a table with same name twice then the trigger should log a stmt saying that the table already exists in the database. or while creating synonyms twice with same name, etc
I want to log all the failed DDL stmts also.
Any solution will be of good help to me.
CREATE TRIGGER [DDLLogging] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;
DECLARE @.data XML;
DECLARE @.schema sysname;
DECLARE @.object sysname;
DECLARE @.eventType sysname;
DECLARE @.logstmt varchar(500);
DECLARE @.query VARCHAR(255)
DECLARE @.file VARCHAR(255)
SET @.data = EVENTDATA();
SET @.eventType = @.data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @.schema = @.data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @.object = @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
SET @.file = 'c:\xzy\'+REPLACE(CONVERT(char(10),GETDATE(),111),'/','_')+'.log'
IF @.object IS NOT NULL
BEGIN
SET @.logstmt = CONVERT(varchar(20),GETDATE(),9) + ', ' + @.eventType + ' ' + @.object + ' successful';
SET @.query = RTRIM('echo ' + COALESCE(LTRIM(@.logstmt),'-') + ' >> ' + RTRIM(@.file))
EXEC master..xp_cmdshell @.query, NO_OUTPUT
END
ELSE
BEGIN
SET @.logstmt = CONVERT(varchar(20),GETDATE(),9) + ', ' + @.eventType + ' successful';
SET @.query = RTRIM('echo ' + COALESCE(LTRIM(@.logstmt),'-') + ' >> ' + RTRIM(@.file))
EXEC master..xp_cmdshell @.query, NO_OUTPUT
END
END;
i am using sql express.
|||DDL Triggers only fire for successfully completed DDL. If the DDL fails due to a error like a duplicate object, then the DDL trigger is never fired. If you want to audit error messages for failed DDL, you will have to use profiler or trace to observe the failures.|||Hi:
Thank you for the reply.
I would like to know if there is any other mechnism to log unsucessfully executed DDL stmt.
All error while executing DDL stmts should be logged into log file.
By looking the log file, we should know which ddl stmts have executed and which have failed.
Please let me know of any solution.
i am using sqlcmd for executing .sql files. SQL Express is my database.
|||Hi,
there is no builtin way to do this, you can temporary setup profiler to get the exception that come up while using false DDL statement, but using the profiler should only be a temporary solution as it it (in my opinion) very expensive.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Tracing is more expensive when capturing unneeded columns, when filtering rows (since events are generated no matter what is filtered), and (most importantly) when the trace consumer (i.e., the trace table or the trace file) cannot consume those traced events in an expedient manner. Traces will drive up logical reads. Your experience will depend upon the trace system you design/choose (in geometric addition to load upon the client/server system).
For failed DDL, one should hope that (say) 1000 such events per second (or minute) is never seen 
You can fire up SQL Server Profiler (the GUI) to create a server-side script (best to forbid the GUI in production and strive to avoid the GUI in development), remove the "profiler" filter (if present), and (for that scalability testing) profile the impact of profiler
Keep it short, sweet, and simple. And remember that you cannot squeeze an elephant through the eye of a needle
