If I had the limitations of Express, then personally I would use the CDO SMTP methods of VBScript, called using CScript (Windows Scripting Host), based on the successful / failed outcome of a script. The batch file envelope can be called using EXEC xp_cmdshell('c:\go.bat').
In English...
1. Create a batch file to launch your VBScript script.
go.bat
@echo off
cls
cscript /nologo myscript.vbs
2. Create your script file.
myscript.vbs
' Declare the object
Set Msg = CreateObject("CDO.Message")
' Set SMTP configuration here. You only need to point to your SMTP server and port number.
Msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
Msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.foo.com"
Msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
Msg.Configuration.Fields.Update
' Set e-mail details here.
Msg.Subject = "A problem has occurred with your script."
Msg.From = "mailbox@foo.com"
Msg.To = "you@foo.com"
Msg.TextBody = "Script has failed. Please check error log."
' Send the e-mail.
Msg.Send
3. Amend your SP to use the scripts.
ALTER PROCEDURE myProc (@name VARCHAR(100) )
AS BEGIN
SELECT [database_id] FROM sys.databases WHERE [name] = @name -- example query
END
becomes...
ALTER PROCEDURE myProc (@name VARCHAR(100) )
AS BEGIN
BEGIN TRY
SELECT [database_id] FROM sys.databases WHERE [name] = @name -- example query
END TRY
BEGIN CATCH
PRINT 'Something went wrong!'
EXEC xp_cmdshell 'c:\go.bat'
END CATCH
You can amend the VBScript / batch file scripts to take parameters if you like, e.g. define an error number / message and call go.bat as 'go.bat ' + @errorNumber, then amend go.bat to call the VBScript file with the %1 parameter passed in, so the VBScript script can put it in the subject of the e-mail, perhaps.
I've used this method before and yes, it's very 90s, but works absolutely fine.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.