SQL Server Database Notifications

2012-01-30T16:18:00.000-08:00

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 hascompleted by adding print statements to my lengthy batch files to see how eachsection performs:

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

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

I have found two workable options for getting notificationof 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:

Putsimply, Growl lets you know when things happen. Files finished downloading, friendscame online, new email has arrived - Growl can let you know when any eventoccurs with a subtle notification.

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

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

GrowlNotifyCommand Line Script

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

Once xp_cmdshell is enabled, simply copy andpaste 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 wantto receive an email if I am away to save me the energy of logging in everycouple hours to check progress.

Configuring Growl to use email is fairly easy once you knowthe 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 DatabaseMail

Another, probably better, option is using the built in SQLServer 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:

RECONFIGURE with OVERRIDE

sp_CONFIGURE 'Database Mail XPs', 1

RECONFIGURE with OVERRIDE

These are the Gmail settings for Database Mail:

Once the settings are configured, you need to know how toconstruct 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 procedurefinished successfully.',

@subject = 'Automated Success Message';

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

select * from msdb.dbo.sysmail_allitems

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

Hope this is useful!

A Humble Reset

2012-01-23T11:43:00.000-08:00

Greetings out there to no-one in particular!

One of my goals for 2012 is to re-engage the online community in some of the areas that I am interested in. This blog will be a place where I can talk about the never-ending challenges that I encounter on my projects, and what interests me as from a technical and business perspective. Hopefully I can interest people in learning a new skill or tool, can save some time in solving problems, or can meet and interact with others in the geospatial technology industry.

I am always interested in solving problems by bringing people together, understanding business and user requirements, and applying my unique perspective and managerial / technical skills; it keeps me engaged and focused.

The main technology tools I use today are Visual Studio 2008, SQL Server 2008, Javascript, ArcGIS for Server and Desktop, and a wide variety of third party data and mapping APIs. I strive to be an expert in data quality and sources, production and automated systems, tool and web development, making customers happy and listening. I want to continue to learn about business development, sales systems, and to be a better designer from a visual perspective.

Thank you for visiting, sharing your thoughts, and beginning a conversation!

~Roy

Geospatial Professional

SQL Server Database Notifications

A Humble Reset