locked
Can sql server database react to email messages sent to it RRS feed

  • Question

  • All,

     Is it possible to have sql server receive, process emails and execute stored procedures based on the content of the email.

    TIA

    Monday, August 30, 2010 4:41 PM

Answers

  • Yes ,take a look into msdb.dbo.sp_send_dbmail in the BOL

    EXEC

     

    msdb.dbo.sp_send_dbmail

    @profile_name

    = 'Profile Name',

    @recipients

    = 'KKKK@google.com',

    @query

    = 'SELECT COUNT(*) FROM AdventureWorks.Person.Address',

    @subject

    = 'Error Log Count',

    @attach_query_result_as_file

    = 1 ;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 31, 2010 5:32 AM
  • Hmm Perhaps you need to look into SQL Service Broker mechanism???


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 1, 2010 5:33 AM
  • You can try this way:

    http://www.eggheadcafe.com/community/aspnet/13/10137356/open-email-attachment-and-load-into-sql-server.aspx

    thanks,
    - MS (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    Wednesday, September 1, 2010 7:00 AM

All replies

  • Hi,

      Please move this post to TSQL or Database Engine forum .


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Tuesday, August 31, 2010 3:54 AM
  • Yes ,take a look into msdb.dbo.sp_send_dbmail in the BOL

    EXEC

     

    msdb.dbo.sp_send_dbmail

    @profile_name

    = 'Profile Name',

    @recipients

    = 'KKKK@google.com',

    @query

    = 'SELECT COUNT(*) FROM AdventureWorks.Person.Address',

    @subject

    = 'Error Log Count',

    @attach_query_result_as_file

    = 1 ;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 31, 2010 5:32 AM
  • I need the database to process received emails.  A use case would be on receipt of an email parse the  body of the email (or an attached file) and update the database.

    The extended stored procedures  sp_processmail returns a result set to the sender based on the body of the email containing a query. 

    I wanted to use email to implemtent a messaging mechanism where data can be loaded directly into the database direclty from an email rather than loading csv files received via ftp.

     

    Tuesday, August 31, 2010 7:26 PM
  • Hmm Perhaps you need to look into SQL Service Broker mechanism???


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 1, 2010 5:33 AM
  • You can try this way:

    http://www.eggheadcafe.com/community/aspnet/13/10137356/open-email-attachment-and-load-into-sql-server.aspx

    thanks,
    - MS (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    Wednesday, September 1, 2010 7:00 AM