In this blog you will see how you can easily send a Slack message to an existing channel when a change has been detected in a SharePoint list. This can be useful for approving changes, or simply monitoring lists in SharePoint. Conceptually, a T-SQL script running in SQL Server monitors changes made to a SharePoint List, and when a change is detected the SQL script sends a Slack message to a Channel.
To follow this blog you must first have downloaded and installed the latest version of Enzo Server, and ensure that you can
connect to Enzo using SQL Server Management Studio (SSMS). For further instructions or to download Enzo Server,
please visit please visit the download page.
This blog also assumes you have installed and configured the Slack and SharePoint adapters. See the SharePoint and Slack documentation for information.
Last but not least, we will be using Linked Server to communicate to both Slack and SharePoint from SSMS. As a result, a Linked Server connection is also required to follow this blog. See the Linked Server documentation for more information.
Now that your Slack adapter has been configured, start SQL Server Management Studio (SSMS).
When the login screen appears, enter the information to connect to any SQL Server database. In the example below, I am connecting to my localhost database on an instance called ENZO:
You should now be able to access Enzo through a Linked Server connection. Assuming your Linked Server is created as [localhost,9550], the following SQL command should work:
A list of available commands that can be executed on the Slack adapter will be returned.
EXEC [localhost,9550].bsc.slack.listchannels EXEC [localhost,9550].bsc.sharepoint.lists
SELECT * FROM [localhost,9550].bsc.SharePoint.ListChanges WHERE name='Companies'
DECLARE @cghToken nvarchar(255) DECLARE @tmpToken nvarchar(255) WHILE(1=1) BEGIN SET @tmpToken = ( SELECT TOP 1 changeToken FROM [localhost,9550].bsc.SharePoint.ListChanges WHERE name='Companies' -- the SP List to monitor AND changeToken = @cghToken ) if (LEN(@tmpToken) > 0) BEGIN EXEC [localhost,9550].bsc.Slack.PostMessage 'C011FGD81S4', -- the channel id 'Record changed in Companies list' SET @cghToken = @tmpToken END WAITFOR DELAY '00:00:15' -- wait 15 seconds END
UPDATE Sharepoint.list@Companies SET Opportunities=3 WHERE ID=52
© 2023 - Enzo Unified