• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

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
0
paulCardiff
Asked:
paulCardiff
  • 2
  • 2
1 Solution
 
Kevin3NFCommented:
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
 
Kevin3NFCommented:
>>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
 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now