Monday, March 19, 2012

Can the Job Notifications be configured to use a different transport mechanism?

(SQL Server 2000, SP3a)
Hello all!
In our Production environment, we're moving towards a configuration that will preclude the
use of SQL Mail. As far as we know, Job Notifications have three transport mechanisms:
E-Mail, Pager, and Net Send. From what we understand, E-Mail and Pager both require SQL
Mail, which would leave Net Send as our only available transport.
Is there any way to integrate an additional Notification transport into SQL Server?
Ideally, we'd like to use something like xp_smtp_sendmail. But, we're not sure how to
make the Notifications leverage something outside the default transports.
Thanks!
John PetersonFor xp_smtp_sendmail, you can just add another step that
sends the email. So if you wanted to notify on failure, just
add a step to the job that you branch to on failure. This
job step can handle the email. You won't have support for
operators but you can write your own - have a table with
whatever email, rules then query this table to find who
should be notified. You can then pass this value to
xp_smtp_sendmail.
-Sue
On Mon, 3 Nov 2003 14:06:18 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>In our Production environment, we're moving towards a configuration that will preclude the
>use of SQL Mail. As far as we know, Job Notifications have three transport mechanisms:
>E-Mail, Pager, and Net Send. From what we understand, E-Mail and Pager both require SQL
>Mail, which would leave Net Send as our only available transport.
>Is there any way to integrate an additional Notification transport into SQL Server?
>Ideally, we'd like to use something like xp_smtp_sendmail. But, we're not sure how to
>make the Notifications leverage something outside the default transports.
>Thanks!
>John Peterson
>|||Thanks, Sue!
I had hoped there was a way to integrate the existing Operator aspect, but introduce a new
transport mechanism. It sounds like it won't be that straightforward. :-(
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:85hdqvse38r38os3ul9b6rsvgi600nlt1v@.4ax.com...
> For xp_smtp_sendmail, you can just add another step that
> sends the email. So if you wanted to notify on failure, just
> add a step to the job that you branch to on failure. This
> job step can handle the email. You won't have support for
> operators but you can write your own - have a table with
> whatever email, rules then query this table to find who
> should be notified. You can then pass this value to
> xp_smtp_sendmail.
> -Sue
> On Mon, 3 Nov 2003 14:06:18 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3a)
> >
> >Hello all!
> >
> >In our Production environment, we're moving towards a configuration that will preclude
the
> >use of SQL Mail. As far as we know, Job Notifications have three transport mechanisms:
> >E-Mail, Pager, and Net Send. From what we understand, E-Mail and Pager both require
SQL
> >Mail, which would leave Net Send as our only available transport.
> >
> >Is there any way to integrate an additional Notification transport into SQL Server?
> >Ideally, we'd like to use something like xp_smtp_sendmail. But, we're not sure how to
> >make the Notifications leverage something outside the default transports.
> >
> >Thanks!
> >
> >John Peterson
> >
>|||No, it's a bit more of a PITA than that, but at the same time, it gives you
a lot more control. We use this mechanism and we have customized
subjects/bodies, inclusion lists based on various error levels, etc.
A
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> Thanks, Sue!
> I had hoped there was a way to integrate the existing Operator aspect, but
introduce a new
> transport mechanism. It sounds like it won't be that straightforward.
:-(|||Thanks Aaron (and Sue)!
Out of curiosity, do you know whether the Notifications is making a SQL call to
xp_sendmail? If so, would it be possible to "swap out" the underlying xp_sendmail .DLL
and replace it with a xp_smtp_sendmail "wrapper" with the same name as xp_sendmail?
I doubt that the Notifications is making a SQL call to "do its business", but I thought
I'd check. :-)
Thanks!
John Peterson
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> No, it's a bit more of a PITA than that, but at the same time, it gives you
> a lot more control. We use this mechanism and we have customized
> subjects/bodies, inclusion lists based on various error levels, etc.
> A
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > Thanks, Sue!
> >
> > I had hoped there was a way to integrate the existing Operator aspect, but
> introduce a new
> > transport mechanism. It sounds like it won't be that straightforward.
> :-(
>|||Alerts does not call xp_sendmail as it did in 6.5 and earlier. Nowadays,
Agent does its own MAPI calls.
I'm writing a utility that does xp_smtp_sendmail calls based on your current
alert configuration. The idea is to configure alerts as desired, and then
schedule my .exe every x minutes. My .exe reads off of the alert config
table and reads the eventlog. Based on when each configured alert was fired
the last time, the exe will xp_smtp_sendmail. The current idea is to use
disabled operators (if you enable them, then Agent will complain that it
can't send email).
I'm finishing up this util this week, then I have to wait for it to go on
the web (which might take a few days to a few weeks - I don't have direct
access to the web). Send me an email if you want to give it a try earlier.
I will put it on www.dbmaint.com, free utilities.
--
Tibor Karaszi
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> Thanks Aaron (and Sue)!
> Out of curiosity, do you know whether the Notifications is making a SQL
call to
> xp_sendmail? If so, would it be possible to "swap out" the underlying
xp_sendmail .DLL
> and replace it with a xp_smtp_sendmail "wrapper" with the same name as
xp_sendmail?
> I doubt that the Notifications is making a SQL call to "do its business",
but I thought
> I'd check. :-)
> Thanks!
> John Peterson
>
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > No, it's a bit more of a PITA than that, but at the same time, it gives
you
> > a lot more control. We use this mechanism and we have customized
> > subjects/bodies, inclusion lists based on various error levels, etc.
> >
> > A
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > Thanks, Sue!
> > >
> > > I had hoped there was a way to integrate the existing Operator aspect,
but
> > introduce a new
> > > transport mechanism. It sounds like it won't be that straightforward.
> > :-(
> >
> >
>|||Hello, Tibor! That sounds almost *exactly* what I'd be pining for! If you don't mind an
early beta tester, I'd be delighted to try it out earlier! :-)
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in
message news:QYApb.36350$dP1.121349@.newsc.telia.net...
> Alerts does not call xp_sendmail as it did in 6.5 and earlier. Nowadays,
> Agent does its own MAPI calls.
> I'm writing a utility that does xp_smtp_sendmail calls based on your current
> alert configuration. The idea is to configure alerts as desired, and then
> schedule my .exe every x minutes. My .exe reads off of the alert config
> table and reads the eventlog. Based on when each configured alert was fired
> the last time, the exe will xp_smtp_sendmail. The current idea is to use
> disabled operators (if you enable them, then Agent will complain that it
> can't send email).
> I'm finishing up this util this week, then I have to wait for it to go on
> the web (which might take a few days to a few weeks - I don't have direct
> access to the web). Send me an email if you want to give it a try earlier.
> I will put it on www.dbmaint.com, free utilities.
> --
> Tibor Karaszi
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> > Thanks Aaron (and Sue)!
> >
> > Out of curiosity, do you know whether the Notifications is making a SQL
> call to
> > xp_sendmail? If so, would it be possible to "swap out" the underlying
> xp_sendmail .DLL
> > and replace it with a xp_smtp_sendmail "wrapper" with the same name as
> xp_sendmail?
> >
> > I doubt that the Notifications is making a SQL call to "do its business",
> but I thought
> > I'd check. :-)
> >
> > Thanks!
> >
> > John Peterson
> >
> >
> > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > No, it's a bit more of a PITA than that, but at the same time, it gives
> you
> > > a lot more control. We use this mechanism and we have customized
> > > subjects/bodies, inclusion lists based on various error levels, etc.
> > >
> > > A
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > Thanks, Sue!
> > > >
> > > > I had hoped there was a way to integrate the existing Operator aspect,
> but
> > > introduce a new
> > > > transport mechanism. It sounds like it won't be that straightforward.
> > > :-(
> > >
> > >
> >
> >
>|||Great! Send me an email, John (remove the obvious parts), and I can send you
the bits when I'm done with it.
--
Tibor Karaszi
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23PfBOLmoDHA.3320@.tk2msftngp13.phx.gbl...
> Hello, Tibor! That sounds almost *exactly* what I'd be pining for! If
you don't mind an
> early beta tester, I'd be delighted to try it out earlier! :-)
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in
> message news:QYApb.36350$dP1.121349@.newsc.telia.net...
> > Alerts does not call xp_sendmail as it did in 6.5 and earlier. Nowadays,
> > Agent does its own MAPI calls.
> > I'm writing a utility that does xp_smtp_sendmail calls based on your
current
> > alert configuration. The idea is to configure alerts as desired, and
then
> > schedule my .exe every x minutes. My .exe reads off of the alert config
> > table and reads the eventlog. Based on when each configured alert was
fired
> > the last time, the exe will xp_smtp_sendmail. The current idea is to use
> > disabled operators (if you enable them, then Agent will complain that it
> > can't send email).
> > I'm finishing up this util this week, then I have to wait for it to go
on
> > the web (which might take a few days to a few weeks - I don't have
direct
> > access to the web). Send me an email if you want to give it a try
earlier.
> > I will put it on www.dbmaint.com, free utilities.
> > --
> > Tibor Karaszi
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> > > Thanks Aaron (and Sue)!
> > >
> > > Out of curiosity, do you know whether the Notifications is making a
SQL
> > call to
> > > xp_sendmail? If so, would it be possible to "swap out" the underlying
> > xp_sendmail .DLL
> > > and replace it with a xp_smtp_sendmail "wrapper" with the same name as
> > xp_sendmail?
> > >
> > > I doubt that the Notifications is making a SQL call to "do its
business",
> > but I thought
> > > I'd check. :-)
> > >
> > > Thanks!
> > >
> > > John Peterson
> > >
> > >
> > > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > > news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > > No, it's a bit more of a PITA than that, but at the same time, it
gives
> > you
> > > > a lot more control. We use this mechanism and we have customized
> > > > subjects/bodies, inclusion lists based on various error levels, etc.
> > > >
> > > > A
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > > Thanks, Sue!
> > > > >
> > > > > I had hoped there was a way to integrate the existing Operator
aspect,
> > but
> > > > introduce a new
> > > > > transport mechanism. It sounds like it won't be that
straightforward.
> > > > :-(
> > > >
> > > >
> > >
> > >
> >
> >
>|||Good thinkin' Tibor.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ZZJpb.36406$dP1.121601@.newsc.telia.net...
> Great! Send me an email, John (remove the obvious parts), and I can send
you
> the bits when I'm done with it.
> --
> Tibor Karaszi
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23PfBOLmoDHA.3320@.tk2msftngp13.phx.gbl...
> > Hello, Tibor! That sounds almost *exactly* what I'd be pining for! If
> you don't mind an
> > early beta tester, I'd be delighted to try it out earlier! :-)
> >
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in
> > message news:QYApb.36350$dP1.121349@.newsc.telia.net...
> > > Alerts does not call xp_sendmail as it did in 6.5 and earlier.
Nowadays,
> > > Agent does its own MAPI calls.
> > > I'm writing a utility that does xp_smtp_sendmail calls based on your
> current
> > > alert configuration. The idea is to configure alerts as desired, and
> then
> > > schedule my .exe every x minutes. My .exe reads off of the alert
config
> > > table and reads the eventlog. Based on when each configured alert was
> fired
> > > the last time, the exe will xp_smtp_sendmail. The current idea is to
use
> > > disabled operators (if you enable them, then Agent will complain that
it
> > > can't send email).
> > > I'm finishing up this util this week, then I have to wait for it to go
> on
> > > the web (which might take a few days to a few weeks - I don't have
> direct
> > > access to the web). Send me an email if you want to give it a try
> earlier.
> > > I will put it on www.dbmaint.com, free utilities.
> > > --
> > > Tibor Karaszi
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > Thanks Aaron (and Sue)!
> > > >
> > > > Out of curiosity, do you know whether the Notifications is making a
> SQL
> > > call to
> > > > xp_sendmail? If so, would it be possible to "swap out" the
underlying
> > > xp_sendmail .DLL
> > > > and replace it with a xp_smtp_sendmail "wrapper" with the same name
as
> > > xp_sendmail?
> > > >
> > > > I doubt that the Notifications is making a SQL call to "do its
> business",
> > > but I thought
> > > > I'd check. :-)
> > > >
> > > > Thanks!
> > > >
> > > > John Peterson
> > > >
> > > >
> > > > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > > > news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > > > No, it's a bit more of a PITA than that, but at the same time, it
> gives
> > > you
> > > > > a lot more control. We use this mechanism and we have customized
> > > > > subjects/bodies, inclusion lists based on various error levels,
etc.
> > > > >
> > > > > A
> > > > >
> > > > >
> > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > > > Thanks, Sue!
> > > > > >
> > > > > > I had hoped there was a way to integrate the existing Operator
> aspect,
> > > but
> > > > > introduce a new
> > > > > > transport mechanism. It sounds like it won't be that
> straightforward.
> > > > > :-(
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks John. :-)
Perhaps I should say that anyone who like to look at this and expect to
install it to provide some feedback are welcome to drop me an email.
(It'll be a freebie when released, so unless you expect to install and play,
you might as well wait a couple of weeks).
I'll post something here when it is on the web.
--
Tibor Karaszi
"John Sitka" <johnsitka@.REMOVEhotmail.com> wrote in message
news:e3eTqjtoDHA.964@.TK2MSFTNGP10.phx.gbl...
> Good thinkin' Tibor.
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ZZJpb.36406$dP1.121601@.newsc.telia.net...
> > Great! Send me an email, John (remove the obvious parts), and I can send
> you
> > the bits when I'm done with it.
> >
> > --
> > Tibor Karaszi
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:%23PfBOLmoDHA.3320@.tk2msftngp13.phx.gbl...
> > > Hello, Tibor! That sounds almost *exactly* what I'd be pining for!
If
> > you don't mind an
> > > early beta tester, I'd be delighted to try it out earlier! :-)
> > >
> > >
> > > "Tibor Karaszi"
> > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in
> > > message news:QYApb.36350$dP1.121349@.newsc.telia.net...
> > > > Alerts does not call xp_sendmail as it did in 6.5 and earlier.
> Nowadays,
> > > > Agent does its own MAPI calls.
> > > > I'm writing a utility that does xp_smtp_sendmail calls based on your
> > current
> > > > alert configuration. The idea is to configure alerts as desired, and
> > then
> > > > schedule my .exe every x minutes. My .exe reads off of the alert
> config
> > > > table and reads the eventlog. Based on when each configured alert
was
> > fired
> > > > the last time, the exe will xp_smtp_sendmail. The current idea is to
> use
> > > > disabled operators (if you enable them, then Agent will complain
that
> it
> > > > can't send email).
> > > > I'm finishing up this util this week, then I have to wait for it to
go
> > on
> > > > the web (which might take a few days to a few weeks - I don't have
> > direct
> > > > access to the web). Send me an email if you want to give it a try
> > earlier.
> > > > I will put it on www.dbmaint.com, free utilities.
> > > > --
> > > > Tibor Karaszi
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > > Thanks Aaron (and Sue)!
> > > > >
> > > > > Out of curiosity, do you know whether the Notifications is making
a
> > SQL
> > > > call to
> > > > > xp_sendmail? If so, would it be possible to "swap out" the
> underlying
> > > > xp_sendmail .DLL
> > > > > and replace it with a xp_smtp_sendmail "wrapper" with the same
name
> as
> > > > xp_sendmail?
> > > > >
> > > > > I doubt that the Notifications is making a SQL call to "do its
> > business",
> > > > but I thought
> > > > > I'd check. :-)
> > > > >
> > > > > Thanks!
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > > > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > > > > news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > > > > No, it's a bit more of a PITA than that, but at the same time,
it
> > gives
> > > > you
> > > > > > a lot more control. We use this mechanism and we have
customized
> > > > > > subjects/bodies, inclusion lists based on various error levels,
> etc.
> > > > > >
> > > > > > A
> > > > > >
> > > > > >
> > > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > > > > Thanks, Sue!
> > > > > > >
> > > > > > > I had hoped there was a way to integrate the existing Operator
> > aspect,
> > > > but
> > > > > > introduce a new
> > > > > > > transport mechanism. It sounds like it won't be that
> > straightforward.
> > > > > > :-(
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

No comments:

Post a Comment