-------------------------------------------------------------
--
-- You can create a database mail profile 
-- either using "Database Mail Configuration" wizard by
-- Mgmt Studio ->SQL Server instance -> Managment -> RightClick Databae Mail ...
-- or use the template code given below (in a query window):
--
--  Database Mail Simple Configuration Template.
--
--  This template creates a Database Mail profile, an SMTP account and 
--  associates the account to the profile.
--
--  Edit the information for profileName, accountName, and emailAddress
--  with appropriate values. 
--  For each database mail profile, this code can be executed
--    successfully only one time.
--
-- Check the successful creation of the mail profile with the following SQL stmt
--   SELECT * FROM msdb.dbo.sysmail_profile  -- lists all database mail profiles
--
--  Since you are a sysadmin, you have access to this profile automatically.  
--  To grant access to the new profile for users who are not
--  members of sysadmin, use msdb.dbo.sysmail_add_principalprofile
-------------------------------------------------------------

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 = 'Snook05';

	 -- Account information. Replace with the information for your account.
		SET @account_name = 'SnookInstance05';

	 -- Use the following exact string for smtp server information
		SET @SMTP_servername = 'smtp.cs.fiu.edu';

	 -- Include your FIU email address
		SET @email_address = 'myusername@fiu.edu';

		SET @display_name = 'SQL Server Mail Acct';


-- 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 (<profile_name,sysname,SampleProfile>) 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 (<account_name,sysname,SampleAccount>) 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 (<account_name,sysname,SampleAccount>).', 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 (<profile_name,sysname,SampleProfile>).', 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 (<account_name,sysname,SampleAccount>).', 16, 1) ;
	ROLLBACK TRANSACTION;
    GOTO done;
END;

COMMIT TRANSACTION;

Print 'Database mail profile '+ @profile_name+ ' has been created successfully';

done:

GO
