SQL Server Performance

Help - Script that mails when DB fails

Discussion in 'Performance Tuning for DBAs' started by vaddi, Aug 31, 2006.

  1. vaddi New Member

    Hello

    I have got a script that mails the dba mail box when the database backup fails. But I have to change the servernames and database names, wherever I implement on different databases. I would like to know how I can make it more dynamic , in the sense general so that it takes the parameters where ever I implement. The mail I would be getting should be like :

    exec master.dbo.sendmail
    @to = 'droche@depaul.edu,
    @subject = 'MSSQL Database Backup Failure Notification',
    @message ='Server name = %COMPUTERNAME% , Database Name Test Backup Failed' ;

    So I should be able to pass the parameters servername and database name.

    The script that I am using for the mailing is :


    USE master;
    GO
    CREATE PROCEDURE dbo.SendMail
    @to VARCHAR(255),
    @subject VARCHAR(255),
    @message VARCHAR(8000)
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE
    @rv INT,
    @from VARCHAR(64),
    @server VARCHAR(255);
    SELECT
    @from = 'testsql2000@is.depaul.edu',
    @server = 'smtp.depaul.edu';
    EXEC @rv = dbo.xp_smtp_sendmail
    @to = @to,
    @from = @from,
    @message = @message,
    @subject = @subject,
    @server = @server;
    END
    GO


    --- After the above script is run the following should be given in the 2nd step when
    --- the backup jobs are scheduled ------

    exec master.dbo.sendmail
    @to = 'dvaddi@depaul.edu',
    @subject =' Test sqlserver 2000',
    @message ='Test Database Backup Failed' ;

    Thanks
  2. satya Moderator

    @@Servername and db_name are useful for such references, review BOL in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page