Mark

Yes, a similar thing happens with your original sample data as well.  Now we just need to see what happens when you get hold of all the extra data.  What indexes do you...

Mark

OK, please let us know when you've done that.  My inkling may have been wrong, since the query optimizer may not totally materialise the CTE anyway.  If you SET STATISTICS...

Mark

I see what you've done there, using the ROW_NUMBER rank instead of the MAX aggregate function.  I'd be interested to find out - have you compared the execution plans for...

Stephen

Here's something that might work for you.  It generates code which you can then run.  It isn't perfect, since it doesn't traverse the whole hierarchy; if you need it to...

Mark

CTEs can be used in UDFs without temporary tables.  I have rewritten your query as below.  I'm not sure about improving performance, but it makes it more readable.  You mentioned...

Mark

Two things:

(1) Using a Common Table Expression instead of a subquery would make your query more readable, even if it didn't help the performance.

(2) At the beginning you set @RegID...

Anders

The DMV sys.dm_exec_requests is probably what you're looking for.  Books Online tells you which new DMVs/metadata views in SQL 2005 map to which system tables in SQL 2000.

John

"No idea on probabilities..."  Steve, the probability that the second disk failure will kill your n-disk RAID10 array is 1 in n-1.  So (approximately)  33% if you have 4 disks,...

Yes, or replace your verify statement with this:

RESTORE VERIFYONLY FROM 'ABC_BackUp'

John

I'm guessing you haven't applied Service Pack 4?  The following link refers: you can also find it simply by googling your error message.  That should be the first thing you...

So I think the most important thing is to find out why you are getting the error.  Are you backing up to tape or to disk?  Is it to a...

Job y must be pretty important if it's worth trashing a backup for!  Have you done any investigation into why your backup sometimes takes 30 minutes and sometimes 2 hours? ...

Pavan

This page from Books Online should get you started:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/c001c2e7-d092-43d4-8fa6-693b3ec4c3ea.htm

I'm a bit concerned, though, that if the user who creates the procedure doesn't have access to the objects in the stored...

You've posted in the SQL 2005 forum... please confirm that you are using SQL Server 2005.

Thanks

John

What does job x do?  Bear in mind that if you want it to stop, you will probably want it to roll back as well... which could easily take longer...