3

I am trying to get the notifications for a job out of my email and into a chat client(HipChat). So far my googling has been less than successful so I will put the question to the community: Is there a way to send notifications from SQL Server to an API?

My first thought is that I need to add an additional job step that would be a powershell routine to execute the api call.

2

So you need SQL Server to make an HTTP call. There's nothing in T-SQL to do this directly. You will need to engage one of the general-purpose programming embeddings it supports.

Your own suggestion of calling PowerShell from a new job step will work. If there are parameters to pass from the job step that does the work to the one that makes the HTTP call this becomes a little awkward but there are ways to achieve it.

Since SQL Server 2005 stored procedures can be written in any .Net language. The code can implement anything any other .Net program can, including HTTP calls. There are a few things to be wary of, though. It is possible to write code which can crash SQL Server itself. An HTTP request can take a long time to complete. If performed synchronously this will hold open the current transaction and any active locks. This can affect concurrency badly. It may be better to use SQL CLR to write to a queue which then asynchronously calls the API.

More recently support has been added for code written in Python, R and Java. Any of these should be able to make HTTP requests.

For the truly brave COM objects can be instantiated directly from T-SQL using the sp_OA* procedures. Here's an implemetation. It has all the ease of assembly combined with the clarity of linear B. I've used this once in my career. That was enough.

For my two cent's - if the use-case is a simple "this job has ended" I'd go with the job step / PowerShell combination.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.