SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Passing alert message to my Job


Passing alert message to my Job

Author
Message
S Campos
S Campos
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
Michael Earl-395764
Michael Earl-395764
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4889 Visits: 23078
What is your third party mail application using if it is not using SMTP or POP3?
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12095 Visits: 8924
- 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


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
S Campos
S Campos
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 19
I know this is going to sound stupid but, how would I write the alert information into a table?
Michael Earl-395764
Michael Earl-395764
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4889 Visits: 23078
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?
Carl Federl
Carl Federl
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3246 Visits: 4350
" 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
S Campos
S Campos
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 19
I am using sendemail
Michael Earl-395764
Michael Earl-395764
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4889 Visits: 23078
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.
S Campos
S Campos
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 19
I there an easier way of doing the same thing with out loggin into a mail server?
Michael Earl-395764
Michael Earl-395764
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4889 Visits: 23078
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search