Category: SQL Server

04 Oct 2016 11:26

Joined:	12/02/2013
Level:	Silver
Points:	147

Points:	2

Have to send mail using SQL Script

Hi Friends,

1. I need to send mail from sql without outlook. I have tried many things in google, but they are suggesting with outlook only. Is there any other possibility to send mail without outlook?

2. Also i need to set mail profile. Am using SQL SERVER 2000.

Thanks in advance.

#768014

04 Oct 2016 12:06

Joined:	18/12/2008
Level:	Gold
Points:	2921

Points:	4

Hi,

Configure Mail and sending it through SQL Server is very simple.
We have mail integrated to every application now a days. We integrate email using SMTP settings in the Web.Config in .NET and use the Send method to send mails.

Remember we will be using pre defined Stored procedure to send the mails. First of all we need to set up an account with the credentials required by the server to send the mails. Usually the mail is sent through SMTP, Simple Mail Transfer Protocol.


sp_send_dbmail

This is the inbuilt procedure in SQL which will take care of sending mails.
Complete code with parameters will be,


sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]  
    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]  
    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]  
    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]  
    [ , [ @from_address = ] 'from_address' ]  
    [ , [ @reply_to = ] 'reply_to' ]   
    [ , [ @subject = ] 'subject' ]   
    [ , [ @body = ] 'body' ]   
    [ , [ @body_format = ] 'body_format' ]  
    [ , [ @importance = ] 'importance' ]  
    [ , [ @sensitivity = ] 'sensitivity' ]  
    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]  
    [ , [ @query = ] 'query' ]  
    [ , [ @execute_query_database = ] 'execute_query_database' ]  
    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]  
    [ , [ @query_attachment_filename = ] query_attachment_filename ]  
    [ , [ @query_result_header = ] query_result_header ]  
    [ , [ @query_result_width = ] query_result_width ]  
    [ , [ @query_result_separator = ] 'query_result_separator' ]  
    [ , [ @exclude_query_output = ] exclude_query_output ]  
    [ , [ @append_query_error = ] append_query_error ]  
    [ , [ @query_no_truncate = ] query_no_truncate ]   
    [ , [ @query_result_no_padding = ] @query_result_no_padding ]   
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

To Create a Database Account,


EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'SendEmailSqlDemoAccount'
  , @description = 'Sending SMTP mails to users'
  , @email_address = 'suraj.0241@gmail.com'
  , @display_name = 'Suraj Sahoo'
  , @replyto_address = 'suraj.0241@gmail.com'
  , @mailserver_name = 'smtp.gmail.com'
  , @port = 587
  , @username = 'XXXXXX'
  , @password = 'XXXXXX'
Go

To Create a Profile,


EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'SendEmailSqlDemo'
  , @account_name = 'SendEmailSql'
  , @sequence_number = 1
GO

Further Step by Step process check on the below site,


http://www.codeproject.com/Tips/846204/How-To-Send-Mail-Using-SQL-Server-Part

Thanks,
Mani

#768018

04 Oct 2016 14:47

Joined:	13/07/2011
Level:	Gold
Points:	4261

Points:	-5

[Response removed by Admin. Read forum policies.]

Regards,
SonyShiva
Never lose hope..You never know what tomorrow will bring

#768050

08 Oct 2016 10:22

Joined:	28/09/2016
Level:	Bronze
Points:	24

Points:	-5

[Response removed by Admin. Read forum policies.]

#768051

08 Oct 2016 11:20

Joined:	17/09/2016
Level:	Bronze
Points:	38

Points:	-5

[Response removed by Admin. Read forum policies.]

#768063

09 Oct 2016 16:41

Joined:	28/04/2006
Level:	Gold
Points:	29773

Points:	4

Hai Murugesan,
Below are the answers to your questions:
1. I need to send mail from sql without outlook. I have tried many things in google, but they are suggesting with outlook only. Is there any other possibility to send mail without outlook?
Ans. yes, if you want to send the email, you can use the Sql Server database but you need to make sure that the mail server for SMTP is enabled in the system.
There is an inbuilt extended stored procedure available in the Sql Server Management studio which can be used directly but the mail server should be enabled before using this feature.
xp_smtp_sendmail
2. Also i need to set mail profile. Am using SQL SERVER 2000.
And. To configure database mail, you can follow the below article:


http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

Hope it will be helpful to you.

Regards,
Pawan Awasthi(DNS MVM)
+91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
pawansoftit@gmail.com

#768124

18 Oct 2016 15:14

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	4

Just follow below steps
1. Log in to SQL Server 2008 with a correct user name and password
2. Expand the Management tab, then select SQL Server Logs, then right-click on Database Mail, then select Configure Database Mail
3. Select the Set up Database Mail by performing the following tasks: radio button
4. Click Next, then a confirmation box appears; click OK on that.
5. When you click OK, then write the profile name description and click on Add.
6. Then, a new window appears where you provide your profile name and configure Outgoing Mail Server(SMTP) then click Basic authentication and provide your email id and password then click OK.
7. Then in the new window, it will show your profile, check that and click Next.
8. configure it and click on Next and finish
9. Send a test email by right-clicking on database mail and click Send Test E-Mail
for more details check below link
http://www.codeproject.com/Tips/846204/How-To-Send-Mail-Using-SQL-Server-Part

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

Have to send mail using SQL Script

About dotnetspider.com

Quick Links

General

Services