x

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?

more ▼

asked Jun 17, 2010 at 02:39 PM in Default

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Jun 17, 2010 at 03:25 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left

hi Katie,

This may help you: Failed to notify operator

more ▼

answered Jun 23, 2010 at 09:30 AM

Henrik Staun Poulsen gravatar image

Henrik Staun Poulsen
579 13 15 16

(comments are locked)
10|1200 characters needed characters left

------------------------------------------------------------- -- 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.)

more ▼

answered Jun 23, 2010 at 11:02 AM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1842
x86
x11

asked: Jun 17, 2010 at 02:39 PM

Seen: 1119 times

Last Updated: Jun 17, 2010 at 02:39 PM