Can i use SQL Server 2005 - Notification Polling within a windows service? or Console application?

I want to use SQL Server 2005 Notification services in order for an agent of some kind to repeatidly Poll adb in order to identify a change/update to particular tables, and then for an action to take effect?

What the best way to do this? as the novice in me say a windows service sounds perfect? but it then again it sounds like a duplication in technology? Would a website be better? but that said i dont want or need a front end to this so is this better or worse?

Any advice would be much appreciated

Thanks
paulCardiffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin HillSr. SQL Server DBACommented:
Notification Services is very difficult to work with (XML only, no GUI to set up), and is going away in SQL 2008.

Depending on your traffic, I'd stick with triggers, a job that queries the table, or your own application.
0
paulCardiffAuthor Commented:
thats quite a statement :-) i'll be honest i've go 3 avenues a the moment and i'm at a loss on which one to go for i.e.

1) Triggers in combination with Cmd Shell to run an Exe file
2) 100% SQLCRL
3) Windows service

My key requirement is that i need to run some .net code when certain rows are added to a table.

I'll be honest i was edging towards notifications services but based on your statment above you've made me think twice, could you please elaborate why you feel notification services would be a bad choice? and just to confirm does sql 2008 have anything in its place?

Also what do you mean by "Depending on your traffic"?

Any advice would be much appreaciated
0
Kevin HillSr. SQL Server DBACommented:
>>could you please elaborate why you feel notification services would be a bad choice?<<

Sure....when I was on the team that supported it at Microsoft, I was one of the three guys in the entire support organization that had a clue (and just barely) how it worked.  When I left, I took one of the other two with me.

Its not a bad product, but so infrequently adopted (due to complexity) that nobody has the experience.

I am speaking about first level support here.  There are some Escalation folks that are somewhat fluent, but nobody is an expert.

This alone, plus the fact that its gone in 2008 with no replacement makes me walk away from any product.

>>Also what do you mean by "Depending on your traffic"? <<

HIgh traffic = bad for triggers :)

Beyond that I'm not sure I'll be much help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
paulCardiffAuthor Commented:
I see what you mean e.g. http://sqlblog.com/blogs/aaron_bertrand/archive/2007/07/31/planning-on-using-notification-services-don-t-bother.aspx

This is so annoying of MS as from my point of view this seemed like the perfect bridge for linking SQL to third party API's i.e. Table updates -> .net hits webservice and informs 3rd party at real time.

So given your experiance Kevin would you mind if i asked how you'd reommcend tackling this problem like this? i.e. SQL Trigger/event -> Webserice/.net? e.g. SSIS, SQLSLR, Triggers, cmdshells etc

FYI: I've looked into pure sqlslr but found it a bit of a pain talking to webservices
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.