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