Email from DDL trigger

  • 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

  • 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!

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

  • 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