Very nice PW! Great example of recursive CTEs too!

<jealous>I wish I'd thought of that</jealous>

How about something like this?

CREATE TABLE #Services
(
    serviceId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
    consumerId INT, 
    beginDate SMALLDATETIME, 
 ...

May I ask why Node 1 is attempting to acquire an exclusive table lock? It may be worth going beyond using NOLOCK as a quick fix.

Scott is correct in...

Hi Jeff,

To answer your question about why a trigger would fire if there were 0 records affected. If an INSERT/UPDATE/DELETE statement is run against a table that has no rows...

Since you're running SQL 2k, and if you want to rum multiple statements It might also be worth check out XP_EXECRESULTSET. Then all you need to do is a little...

It looks like some of those group bys are unneccessary. Check out Jesper's code for the best 2k approach to date. As far as an explanation goes, This code evolved...

Wow! That's much better! Nice work Jesper!

I'm glad you're up and running!

Check out this thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=337493#bm337593

This person is trying to do what you are trying to do.

How many rows(registers) are you expecting to update? What is your trasaction isolation level? I would say it is never advisable to say the following:

IF NOT EXISTS(SELECT ...)
BEGIN
  ...

Sure! Specifically what code are you refering to? The code in the article or the code in the forum (or both)

The 30 is an arbitary max of colid. If you have a proc greater than 120,000 bytes long, this will fail. Thank you for brining this up.

I've thought about...

Thank you to everyone who has commented on the article. I've really enjoyed hearing how others have approached this problem and it's also great to hear that there are better...

That's definitly a valid approach, and I like that your solution give you line numbers. I have often wanted to expand out the functionality of my search app to be...

OBJECT_DEFINITION sounds like a real winner! I hadn't heard of that function, it sounds perfect though!

Thanks for sharing this tip Tim! I guess I need to swot up some more...