SQL Server Agent Job Custom Notifications

Category: sql server servicebroker


scott_m on Mon, 04 Apr 2016 19:57:30

Is there a good way to customize sql agent notifications.  Not a fan of email or pager notifications.  More specifically, we use service broker and hipchat alot.  Would be nice if Service Broker could subscribe to the notifications.  If that is somehow possible we could send a corresponding message to hipchat.



Riaon on Tue, 05 Apr 2016 09:55:21

You mean to create a schedule job as Send in SQL Server Agent?

scott_m on Wed, 06 Apr 2016 01:06:19

On the SQL Server Agent Operator Page, you have options for email, netsend, or pager.  Nothing for custom notifications.  We want to send notifications to service broker or hipchat.

un6o6 on Fri, 16 Dec 2016 20:45:34

I agree this would be a nice convenience.  I was thinking about the same thing today (which is why I just upvoted the base post).

That said (and the OP probably knows this, too), the workaround is pretty straightforward.  If you're using a SQL Server Agent job, you can add job steps to be executed on the success or failure of a particular previous step.  And these job steps can do just about anything[1].  You could add a PowerShell job step and use this PowerShell module[2] to send a HipChat message.

In terms of SQL Server implementing a broader set of built-in notification mechanisms. . .an XMPP interface would give you access not only to HipChat, but a bunch of other messaging services[3].  Another possibility that comes to mind is message queuing.  SQL Server natively supports MSMQ.  Gateways exists to connect MSMQ to other message queuing services (although I don't see one that goes directly from MSMQ to XMPP, you can get there in two hops using the open source Apache queuing services (theoretically - I've never tried it myself and who knows what complication may arise)).

[1] htt ps :/ / msdn.microsoft. com/en-us/library/ms187056.aspx
[2] htt ps :/ /github. com/lholman/hipchat-ps
[3] htt ps :/ /en.wikipedia. org/wiki/XMPP#Implementations</o:p>