none
Sending email by using a stored procedure - SQL Server 2005

    Question

  • Hi,

    I'm searching a stored procedure solution to send email in SQL Server 2005. In the production db server I cannot change the SQL Server installation and/or configuration because it hosts other application dbs. Moreover, I want to avoid to use the Send Mail task of SSIS. So, I'd like to find a stored procedure to send mail without using SQL mail or database mail.

    Any ideas, please? Thanks

    Friday, August 31, 2012 8:36 AM

Answers

  • Hi,

    I can think below can be one more workaround to this situation..

    As you have said that, you are using Send Mail SSIS task on some other box. You can try to use that functionality..

    1. You can create a procedure which stores all the fields(To, CC, Subject, Body) in one table and also add one flag and key to row for tracking.

    2. One the box where SSIS is setup, you can schedule SSIS to run every 10 mins or so which can connect to this SQL server, take the mail records in record set, send mail in for each loop..

    3. In the same for each task, you can update the flag into table.

    Although this approach is not suitable for mass mailing, this can work if you have small numbers of mails to be sent. The only drawback of this approach is there is a delay in sending mail and some load on SSIS box. 


    - Chintak (My Blog)

    Friday, August 31, 2012 11:16 AM

All replies

  • Hi  ,

    U can use system stored procedure for db mail in SQL server.

    Have look at this example.

    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Adventure Works Administrator', @recipients = 'danw@Adventure-Works.com', @body = 'The stored procedure finished successfully.', @subject = 'Automated Success Message' ;

    Please have look on the comment




    • Edited by Santhosh H Friday, August 31, 2012 8:50 AM
    Friday, August 31, 2012 8:39 AM
  • SQL Mail(not safe and deprecated) and DB Mail are the two ways to achieve sending mail from SQL server. Aprt from these I dont think SQL has any other method to do so.

    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Friday, August 31, 2012 8:39 AM
  • Hi,

    The another alternative is to use CLR. You can find more info on this approach in below link

    http://www.mssqltips.com/sqlservertip/1795/send-email-from-sql-server-express-using-a-clr-stored-procedure/

    But, even for this you change configuration option CLR Enabled to 1 if this is already not 1. 


    - Chintak (My Blog)

    Friday, August 31, 2012 8:41 AM
  • Hi,

    I'm searching a stored procedure solution to send email in SQL Server 2005. In the production db server I cannot change the SQL Server installation and/or configuration because it hosts other application dbs. Moreover, I want to avoid to use the Send Mail task of SSIS. So, I'd like to find a stored procedure to send mail without using SQL mail or database mail.

    Any ideas, please? Thanks

    The way is to send out mails by the database.

    http://msdn.microsoft.com/en-us/library/ms190307.aspx


    Many Thanks & Best Regards, Hua Min

    Friday, August 31, 2012 8:42 AM
  • dbmail or CLR methods does need some configuration change on the server. If you can't  do this, you may need to create a windows service that reads the required data from a table and sends it

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, August 31, 2012 8:46 AM
  • Hi,

    You need to first configure databasemail in SQL Server by creating Account & Profiles, then you can use the Store procedure to send emails.

    Check following blog post where I've put up step-by-step to configure DBmail and issues I faced, link: http://sqlwithmanoj.wordpress.com/2010/09/29/database-mail-setup-sql-server-2005/


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011

    Friday, August 31, 2012 8:47 AM
  • Hi Chintak, thanks for your reply!

    Your idea is a good one, but perhaps I won't authorized to change the configuration option for security reasons. Put a DLL on a production db server (in cluster) couldn't be a problem: I don't put it in the GAC.

    However, I'm already using the Send Mail Task, for other activities, on a separate server with SSIS from db server because I haven't any authorizations to install SSIS on the production db server (in cluster). Now, I'd like to use a stored procedure to send mail.

    Thanks

    Friday, August 31, 2012 10:37 AM
  • Hey,

    Why don't u use this System stored Procedure

    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Adventure Works Administrator', @recipients = 'danw@Adventure-Works.com', @body = 'The stored procedure finished successfully.', @subject = 'Automated Success Message' ;


    Please have look on the comment

    Friday, August 31, 2012 10:49 AM
  • Hi SantoshH,

    to use this system sp do I need to configure database mail? If yes, I will be not authorized to change the production db server (in cluster) configuration.

    Thanks

    Friday, August 31, 2012 10:55 AM
  • Hi ,

    If u have sufficient rights then u can use the following to configure it,even if u r added as sysadmin user then u can use this scripts and use system Procedure.Let me know. Since u have already using the Send mail tasks u can use the same profilename for sending mails.

    sp_CONFIGURE 'show advanced'1
    GO
    RECONFIGURE
    GO
    sp_CONFIGURE 'Database Mail XPs'1
    GO
    RECONFIGURE
    GO


    Please hav look on the comment


    • Edited by Santhosh H Friday, August 31, 2012 11:12 AM
    Friday, August 31, 2012 11:11 AM
  • Hi,

    I can think below can be one more workaround to this situation..

    As you have said that, you are using Send Mail SSIS task on some other box. You can try to use that functionality..

    1. You can create a procedure which stores all the fields(To, CC, Subject, Body) in one table and also add one flag and key to row for tracking.

    2. One the box where SSIS is setup, you can schedule SSIS to run every 10 mins or so which can connect to this SQL server, take the mail records in record set, send mail in for each loop..

    3. In the same for each task, you can update the flag into table.

    Although this approach is not suitable for mass mailing, this can work if you have small numbers of mails to be sent. The only drawback of this approach is there is a delay in sending mail and some load on SSIS box. 


    - Chintak (My Blog)

    Friday, August 31, 2012 11:16 AM
  • Ok SanthoshH, but I need to define a profile for Database Mail.

    Moreover, the SSIS pkgs are on a separate server from the db server. I don't have a profile for the Send Mail task but a SMTP connection.

    Thanks

    Friday, August 31, 2012 2:54 PM
  • Yes,U can do that once if u create a Profile then sending mail ll be done thru SP


    Please have look on the comment

    Friday, August 31, 2012 3:02 PM
  • As I said I could not be authorized to do any changes on the production db server (in cluster).

    For this reason, I had to use another server to have SSIS.

    Thanks

    Friday, August 31, 2012 3:13 PM