xp_smtp_sendmail
========================
SQLServer got build in xp_sendmail procedure.
xp_smtp_sendmail is a better alternative to xp_sendmail.
It is better, because it has all the SMTP options.
========================
How to setup xp_smtp_sendmail
========================
========================
- For SQL Server 2000, download xpsmtp80.dll file from here
- Copy xpsmtp80.dll into the SQL Server BINN directory.
For SQL Server 2000 the default location is:
"C:\Program Files\Microsoft SQL Server\MSSQL\Binn" - Register the extended stored procedure xp_smtp_sendmail by using sp_addextendedproc against the master database:
exec sp_addextendedproc 'xp_smtp_sendmail','xpsmtp80.dll' - Grant rights to the users using SQL Query Analyzer by executing the below query against the master database:
grant execute on xp_smtp_sendmail to publicBy default only the member of the sysadmin role have execution rights on the extended stored procedures. - Once the first 4 steps are done, execute the below code to send the email:
exec master.dbo.xp_smtp_sendmail @from = 'from_user@my_smtp_server.com', @to = 'to_user@my_smtp_server.com', @cc='cc_user@my_smtp_server.com', @subject = 'Mail Subject', @message = 'Mail Body', @type = 'html', @server = 'my_smtp_server address'
call to master.dbo.xp_smtp_sendmail example
==========================
USE [SupportTool]
GO
/****** Object: StoredProcedure [dbo].[send_email] Script Date: 01/06/2016 18:57:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[send_email] @p_to_list [nvarchar](1000),
@p_cc_list [nvarchar](1000),
@p_message [nvarchar](4000), @p_subject [nvarchar](1000) AS
DECLARE @from_user [nvarchar](100);
DECLARE @smtp_server [nvarchar](100);
DECLARE @mail_type [nvarchar](100);
SET @from_user = 'from_user@starhome.com';
SET @smtp_server = 'smtp_server.starhome.local';
SET @mail_type = 'text/html';
BEGIN
EXEC master.dbo.xp_smtp_sendmail
@to = @p_to_list,
@from = @from_user,
@message = @p_message,
@subject = @p_subject,
@type = @mail_type,
@server = @smtp_server;
END
To send an HTML content:
USE [SupportTool]
GO
/****** Object: StoredProcedure [udik].[am_send_email] Script Date: 01/07/2016 11:47:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[am_send_email] @p_to_list [nvarchar](1000),
@p_cc_list [nvarchar](1000),
@p_message [nvarchar](4000),
@p_subject [nvarchar](1000) AS
DECLARE @from_user [nvarchar](100);
DECLARE @smtp_server [nvarchar](100);
DECLARE @mail_type [nvarchar](100);
SET @from_user = 'user_a@starhome.com';
SET @smtp_server = 'server.starhome.local';
SET @mail_type = N'TEXT/HTML';
BEGIN
EXEC master.dbo.xp_smtp_sendmail
@to = @p_to_list,
@cc= @p_cc_list,
@from = @from_user,
@message = @p_message,
@subject = @p_subject,
@type = @mail_type,
@server = @smtp_server;
END
GO
/****** Object: StoredProcedure [udik].[am_send_email] Script Date: 01/07/2016 11:47:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[am_send_email] @p_to_list [nvarchar](1000),
@p_cc_list [nvarchar](1000),
@p_message [nvarchar](4000),
@p_subject [nvarchar](1000) AS
DECLARE @from_user [nvarchar](100);
DECLARE @smtp_server [nvarchar](100);
DECLARE @mail_type [nvarchar](100);
SET @from_user = 'user_a@starhome.com';
SET @smtp_server = 'server.starhome.local';
SET @mail_type = N'TEXT/HTML';
BEGIN
EXEC master.dbo.xp_smtp_sendmail
@to = @p_to_list,
@cc= @p_cc_list,
@from = @from_user,
@message = @p_message,
@subject = @p_subject,
@type = @mail_type,
@server = @smtp_server;
END
And this
is the usage:
BEGIN
DECLARE @messsage_body nvarchar(4000)
SET @messsage_body = N'<html><meta http-equiv="Content-Type"
content="text/html;
charset=iso-8859-1"><body><Title>This is
Title</Title><TABLE border="1"
bgcolor="DARKBLUE"><tr bgcolor="WHITE"><td>field
1</td><td>field 2</td></tr
bgcolor="WHITE"></TABLE>blablabla</body></html>'
EXEC [SupportTool].[udik].[am_send_email]
@p_to_list = 'user1@starhomemach.com;user2@starhomemach.com',
@p_cc_list = 'user3@starhomemach.com;user4@starhomemach.com',
@p_message
= @messsage_body,
@p_subject = 'This is a
html mail Subject'
END
nice coding thank you for sharing Sql Server dba Online Training
ReplyDelete