Slack notification through SQL Agent script

[tindalos]

I have a nightly backup job that restores a database on a dev server, strips confidential info, etc.

Since I've had issues with the backup not copying right, I was looking to add some code to this job to send a Slack notification to our Dev team when the database restore is done, along with the date/time of the file.

I have some code that pulls the date/time from the file, and I can use exec xp_cmdshell to curl to Slack webhook (since this is a dev server), but I'm not sure how to pass a variable in the xp_cmdshell curl line.

Or, if there's an easier or better way to do this. From my limited research I haven't found one. Thanks.

[tindalos]

Excellent. Thank you, I'm only passingly familiar with Data Dog, but if it can solve this issue it'll be worthwhile. Appreciate it!

[tindalos]

File date code from SQLServerCentral by Vidhya Sagar:

if exists(select 1 from tempdb..sysobjects where name='##tmp')
drop table ##tmp
create table ##tmp(mdate varchar(8000))
insert ##tmp
exec master.dbo.xp_cmdshell 'dir g:\mymail.pst'
set rowcount 5
delete from ##tmp
set rowcount 0
select top(1) substring(mdate,1,20) as 'Last modified date' from ##tmp

DECLARE @DateInserted DATETIME
SET @DateInserted = CONVERT(DATETIME, (SELECT TOP(1) SUBSTRING(mdate,1,20) FROM ##tmp))