question

Katie 1 avatar image
Katie 1 asked

Database notification via email

Guys,

I need to configure the database for alerting and notification purposes. I created a job and chose to have alerted, when ever the job is successful. I also created an operator. To test it, I started with an example, and gave my gmail id. Obviously it does not work. I tried with putting in my work email to do a test. It does not work.

I dont understand what am i missing here.. do i have to enable anything in the background?

sql-server-2008jobalerts
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

There's a few steps to get mail working, there are profiles and accounts to create for the server to use to carry out the send. It sounds like you have then receive side of things set up already. This http://msdn.microsoft.com/en-us/library/ms175887.aspx should get you sorted.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Henrik Staun Poulsen avatar image
Henrik Staun Poulsen answered

hi Katie,

This may help you: Failed to notify operator

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL avatar image
DaniSQL answered

------------------------------------------------------------- -- Database Mail Simple Configuration Template. -- -- This template creates a Database Mail profile, an SMTP account and -- associates the account to the profile. -- The template does not grant access to the new profile for -- any database principals. Use msdb.dbo.sysmail_add_principalprofile -- to grant access to the new profile for users who are not -- members of sysadmin. -------------------------------------------------------------

DECLARE @profile_name sysname,
        @account_name sysname,
        @SMTP_servername sysname,
        @email_address NVARCHAR(128),
        @display_name NVARCHAR(128);

-- Profile name. Replace with the name for your profile
        SET @profile_name = 'MyProfile';

-- Account information. Replace with the information for your account.

        SET @account_name = 'MyAccount';
        SET @SMTP_servername = 'mail.yourcompany.com';
        SET @email_address = 'dba@yourcompany.com';
        SET @display_name = 'SQL Alerts';


-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
  RAISERROR('The specified Database Mail profile (MyProfile) already exists.', 16, 1);
  GOTO done;
END;

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
 RAISERROR('The specified Database Mail account (MyAccount) already exists.', 16, 1) ;
 GOTO done;
END;

-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;

DECLARE @rv INT;

-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
    @account_name = @account_name,
    @email_address = @email_address,
    @display_name = @display_name,
    @mailserver_name = @SMTP_servername;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail account (MyAccount).', 16, 1) ;
    GOTO done;
END

-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
    @profile_name = @profile_name ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail profile (MyProfile).', 16, 1);
    ROLLBACK TRANSACTION;
    GOTO done;
END;

-- Associate the account with the profile.

    EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
        @profile_name = @profile_name,
        @account_name = @account_name,
        @sequence_number = 1 ;

    IF @rv<>0
    BEGIN
        RAISERROR('Failed to associate the speficied profile with the specified account (MyProfile).', 16, 1) ;
        ROLLBACK TRANSACTION;
        GOTO done;
    END;

    COMMIT TRANSACTION;

    done:

    GO

------Turning on Database mail 

    sp_CONFIGURE 'show advanced', 1
    GO
    RECONFIGURE
    GO
    sp_CONFIGURE 'Database Mail XPs', 1
    GO
    RECONFIGURE
    GO 
    sp_CONFIGURE 'show advanced', 0
    GO
    RECONFIGURE
    GO

If you want to to configure your gmail instead of your company mail server follow this article (Port number is 587 and use smtp.gmail.com as smtp server name.)

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.