June 19, 2010 at 7:15 pm
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
June 19, 2010 at 8:33 pm
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
June 20, 2010 at 6:38 am
Thanks Carpal but the email still doesn't include the select statement. Any ideas? I'm running SQL 2008 R2.
June 20, 2010 at 7:17 am
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'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply