Just an update, in case anyone else is reading this, I based my query upon the example in the link that Chris sent as follows:

with AllNodes (parent_id, child_id, level)

as

(

select parent_id,...

Hi Chris,

Ok, I'll have a read, thanks for your help.

Regards

David

I can't ever see it going beyond six levels if that helps!

Hi Chris,

The problem is that the number of levels is not fixed, for some members it could be two levels deep, for others three and for others one!

Thanks

David

Hi Chris,

Here is a sample script that will replicate a portion of my data:

CREATE TABLE #tblTMembers(

[CHILD_ID] [int] NOT NULL,

[PARENT_ID] [int] NULL

) ON [PRIMARY]

INSERT INTO #tblTMembers

...

Hi,

Does anyone have any suggestions, I'm still no further forward :crazy:

Is there anything new in SQL Server 2005 that can help?

Thanks

David

Hi Gail,

Here is the output in the ERRORLOG after turning the trace...

GilaMonster (9/18/2008)

---

What traceflag do you have on to get that output? (1205?)

Try switching whichever that one is off and switching 1222 on. It gives very nice, very detailed output. There's...

Hmmm...not sure about easy 😉

This deadlock is occurring on our live server and is related to a third party product so trying to recreate what the problem is will be...

Hi Reeth,

Thanks for the tip. The example you provided works perfectly!

Regards

David

Hi Mike,

Unfortunately, I cannot change the column type because it is a third party database. I tried the following:

select datepart(m,cast(convert(char(8),'11/08/08',103)as datetime))

but it still returns 11.

Thanks

Thanks Michael, that's what I was trying to achieve.

Hi Kent,

Thanks for your help, I've managed to get it work using the ROW_NUMBER and partition by clause.

Jeff/Matt,

Thanks for your help, I've decided to go down the Tally table route as it suits what I need with very little effort.

BTW Jeff, good article on Tally tables!

Thanks Mark