Passing alert message to my Job

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Passing alert message to my Job

11 posts, Page 1 of 2

1 2 »»

Passing alert message to my Job

Rate Topic

Display Mode

Topic Options

Author

Message

S Campos

Posted Friday, May 02, 2008 7:09 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 05, 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

Michael Earl-395764

Posted Friday, May 02, 2008 7:50 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

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

Post #494243

ALZDBA

Posted Friday, May 02, 2008 7:51 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:17 AM
Points: 6,862, Visits: 8,049

- 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

     Jul 13

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

- 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" BigGrin

Need a bit of Powershell? How about this

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

Post #494244

S Campos

Posted Friday, May 02, 2008 8:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 05, 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

Michael Earl-395764

Posted Friday, May 02, 2008 8:25 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, 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

Carl Federl

Posted Friday, May 02, 2008 8:41 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:32 PM
Points: 2,224, Visits: 4,083

" 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

S Campos

Posted Friday, May 02, 2008 9:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 05, 2008 2:23 PM
Points: 5, Visits: 19

I am using sendemail

Post #494310

Michael Earl-395764

Posted Friday, May 02, 2008 9:06 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, 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

S Campos

Posted Friday, May 02, 2008 9:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 05, 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

Michael Earl-395764

Posted Friday, May 02, 2008 9:21 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, 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 »

11 posts, Page 1 of 2

1 2 »»

Permissions