This is part 2 of a set of articles Part 1 which details the setting up of the Slack Integration can be found SQL Server and sending messages to Slack. ( or how I stopped worrying about emails) Part 1
Preparing The Slack Integration with Azure Data Studio (ADS)
To build a simple Hello World application using ADS the python package slackclient needs to be installed to your Python Environment. if you’ve not installed a package to ADS It is quite simple. Having chosen the Kernel to use a Python 3, there should be a option on the top of the page to Manage Packages
By clicking this the menu option allows you to “Add New”, in the search pip package enter slackclient
clicking install starts a process in your tasks window to install the slackclient. If you are doing this for the first time it would be useful to also install sqlmlutils at this point. Using this method of loading the python packages is only loading the package to your local environment. if you want to load slackclient to your SQL Server to run the later SQL based scripts you need to run a python script which will require sqlmlutils package loaded locally.
Testing the Slack integration
The code to send a simple message to Slack is 3 lines of code in python.
import slack #initialize client with the token. client = slack.WebClient(token = "<insert token here>" ) #Send the message to Slack client.chat_postMessage(channel="#bot-integration", text= "Hello World")
the command import slack references the python package we installed in the earlier step.
Creating the client object is done by loading the slack token taken (in this case redacted) from the above picture during the slack set up. This identifies which slack integration to send to.
chat_postMessage command in it’s simplest format requires a Channel Name to send to and a Message String. in the previous article I set up the integration in the #bot-integration channel. It is possible to capture the return variables from this function which can provide useful information such as the thread id of the message. this can be used to update the message later if required. and the Channel ID (Note: Channel Name is friendly identifier ChannelID is a unique id the integration uses required for certain operations)
This is where this just works or there is some debugging needed. Some of the issues i’ve faced at this point are typos missing quotes, The channel name not being spelt correctly, or the firewall blocking sending the message. I have once had the issue the slackclient package was incorrectly loaded and I managed to uninstall and reinstall the package. hopefully this will just work for you.
Not what I promised you?
Okay agreed, so far this is a 3 line script running in Azure Data Studio. it should be giving some idea of what is possible, but this is not SQL Server, but I promise this is coming up shortly. This however is a good place to extend on the properties of the chat_PostMessage and chat_Update functions.
This is a python function to send a message to Slack. in it’s simplest form you only need to use a channel name and a Message string as shown above. When this function completes it returns a string of information to let you know if it returned correctly but also some additional useful data.
thread_ts: as a Return type this is a reference to the Message that was posted.
ChannelID: This is the ChannelID (not the Channel name)
Using the thread_ts you can pass this into the chat_PostMessage function with the reference ts to post under the original thread, This can be done multiple times.
import slack #initialize client with the token. client = slack.WebClient(token = "<insert token here>" ) MessageReturn = client.chat_postMessage(channel = '#bot-integration', test="Hi") thread_ts = MessageReturn["ts"] #Send message in a conversation of original post. client.chat_postMessage(channel="#bot-integration" test="goodbye", ts= thread_ts) # in Slack Message will appear as # Hi # goodbye
The ChannelId return parameter is what Slack uses to name then Channel if you are just posting message this is not required but if you want to update the message you will need both the ChannelId and the Thread_ts.
if you have captured the ts Id and the ChannelID (channel in MessageReturn) you can update the message.
import slack #initialize client with the token. client = slack.WebClient(token = "<insert token here>" ) MessageReturn = client.chat_postMessage(channel = '#bot-integration', test="Hi") thread_ts = MessageReturn["ts"] channel_id = MessageReturn["channel"] client.chat_update(channel=Channel_id, Text="Hello", ts=thread_ts) #Final output will be # Hello
The above code changes the original posted message of Hi to Hello.
The code above is a good place to stop and play. These 2 functions are the base of my SQL Servers procedure calls. These will be shown in Part 3 of this article