Email from DDL trigger

Question

Post reply

Email from DDL trigger

smitty-1088185

SSCrazy

Points: 2321
More actions
June 19, 2010 at 7:15 pm

#143821

Is there a way to get this select statement inserted into the email notification?
CREATE TRIGGER [ddl_trig_database]
ON ALL SERVER
FOR CREATE_DATABASE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'me@home.com',
@body = SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),
@subject = 'DATABASE MODIFICATION'
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

smitty your syntax was close; i tested this with my profile, and it works fine:

ALTER TRIGGER [ddl_trig_database]

ON ALL SERVER

FOR CREATE_DATABASE

AS

declare @results varchar(max)

SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default Notifications',

@recipients = 'lowell@mydomain.com',

@body = @results,

@subject = 'DATABASE MODIFICATION'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

smitty-1088185 SSCrazy Points: 2321 More actions · Answer 2

Thanks Carpal but the email still doesn't include the select statement. Any ideas? I'm running SQL 2008 R2.

smitty-1088185 SSCrazy Points: 2321 More actions · Answer 3

Hey Carpal, I got it to work. Look at the changes I made below.

Alter TRIGGER [ddl_trig_database]

ON ALL SERVER

FOR CREATE_DATABASE

AS

declare @results varchar(max)

SET @results = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default',

@recipients = 'me@home.com',

@body = @results,

@subject = 'DATABASE MODIFICATION'