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

avatar image

Katie 1
1.4k 132 164 205

(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

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(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

avatar image

Henrik Staun Poulsen
589 14 17 20

(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;

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

avatar image

DaniSQL
4.9k 33 39 43

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

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:

x2091
x100
x13

asked: Jun 17, 2010 at 02:39 PM

Seen: 1330 times

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

Copyright 2016 Redgate Software. Privacy Policy