October 22, 2003 at 7:30 am
I'd like to build an alert process that is able to parce the Alert Error Text and use some of the information in the SQL Agent Job to perform some logic. Specifically I am building an alert based on the error number 18246. The message text looks something like this: "Database backed up with following information: Database: %1, creation date and time: %2(%3), pages dumped: %4!d!, first LSN: %5, last LSN: %6, sort order: %7!d!, striped: %8, number of dump devices: %9!d!, device information: (%10)." I'd like to take %10 (which is the name of the backup) and pass it to a SQL Server agent job. Does anyone know of a way to do this?
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 24, 2003 at 12:13 am
AFAIK there is no feedback to the job launched by sqlagent and it will only document that the job is started by the alert-related jobschedule. Based on this, I guess the only way to go is to read the sever-logfile and process the latest (range) message(s).
Would be nice to have a direct event-input (=errormessage).
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
October 28, 2003 at 7:32 am
That is kind of what I thought. Thank you for the feedback.
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 5, 2003 at 8:21 am
Here is a thought
Once the Alert is Setup You have the Option to Include the ALERT ERROR TEXT in Email to the specified Operator and if you create an Operator with the SQL Server email account you will be effectively sending the email sql server account, which can use sp_readmail to parse the message!
I know this is just a hack and not all places are allowed to use sp_readmail but hey this is an answer to your question
(IMHO better than nothing)
HTH
* Noel
November 5, 2003 at 11:06 am
Interesting thought. Hummm..........
Right now we don't allow anyone to send mail to SQL Server.
It has been a long time since I played with xp_readmail and sending email to SQL Server. Can the xp_sendmail process actually parse a note? I kind of remember the message body needed to contain a sql query string of somekind.
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 5, 2003 at 4:02 pm
SOrry for this late reply I am very busy 😉
XP_READMAIL is what you could use to read the body an the mail is sent by the SQL server AGENT Alert!
I hope it makes sense
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply