SQL Server - How to integrate database with Slack and send messages using Python and sp_execute_external_script

Views: 1.264
Reading Time: 7 minutes

Faaaaala guys !!
Today I am very excited about this post. My first using Python on SQL Server 2017, and you can expect a lot more. On February 04 of 2017, I had released the post. SQL Server - How to Integrate Database with Slack and Send Messages Using CLR (C #) And this time I'll be back here to show you how to do this same integration using only native SQL Server 2017 features.

Introduction

As you may know, SQL 2017 brought a series of news and new features One of our daily tasks, and one of them, was the ability to run scripts written in Python by SQL Server, both for statistical analysis (widely used by data scientists and statisticians), and for performing everyday tasks.

I don't know Slack

View more information

What is Slack?

For those who do not know, the Slack is one of the most famous and widely used corporate communicators in the world, Ryver's main competitor, and is proposing to replace email communication with private and public (in-team) groups focused on particular teams and / or issues. You can create public groups where your entire team can interact and collaborate, and private groups for each industry where only those in the group can view messages.

Slack allows you to reply to emails from your own tool, send and share files, send private messages, install multiple plugins to add new features and more.

In the free plan, message fetching retrieves only the latest 10 thousand messages, the storage limit is 5 GB and some more limitations you can check on this link.

Where and how can I use Slack in my business?

This feature is especially useful for building teams in your company and generating alerts and monitoring by team, where each team receives alerts only for their activities. In addition, you can use open groups to send information and notifications that matter to the entire company. All at no cost and you can receive by email, view on the web and on your smartphone.

Another cool way to use Slack is to send sales target tracking to the sales department so that regional and local managers can track sales team performance in real time without having to consult and open up. reports wherever they are, even outside the company.

If your company adopts Slack, HR can use the service to send messages and information to employees. Send Christmas messages, a special message on the employee's birthday, etc.

Anyway, the advantage of Slack is that it can be sent in many different ways (Email, Web, Smartphone) wherever you are, inside or outside the company and it represents a big revolution in the way people interact with The informations.

I use Slack where I work as a tool for communication, monitoring and alerts of IT routines, such as critical job failures, backup problems, database corruption, slow queries, locks, etc. Whenever a critical routine fails, an internal email and notification via Slack are sent, where a push notification arrives on my mobile phone (just like Whatsapp) and wherever I am, I become aware of that failure.

How to add users to Slack

The first step in building the integration is to create your Slack account, accessing this link. Once created, you will receive an email to log in to your account.

Slack splash screen, with some groups I created for this post

To add new users, just click on the menu (Your name) and select the option “Invite people”

You can add your contacts from your Google account, import multiple contacts in CSV format and more. Once you invite others in, they will receive a link to join your team and after they finish signing up, they will be able to view the data through their web browser or mobile app.

How to enable Python on SQL Server 2017

To enable Python on SQL Server 2017 you will need to enable the external scripts enabled, with the command below:

Result:
Configuration option 'external scripts enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

Once executed, you will need to restart the SQL Server 2017 service in Configuration Manager:

Now just restart the service, as the image below:

I also recommend restarting the "SQL Server Launchpad" service, since when I tried to run the Python script, I received this error message:

Msg 39011, Level 16, State 1, Line 10
SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.

How to install Slacker library

For our SQL Server integration with Slack, let's use the Slacker library. For your installation, open Command Prompt as Administrator:

Once opened, simply enter the command below:

If you do not know the path of your installation, you can easily find out with this command here:

Result:

After entering the above commands, the library will have been successfully installed:

How to enable Slack API

In order to be able to send messages through Slack, we need to create a custom integration. To do this go to the URL https://api.slack.com/docs/oauth-test-tokens logged in to the user account that will serve as BOT and will send the automatic messages.

Click the "Create Token" button.

Copy the generated token to your application. It is with this huge code (76 characters in my case) that you will be authenticating with the API and will be able to send your messages through the API.

How to send messages in Slack through SQL Server

Now that I have explained what Slack is, we have enabled Python scripts in the instance and you have already generated your access token, we can start sending messages to Slack through SQL Server 2017.

Simple version (format only)

Stored Procedure code that sends messages:

Usage example for private message to another user:

Result:

Usage example for message on a channel:

Result:

Full version (with colors, links and formatting)

Stored Procedure code that sends the message:

Examples of use

Sending Formatted Messages

Sending Messages with External Links
Clicking on the message title will take you to the URL https://www.dirceuresende.com/blog 🙂

Message wrapping with line break

To learn more about Slack formatting, visit this link.

And that's it folks!
I hope you enjoyed this post and see you next time!