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 informationWhat 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:
| 1 2 3 4 5 | EXEC sp_configure 'external scripts enabled', 1 GO RECONFIGURE WITH OVERRIDE; GO |
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:
| 1 2 | cd "C:\Program Files\Microsoft SQL Server\MSSQL14.NOME_DA_SUA_INSTANCIA\PYTHON_SERVICES\Scripts" pip install slacker |
If you do not know the path of your installation, you can easily find out with this command here:
| 1 2 3 4 5 | SELECT TOP (1) TRIM(REPLACE(B.[value], 'MSSQL\Binn\sqlservr.exe', '')) + 'PYTHON_SERVICES\Scripts' FROM sys.dm_server_registry A CROSS APPLY STRING_SPLIT(REPLACE(CAST(value_data AS VARCHAR(MAX)), '"', ''), '-') B WHERE A.registry_key LIKE 'HKLM\SYSTEM\CurrentControlSet\Services\MSSQL%' AND A.value_name = 'ImagePath' |
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:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | USE [dirceuresende] GO CREATE OR ALTER PROCEDURE dbo.stpEnvia_Mensagem_Slack ( @Ds_Mensagem NVARCHAR(4000), @Ds_Canal NVARCHAR(256) ) AS BEGIN DECLARE @SlackToken NVARCHAR(100) = 'seu token aqui' -- https://api.slack.com/docs/oauth-test-tokens DECLARE @Script NVARCHAR(MAX) = ' from slacker import Slacker slack = Slacker("' + @SlackToken + '") slack.chat.post_message(channel, text)' EXEC sys.sp_execute_external_script @language = N'Python', @script = @Script, @params = N'@channel nvarchar(256), @text nvarchar(max)', @channel = @Ds_Canal, @text = @Ds_Mensagem END |
Usage example for private message to another user:
| 1 2 3 | EXEC dbo.stpEnvia_Mensagem_Slack @Ds_Mensagem = N'Teste de integração', -- nvarchar(max) @Ds_Canal = N'@bot_dirceuresende' -- nvarchar(128) |
Result:
Usage example for message on a channel:
| 1 2 3 | EXEC dbo.stpEnvia_Mensagem_Slack @Ds_Mensagem = N'Teste de integração', -- nvarchar(max) @Ds_Canal = N'#ti' -- nvarchar(128) |
Result:
Full version (with colors, links and formatting)
Stored Procedure code that sends the message:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | USE [dirceuresende] GO CREATE OR ALTER PROCEDURE dbo.stpEnvia_Mensagem_Slack_Completa ( @Ds_Mensagem NVARCHAR(4000), @Ds_Canal NVARCHAR(256), @Ds_Cor_Hexa NVARCHAR(10) = '', @Ds_Titulo NVARCHAR(128) = '', @Ds_Link NVARCHAR(256) = '', @Ds_Mensagem_Interna NVARCHAR(4000) = '' ) AS BEGIN DECLARE @SlackToken NVARCHAR(100) = 'seu token aqui', -- https://api.slack.com/docs/oauth-test-tokens @ConteudoTexto NVARCHAR(MAX) = '[ { "fallback": "' + @Ds_Mensagem + '", "color": "#' + @Ds_Cor_Hexa + '", "pretext": "' + @Ds_Mensagem + '", "title": "' + @Ds_Titulo + '", "title_link": "' + @Ds_Link + '", "text": "' + @Ds_Mensagem_Interna + '", "mrkdwn_in": ["text", "pretext"] } ]' DECLARE @Script NVARCHAR(MAX) = ' from slacker import Slacker slack = Slacker("' + @SlackToken + '") slack.chat.post_message(channel, text, username, as_user, parse, link_names, attachments, unfurl_links, unfurl_media, icon_url, icon_emoji, thread_ts)' EXEC sys.sp_execute_external_script @language = N'Python', @script = @Script, @params = N'@channel nvarchar(256), @text nvarchar(max), @username nvarchar(128), @as_user nvarchar(10), @parse nvarchar(100), @link_names nvarchar(10), @attachments nvarchar(max), @unfurl_links nvarchar(10), @unfurl_media nvarchar(10), @icon_url nvarchar(255), @icon_emoji nvarchar(100), @thread_ts nvarchar(100)', @channel = @Ds_Canal, @text = @Ds_Mensagem, @as_user = 'false', @attachments = @ConteudoTexto, @icon_emoji = '', @icon_url = '//dirceuresende.com/wp-content/uploads/2018/01/cropped-Azure-SQL-Database-generic_COLOR-2-180x180.png', @link_names = 'true', @parse = 'none', @thread_ts = '', @unfurl_links = 'true', @unfurl_media = 'true', @username = 'BOT Dirceu Resende' END |
Examples of use
Sending Formatted Messages
| 1 2 3 4 5 6 7 | EXEC dbo.stpEnvia_Mensagem_Slack_Completa @Ds_Canal = N'informativos', -- nvarchar(max) @Ds_Mensagem = N'Teste de Mensagem', -- nvarchar(max) @Ds_Cor_Hexa = N'ff0000', -- nvarchar(max) @Ds_Titulo = N'Título da Mensagem com Borda Vermelha', -- nvarchar(max) @Ds_Link = N'', -- nvarchar(max) @Ds_Mensagem_Interna = N'Essa é a mensagem do Slack. Aceita *negrito*, _italico_ e ~sublinhado~' -- nvarchar(max) |
Sending Messages with External Links
Clicking on the message title will take you to the URL https://www.dirceuresende.com/blog 🙂
| 1 2 3 4 5 6 7 | EXEC dbo.stpEnvia_Mensagem_Slack_Completa @Ds_Canal = N'informativos', -- nvarchar(max) @Ds_Mensagem = N'', -- nvarchar(max) @Ds_Cor_Hexa = N'00ff00', -- nvarchar(max) @Ds_Titulo = N'Título da Mensagem com Borda Verde', -- nvarchar(max) @Ds_Link = N'https://www.dirceuresende.com/blog', -- nvarchar(max) @Ds_Mensagem_Interna = N'Essa é a mensagem do Slack. Aceita *negrito*, _italico_ e ~sublinhado~' -- nvarchar(max) |
Message wrapping with line break
| 1 2 3 4 5 6 7 | EXEC dbo.stpEnvia_Mensagem_Slack_Completa @Ds_Canal = N'informativos', -- nvarchar(max) @Ds_Mensagem = N'', -- nvarchar(max) @Ds_Cor_Hexa = N'0000ff', -- nvarchar(max) @Ds_Titulo = N'', -- nvarchar(max) @Ds_Link = N'', -- nvarchar(max) @Ds_Mensagem_Interna = N'Essa é a mensagem do Slack com Borda Azul.\n\nAceita *negrito*\nAceita_italico_\nAceita ~sublinhado~' -- nvarchar(max) |
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!
Good !!! #ripCLR
top d +.
The best communicator I know!
Another excellent post. Strong hug
Excellent post my friend! I will use it in my alerts for SQL Server. Big hug!