First we need to Enable DB Mail Option using query, admin only can do this operation,
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
Example of Manual Config :
Som example for sending DB Mail is in the below link :
Sample 1 :
SET ANSI _NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE usp_SendMail
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sub NVARCHAR(4000)
DECLARE @body1 NVARCHAR(max)
DECLARE @ERRMSG VARCHAR(max)
SET @sub = 'Test Mail -- ' + CONVERT(VARCHAR,GETDATE())
SET @body1 = 'Hi Team,'
BEGIN TRY
EXEC msdb.dbo.sp_send_dbmail @profile_name='DBA', -- Need to create a Profile using Management - > DB Mail - > Config DB Mail
@recipients='nsuresh@hovservices.in',
@copy_recipients='vijayans@hovservices.in',
@query='SET NOCOUNT ON
SELECT ''<table border=2 bgcolor=gray font-family=Verdana font-size=medium>
<tr>
<td><B>Test Mail</B></td></tr>''
SELECT ''/<table>''',
@query_result_no_padding=1
END TRY
BEGIN CATCH
SET @ERRMSG = ERROR_MESSAGE()
SET @ERRMSG=REPLACE(@ERRMSG,'''','')
SET @sub = 'Log -- ERROR' + CONVERT(VARCHAR,GETDATE())
EXEC msdb.dbo.sp_send_dbmail @profile_name='DBA',@recipients='nsuresh@hovservices.in',
@copy_recipients='vijayans@hovservices.in',
@subject=@sub,@body=@ERRMSG,@body_format='HTML',
@query_result_no_padding=1
END CATCH
END
Sample 2 :
-- Create a Account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'PFP Account',
@description = 'Mail account for used by PFP Users',
@email_address = 'nsuresh@hovservices.in',
@display_name = 'PFP DEV Mail',
@replyto_address = 'nsuresh@hovservices.in',
@mailserver_name = '10.16.5.39';
-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'PFP Profile',
@description = 'Mail Profile for use by PFP Process';
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'PFP Profile',
@account_name = 'PFP Account',
@sequence_number =1 ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'PFP Profile',
@recipients = 'vijayans@hovservices.in',
@body = 'Profile has been created',
@subject = 'PFP' ;
02:20 |
Category:
SQL SERVER
|
0
comments
Comments (0)