SQL Server Database Notifications

Oftentimes I am stuck wondering if a database query has completed, or a certain amount of progress on a multi-step query has been made.  I can always backtrack after the query has completed by adding print statements to my lengthy batch files to see how each section performs:

print 'Step 1 complete... ' + convert(varchar(255),getdate())

… however, the downside is that the messages don’t appear until the query has fully completed, which doesn’t help that much. 

I have found two workable options for getting notification of a SQL Server Process Status, both during a process and upon completion. 

Growl for Windows

The first option is using Growl for Windows+ xp_cmdshell

From the Growl site:

Put simply, Growl lets you know when things happen. Files finished downloading, friends came online, new email has arrived - Growl can let you know when any event occurs with a subtle notification.

The version for Mac appears to be much more complete in terms of hooks to COTS software, but with some simple configuration work I can get what I want. 

After installing the main software package, download the command line program as well:

GrowlNotify Command Line Script

Now, all we have to do is create a command line string and execute it (just as easily with command prompt, shell in VB etc.).  The query below contains the full syntax for wrapping a Growl notify command for use in xp_cmdshell, which is a great way to run command line statements from SQL Server Management Studio.  

Once xp_cmdshell is enabled, simply copy and paste the code below into a query window:

declare @myalert varchar(max)

set @myalert = 'D:\workspace\code_checkouts\growl\growlnotify /t:Hey_There /s:true /host:localhost /p:2 "Query Complete\n ' + convert(varchar(255),getdate())

declare @mysql varchar(max)

set @mysql = 'EXEC xp_cmdshell ''' +@myalert + ''''

exec (@mysql)

And you will get a notification window popup:

This is useful when I am at my workstation, but I also want to receive an email if I am away to save me the energy of logging in every couple hours to check progress. 

Configuring Growl to use email is fairly easy once you know the settings.  For Gmail account settings:

Then enable the email notification for growlnotify:

Now, in addition to a popup, I get an email in my inbox:

SQL Server Database Mail

Another, probably better, option is using the built in SQL Server 2008 functionality called Database Mail. 

This excellent walkthrough explains the whole concept:

http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

I used this query to enable the functionality in SSMS:

GO

RECONFIGURE with OVERRIDE

GO

sp_CONFIGURE 'Database Mail XPs', 1

GO

RECONFIGURE with OVERRIDE

GO

These are the Gmail settings for Database Mail:

Once the settings are configured, you need to know how to construct the command - this is detailed syntax page from MSDN:

http://msdn.microsoft.com/en-us/library/ms190307.aspx

Finally, I used this to send the email:

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'RoyJackson',

    @recipients = 'roy.jackson@gdr.com',

    @body = 'The stored procedure finished successfully.',

    @subject = 'Automated Success Message' ;

   

And this to see if it worked – if the message failed you will see an error row and some details as to the status:

select * from msdb.dbo.sysmail_allitems

Next stop is using Growl within some other programs such as ArcGIS / python…

Hope this is useful!