Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Passing alert message to my Job Expand / Collapse
Author
Message
Posted Friday, May 2, 2008 7:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2008 2:23 PM
Points: 5, Visits: 19
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
Post #494214
Posted Friday, May 2, 2008 7:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
What is your third party mail application using if it is not using SMTP or POP3?
Post #494243
Posted Friday, May 2, 2008 7:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
- 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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #494244
Posted Friday, May 2, 2008 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2008 2:23 PM
Points: 5, Visits: 19
I know this is going to sound stupid but, how would I write the alert information into a table?
Post #494267
Posted Friday, May 2, 2008 8:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
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?
Post #494272
Posted Friday, May 2, 2008 8:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:57 PM
Points: 2,281, Visits: 4,225
" 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
Post #494287
Posted Friday, May 2, 2008 9:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2008 2:23 PM
Points: 5, Visits: 19
I am using sendemail
Post #494310
Posted Friday, May 2, 2008 9:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
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.
Post #494317
Posted Friday, May 2, 2008 9:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2008 2:23 PM
Points: 5, Visits: 19
I there an easier way of doing the same thing with out loggin into a mail server?
Post #494324
Posted Friday, May 2, 2008 9:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
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.
Post #494333
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse