SQL Server connector (actually more of a sql2growl bridge)

[Daniel]

I have started a project to add the ability to send notifications from
SQL Server (version 2005 or higher). I thought of three ways to make
this happen:
a) use the SQL Server CLR integration feature (needs clr option in
UNSAFE mode enabled)
b) use a SQL Queue (needs Service Broker to be enabled) and have some
c# service receive the queued records
c) use a SQL Table (no special needs) and have some c# service poll
for new notifications

I pretty much left the use of CLR immediately behind me because I
don't think there are much production environments where you will get
CLR with UNSAFE option enabled (DBAers, correct me if I am wrong).

The use of a simple table would work fine but it will add a delay
between the moment you send a notification (you insert the record in
the table) and the moment the C# service picks it up to send to
Growl.

So I went for the method of using a SQL Queue. Since SQL Server uses
the Service Broker (module that facilitates queues) itself for more
and more things I don't think it will be an issue to have it enabled
in a production environment.

The idea behind a SQL Queue is that you have one process that queues
items and that you configure the queue to activate a stored procedure
to pick up the inserted item (the number of readers can dynamically be
tuned depending on the load). However I am not going to use this :) I
am only interested in the WAITFOR RECEIVE part (please google around
for more information). This mechanism allows me to start a blocking
wait for notifications (of course via a stored procedure) from C#.
This way a notification is picked up instantly when inserted into the
queue.

I plan to make the code (both the SQL Server DDL/T-SQL code and the C#
service) available soon. I have a working solution but it has some
rough edges that need to be polished off and I need to do some code
cleanup.

The first version will be limited to sending notifications from SQL
Server and there will be no support for callbacks.

Sending a notification is as simple as:

EXEC [Growl].[spQueueNotification]
@Application = N'TestApp',
@Type = N'Test Type',
@Title = N'Test Title',
@Message = N'Test Message\nSome more text'

I am looking forward to hear if a connector like this is something
that more people (instead of just me) need.

[Brian Dunnington]

Daniel -

sounds like a great solution. i am sure there are folks out there that
would like to send notifications by simply calling a stored procedure.
just one question to satisfy my curiosity:

how does the 'sending' application (not SQL, but the application that
is supposed to be sending the notification) handle registering? or is
it assumed that the application will already have been registered via
other means and this is only for sending notifications?

once you have the code to a point that you would like to share, send
me a link to an article/source repository/whatever and i will link to
it from the growlforwindows.com site.

[Daniel]

The first time an application and type are used from the stored
procedure the C# code will do the actual registation of the
application. Both application and types are cached. This part is not
yet perfect though. The C# service (the part that selects the
notifications from the SQL queue and relays them to a Growl server)
should be able to handle the situation that a user removes the
application from Growl. For this I am thinking of catching the
"Application not registered" error and then recreate the application
and types together with sending out the last notification.
But his is where the rough edges are in my solution. I want to be able
to handle different connectors, different applications, different
types and even different icons from SQL Server (my idea is that the
SQL to Growl "bridge" can be used for various database applications).

As soon as I am happy enough with the code I will send you a link.
Hopefully together with some documentation on how to use it.

On Mar 31, 12:54 am, Brian Dunnington <briandunning...@gmail.com>
wrote:

[Daniel]

Finished the first version. Setup is available at:
http://cloud.github.com/downloads/knippers/sql2growl/Sql2Growl_1.0.zip.

The installation consists of two parts (both included in the zip). One
is to setup the objects in the database (personally I am always a bit
reserved to have a setup touch my databases, but if there is a user
demand for it, I might integrate the setup of database objects in the
installation set). The seconds is to install the NT service that
interacts with the database and upon receiving a notification forwards
it to Growl.

To send a notification from the database execute the following
procedure (assuming that the defaults in the SQL script where used):

EXEC [Growl].[spQueueNotification]
@Application = N'Test App',

@Type = N'Executed',

@Title = N'Test Title',

@Message = N'Test message\nline two' -- use \n for newline

Other parameters:

@ClearCachedObjects = 0 -- set to 1 to clear cached Growl
objects
@Icon = 'name' -- The service will look for the icon in Icons
folder of installed service and append .png to the name
@Password = 'password' -- usually only needed if notification
has to be forwarded to other Growl instance
@Host = 'hostname' -- if you want the notification to be
forwarded to another Growl instance, it's preferred to setup
-- forwarding in Growl itself
@Port = '23053' -- only has to be set if Growl is not
listening on default port

Oh and of course the full sourcecode is available at:
http://github.com/knippers/sql2growl/tree/master

Please let me know if you (anyone) has any ideas on how to improve the
code. If you find a bug or if you need help.

Daniel

[briandunnington]

nice. i am going to play with it a bit and also put up a link from the
growlforwindows.com site (if that is ok with you).

thanks for sharing your work.

[Daniel]

Putting up a link would be appreciated. And please let me know if you
have tips or any other suggestion to make it better.

[briandunnington]

do you have a page that explains the project that you would like me to
link to, or would you prefer that i just link to the post above where
you describe the installation and usage, as well as link to the
installation and source code?

[Daniel]

I do not have a special page for it, but I keep a weblog for it at:
http://www.tudra.net/wp/category/sql2growl/

And maybe just for convenience use you can add a direct link to the
downloads: http://github.com/knippers/sql2growl/downloads

If you ever intent to host a sort of 3rd party, integrators, etc. page
(hint :) ) I would be more then happy to provide you with a
description of the tool and some usage info.


On Apr 14, 12:30 am, briandunnington <briandunning...@gmail.com>
wrote:

[briandunnington]

i am planning to add a 3rd party/integrators type section soon, but i
just haven't had the time yet. (for the custom displays, i started
this process so that i can feed it a list of information like creator,
link, image, description, instructions, etc and it pulls it all in for
each one, but i haven't extended it yet for anything beyond displays).

as soon as i get that up, i will request the remaining info from you.

thanks again for sharing the code - i am still digging through it all
but it is pretty handy so far.