Passing alert message to my Job

  • I have a case were the client does not want to user sql mail, so no pop3 or smtp. I want to still send out mail using a small third party sendmail. I have alert that exec a job but I don't know how to pass the alert message to the job. I would like to have some input as to how or even if I should do it this way.

    Thank you in advance

  • What is your third party mail application using if it is not using SMTP or POP3?

  • - Write the message to a proprietary message table and let the job that is started by the alert read that table and cleanup the data.

    - SQLServer Service Broker can help you with this.

    This is the sqlserver message queue system.

    You write a message to a SSB queue, the queue executes a sproc.

    There are a couple of nice intro articles at SSC.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I know this is going to sound stupid but, how would I write the alert information into a table?

  • You could replace the T-SQL in the sp_send_dbmail procedure with a call to your mail client and you should find that SQL does not know that you have bypassed SQL Mail. You could also replace the queue processing procedure for SQL mail (it uses service broker) and basically do the same thing. I would question the use of a third party mail system though. Most use SMTP for sending mail, so you may simply be bypassing SQL Mail and ending up doing the exact same thing in a less-tested way.

    What mail application are you using?

  • " I have alert that exec a job but I don't know how to pass the alert message to the job."

    The documentation regarding alert tokens ( variables ) is well hidden and described under "sp_add_jobstep (Transact-SQL) " An example of a alert token is

    $(A-DBN)

    Database name. If the job is run by an alert, the database name value automatically replaces this token in the job step.

    By default, alert token access is disabled but can be enabled by configuring the SQL Server Agent. See BOL "Using Tokens in Job Steps"

    SQL = Scarcely Qualifies as a Language

  • I am using sendemail

  • I am not familiar with it, but if it is this one:

    http://caspian.dotconf.net/menu/Software/SendEmail/

    it is an SMTP mail client. I am not sure how you would benefit from using this over SQL Server database mail. I know you have to comply with the wishes of the client, but it seems like this would simply add a layer of complexity and reduce security.

  • I there an easier way of doing the same thing with out loggin into a mail server?

  • I am not sure what you mean. The client you were planning to use does use an SMTP server of some sort. It may be a local one right within the client or it may be relaying it somehow, but there has to be an SMTP server of some sort.

    Database mail works with pretty much any SMTP server. So you could configure any windows server with IIS and SMTP through the windows components installer. The companies I have worked for have always used Exchange, Lotus Notes, or some other email system (hosted locally) and I database mail would be configured to simply use the SMTP server on one of these servers. This is typically more secure than just turning on SMTP on a web server somewhere and using it to send email.

  • I thank you for all your advice. I am going to talk to the client and see if I can configure it the way you suggest.

    Thanks again.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply