You must Sign In to post a response.
  • Category: SQL Server

    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
    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
    [Response removed by Admin. Read forum policies.]
    Regards,
    SonyShiva
    Never lose hope..You never know what tomorrow will bring

  • #768050
    [Response removed by Admin. Read forum policies.]

  • #768051
    [Response removed by Admin. Read forum policies.]

  • #768052

  • #768063
    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
    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]


Sign In to post your comments