Posting SQL Server Notifications to Slack

Problem

You have a business that focuses on customers signing up for your service and placing orders through your website. You’d like to get real time notifications of these events. In this article, we look at how this can be done using SQL Server along with a communication and collaboration tool called Slack.

Solution

When certain events happen in the SQL Server database, we want to send a notification.

To implement this solution, we will create a trigger on the orders and customers table that will trigger a stored procedure that will create a new message in our Slack channel. Below is a diagram of the workflow.

Steps in this Solution

Create the Slack Channel
Enable App Webhook
Create Stored Procedure
Create SQL Server Event Notifications
Run some insert tests
Run some automated tests

Create the Slack Channel

This same logic can be implemented with MS Teams or other webhook supported technologies, but in this example, I will be implementing with Slack. If you do not have a Slack account you can sign up for free here.

Once you have a Slack account, we need to create a channel. Click the + button next to Channels and select Create a channel.

Name your channel and click Create.

Enable App Webhook

Now that we have the channel created, we need to add an “app” to the channel. Click the Add an App link in the newly created channel header:

In the search bar type webhook, the one we want is Incoming WebHook for our channel, click install.

Select the channel you want the notifications to go to, click “Add Incoming WebHooks Integration“.

Take a note of the URL provided on the following screen, this URL is a unique endpoint that points to your particular Slack channel.

Create SQL Server Stored Procedure

Next, we will create a SQL Server stored procedure that will allow us to interface with the webhook for the Slack channel that we just created.

To do this, we will utilize the sp_OACreate stored procedure to instantiate a HTTP connection object, with that object we are able to send HTTP verb requests to web end points. In this case we will be using the post verb to post in a small message to our Slack channel.

I have adapted the method slightly to be abstracted as a stored procedure, but the original code I found via this SO answer. The stored procedure accepts a Rtype parameter (Post, Get, Post, etc.), auth header, a payload (i.e. json data) and a URL. The stored procedure also has 2 output two variables, outstatus which is the HTTP code response from the end point (i.e. 200, 400, 401) and the response text of the request.

CREATE PROC SPX_MAKE_API_REQUEST(@RTYPE VARCHAR(MAX),@authHeader VARCHAR(MAX), @RPAYLOAD VARCHAR(MAX), @URL VARCHAR(MAX),@OUTSTATUS VARCHAR(MAX) OUTPUT,@OUTRESPONSE VARCHAR(MAX) OUTPUT
)
AS
 
DECLARE @contentType NVARCHAR(64);
DECLARE @postData NVARCHAR(2000);
DECLARE @responseText NVARCHAR(2000);
DECLARE @responseXML NVARCHAR(2000);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
SET @contentType = 'application/json';
-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, @RTYPE, @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', 'application/json';
SET @RPAYLOAD = (SELECT CASE WHEN @RTYPE = 'Get' THEN NULL ELSE @RPAYLOAD END )
EXEC @ret = sp_OAMethod @token, 'send', NULL, @RPAYLOAD; -- IF YOUR POSTING, CHANGE THE LAST NULL TO @postData
-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;
-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;
SET @OUTSTATUS = 'Status: ' + @status + ' (' + @statusText + ')'
SET @OUTRESPONSE = 'Response text: ' + @responseText;
-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);

Create SQL Server Event Notifications

Now that we have a stored procedure created, we need to enable a sp_configure option to be able to run the stored procedure.

sp_configure 'show advanced options', 1 
GO 
RECONFIGURE; 
GO 
sp_configure 'Ole Automation Procedures', 1 
GO 
RECONFIGURE;

Once that is complete, we can test our stored procedure to make sure it is working by sending a request to Nasa’s endpoint for tracking the positional coordinates of the international space station. For this we will issue a GET request. As you can see, I just pass ‘xxx’ values for both the auth header and the payload parameters.

DECLARE @OUTSTATUS VARCHAR(MAX),@OUTRESPONSE VARCHAR(MAX)
EXEC SPX_MAKE_API_REQUEST 'GET','XXX','XXX','http://api.open-notify.org/iss-now.json',@OUTSTATUS OUTPUT,@OUTRESPONSE OUTPUT
SELECT @OUTSTATUS AS [RESPONSE CODE],@OUTRESPONSE AS [RESPONSE VALUES]

Now that the stored procedure is verified to be working, let’s create the table structure for this working example.

We will have a Customers table as well as an Orders table with a small amount of data in each table.

CREATE TABLE Customers 
( 
  CustomerId INT IDENTITY(1,1), 
  [Name] VARCHAR(MAX), 
  [Address] VARCHAR(MAX), 
  [Birthdate] Date, 
  CONSTRAINT PK_Customers_CustomerId PRIMARY KEY CLUSTERED (CustomerId) 
) 
CREATE TABLE Orders 
( 
  OrderId INT IDENTITY(1,1), 
  OrderDate DATETIME, 
  Amount Money, 
  Description VARCHAR(MAX), 
  CustomerId INT, 
  CONSTRAINT FK_CustomerId FOREIGN KEY (CustomerId) 
    REFERENCES MSSQL_EVENTNOTIFICATIONS.dbo.Customers (CustomerId) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE 
)

Next, we create a trigger on each table, make sure to replace the 4^th parameter in the Make_Api_Request stored procedure with your unique Slack URL:

CREATE TRIGGER NewCustomerAlert 
ON dbo.Customers 
AFTER INSERT 
AS 
DECLARE @OUTSTATUS VARCHAR(MAX),@OUTRESPONSE VARCHAR(MAX),@POSTDATA VARCHAR(MAX), @CUSTNAME VARCHAR(MAX) 
SET @CUSTNAME = (SELECT Name FROM INSERTED) 
SET @POSTDATA = '{"text":"A new customer has created a user account : ' + @CUSTNAME +'"}' 
EXEC SPX_MAKE_API_REQUEST 'POST','',@POSTDATA,'https://hooks.slack.com/services/{YOUR UNIQUE URL}',@OUTSTATUS OUTPUT,@OUTRESPONSE OUTPUT 
SELECT @OUTSTATUS AS [RESPONSE CODE],@OUTRESPONSE AS [RESPONSE CODE]

In this example we grab the customer name from the inserted data and use that value in the post request back to our Slack channel.

Lastly, we create a database trigger for the orders table, this trigger is a little more sophisticated than the previous one. This trigger will send a dynamic message to the Slack channel based on the order amount of the inserted data.

CREATE TRIGGER NewOrderAlert
ON dbo.Orders
AFTER INSERT
AS  
 
DECLARE @OUTSTATUS VARCHAR(MAX),@OUTRESPONSE VARCHAR(MAX),@POSTDATA VARCHAR(MAX), @CUSTNAME VARCHAR(MAX),@ORDERAMT MONEY
SELECT @ORDERAMT = (SELECT amount FROM INSERTED)
 
SET @POSTDATA = (SELECT CASE WHEN @ORDERAMT < 50 THEN '{"text":"A : Small order has been placed"}' 
                             WHEN @ORDERAMT >= 51 AND @ORDERAMT <= 200 THEN '{"text":"A Medium order has been placed!"}' 
                      WHEN @ORDERAMT >= 51 AND @ORDERAMT >= 201 THEN '{"text":"A Large order has been placed!"}' 
                      END)
 
EXEC SPX_MAKE_API_REQUEST 'POST','',@POSTDATA, 'https://hooks.slack.com/services/{YOUR UNIQUE URL}',@OUTSTATUS OUTPUT,@OUTRESPONSE OUTPUT
SELECT @OUTSTATUS AS [RESPONSE CODE],@OUTRESPONSE AS [RESPONSE CODE]
 
GO

Run some test inserts

Now that we have our table structure and database triggers configured, let’s insert some test data into our tables.

--CREATE NEW CUSTOMERS AND ORDERS TO TEST THE DATABASE TRIGGERS AND STORED PROCEDURE.
INSERT INTO Customers
VALUES('ALAN T','611 Linda Dr','4/1/1975')
 
INSERT INTO Customers
VALUES('JIM B','992 Carpenter Court','11/14/1991')
 
INSERT INTO Customers
VALUES('WYATT E','9317 Boston Rd','12/31/1960')
 
INSERT INTO Customers
VALUES('Test Case','178 Stall Dr','1/1/2020')
 
INSERT INTO Orders
VALUES('8/1/2020','35.49','A few small items...',1)
 
INSERT INTO Orders
VALUES('8/14/2020','85.99','A ton of small items...',2)
 
INSERT INTO Orders
VALUES('8/14/2020','500.00','One really Big Item!',3)

After each insert into each table you should see messages populate in your Slack channel.

Run some automated tests

Lastly let’s create a while loop to create random order amounts and dates to be inserted into our new tables and monitor the Slack channel for notifications.

--CREATE 5 RANDOM ORDERS BY CUSTOMER ID 4, MONITOR FOR ALERTS IN SLACK.
WHILE (@I < 5)
BEGIN
   SET @M = (SELECT ROUND(RAND() * 12, 0))
   SET @D = (SELECT ROUND(RAND() * 28, 0))
   SET @Y = (SELECT 2020)
   SET @DT = (SELECT CAST(@M AS VARCHAR(2)) + '/' + CAST(@D AS VARCHAR(2)) + '/' + CAST(@Y AS VARCHAR(4)))
   SET @AMT = (SELECT 1 + ROUND(RAND() * (600 + 1 - 1), 0))
 
   INSERT INTO Orders
   VALUES(@DT,@AMT,'Random',4)
 
   SET @I = @I + 1
   WAITFOR DELAY '00:00:05'
END

Summary

That’s it. Now we have a real time notification component to a few tables in our database.

To recap we created a Slack channel, added a web hook integration used the URL for that webhook in a stored procedure that we created. That stored procedure created a HTTP session and posted data from a database table trigger. These messages were simple and informative and nature, but more complex implementations can be created see this link for more details on message formatting.

Next Steps

Check out these additional MSSQLTips trigger articles:
Experiment with the SPX_MAKE_API_REQUEST stored procedure, try different verb types and settings such as using auth headers
Try and implement this technique to give extra visibility to database events that you care about
Take a look at this article too: Receive SQL Server Alerts through Slack

Resources

Community

Subscribe