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 informationHow 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' |
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) |
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) |
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 = '//www.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!