Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, I will be explaining how to send the notifications we obtain while managing SQL Server to Telegram.
First of all, of course, the first thing we need to do is to have a telegram account.
I would also like to give basic information about Telegram.
It is a WhatsApp-style messaging platform that we all use, and it has different features that distinguish both products from each other.
We may need WhatsApp business or different middleware to send notifications from WhatsApp, which we will explain in the subject.
After sharing information about Telegram, what we need to do is to obtain a token and a chat_id to send notifications in Telegram.
To do this, we log in to “web.telegram.org”.
We write @BotFather in the Search section and start the conversation with start.
As seen in the picture above, we can actually get support for many contents with @BotFather.
What we want to do is create a new bot.
For this we write “/newbot”.
It first asks us for a name for the bot, and then a username for the bot.
The important thing to note in Picture 3 is that the username information must end with _bot, and the same picture gives you the HTTP API information you will need to use this bot. Don’t forget to take note of this information!
Next is to get Chat_id information.
To do this, we call the username we gave in picture-2 and say hello. (We search and find it and say start.)
After saying Hello to the bot we created, all we need to do is edit and open the address below in a new browser tab.
1 | https://api.telegram.org/bot<HTTPTOKENAPI>/getUpdates |
Here we replace the part that says <HTTPTOKENAPI> with the HTTPAPI information we see in Picture 3.
When we continue the process, a screen like the one below appears.
We have completed what needs to be done on Telegram, now we have Token information and Chat_id information.
Using these two information, we will make an http post request in SQL Server and send our notification.
For this process, we first need to enable the use of Ole objects on the SQL Server side.
Let’s do this with the code block below.
1 2 3 4 | EXEC sp_configure ‘Ole Automation Procedures’, 1; GO RECONFIGURE; GO |
Now it’s time for the query to make the notification. There are two parts in the query that need to be changed.
We will enter the Token and Chat_id information we have, write the notification we want and run the query. (You must fill in the @TelegramToken and @TelegramChatId information!)
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 | DECLARE @responseText NVARCHAR(2000); DECLARE @responseXML NVARCHAR(2000); DECLARE @ret INT; DECLARE @status NVARCHAR(32); DECLARE @statusText NVARCHAR(32); DECLARE @token INT; DECLARE @url NVARCHAR(256); declare @TelegramToken NVARCHAR(256); declare @TelegramChatId NVARCHAR(32) declare @TelegramMesaj NVARCHAR(32) set @TelegramToken =” set @TelegramChatId = ” set @TelegramMesaj = ‘Çağlar Özenç – DMC Bilgi Teknolojileri ‘ SET @url = ‘https://api.telegram.org/bot’ + @TelegramToken +’/sendMessage?chat_id=’+@TelegramChatId+’&parse_mode=Markdown&text=CAGLAROZENCSQLServer-DMC’; — Open the connection. EXEC @ret = sp_OACreate ‘MSXML2.ServerXMLHTTP’, @token OUT; IF @ret <> 0 RAISERROR(‘Unable to open HTTP connection.’, 10, 1); — Send the request. EXEC @ret = sp_OAMethod @token, ‘open’, NULL, ‘POST’, @url, ‘false’; EXEC @ret = sp_OAMethod @token, ‘setRequestHeader’, NULL, ‘Authentication’, null; EXEC @ret = sp_OAMethod @token, ‘setRequestHeader’, NULL, ‘Content-type’, null; EXEC @ret = sp_OAMethod @token, ‘send’, NULL, null; — Handle the response. EXEC @ret = sp_OAGetProperty @token, ‘status’, @status OUT; EXEC @ret = sp_OAGetProperty @token, ‘statusText’, @statusText OUT; EXEC @ret = sp_OAGetProperty @token, ‘responseText’, @responseText OUT; — Show the response. PRINT ‘Status: ‘ + @status + ‘ (‘ + @statusText + ‘)’; PRINT ‘Response text: ‘ + @responseText; — Close the connection. EXEC @ret = sp_OADestroy @token; IF @ret <> 0 RAISERROR(‘Unable to close HTTP connection.’, 10, 1); |
If your transaction is successful after the query, the information you provided will be sent to Telegram.
By using this method, you can quickly get information about the developing situations in the SQL Server Database Servers under your management.
With different improvements, you can send notifications to telegram groups and instantly inform many people other than yourself.
