asked on

Notification from SQL Server to my App

Hello,

I've been using timers to check some object status from a database (SQL Server 2008 Express). It was all ok, until the data retrieved was big enought that the timers routine to check got inefficient.

I read something about SQLDependency, which triggers an event when a table is changed and notifies my app. That seems to be great, since i have to treat the data only when I really have to...

The problem is I'm kind of a rookie in database structuring and the examples I found were rather complex for me...

Could someone post here a very simple example that i can understand how to use this? Or if there is a better way that would be welcome too.

Thanks in advance.

Dmitry G

There are some examples alreadyon Internet:

http://msdn.microsoft.com/en-us/library/t9x04ed2(v=vs.80).aspx
and related
http://msdn.microsoft.com/en-us/library/9dz445ks(v=vs.80).aspx

The code is pretty simple.

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

toddinho

ASKER

Wow, thats GREAT!!! Thanks for the example emoreau!!

PS: Sorry for the time i took to accept the solution, I couldn't download the .zip from the office, i had to take it home and email it to myself.

toddinho

ASKER

Just one last question: If i want to drop a database that has these services it says it's in use. How can i delete them so I can drop it?

Éric Moreau

I never tried. but you should be able to use SP_Kill to kill any session (use SP_who2 to find out the sessions).

toddinho

ASKER

I figured this out... It wasn't the Service Broker that was using the database, i had to set it offline.

alter database [DemoNotifications2]
set OFFLINE
with rollback immediate
go
drop database [DemoNotifications2]
go

Thanks anyway :)