run SP_who2 and see what process is blocking?

Thanks, interesting way of looking at the problem.

Also you could change the WHERE clause to

WHERE N <= DATALENGTH(CountryName) instead of using the SUBSTRING comparison.

The example...

Dont mean to put a downer on the conversation, but I was looking at a code generated query today which was running very slowly - subquery upon subquery upon subquery...

You'll need to respond about whether there are indexes available..

Also try running it with just the empid join, then modify it for just the name string join and see how...

Cast the empnum's as INT and remove the 0000 concatenation / string comparison.

Remove the CHARINDEX function and try "a.account LIKE '%' + @query + '%' "

Just an FYI for anyone who gets the editorial via email... the book "Protecting SQL Server Data" is advertised for sale from amazon below the editorial, but you download...

Can you check the actual execution plan? Maybe limit the subselect to top 10 results first. It's probably cascading the delete to other tables etc..

Also find out how...

WITH cte(warehouse, product,req_quantity, batch_number,RowNumber) AS

(

SELECT a1.warehouse, a1.product, a1.req_quantity, a2.batch_number,

ROW_NUMBER() OVER(PARTITION BY a1.warehouse, a1.product ORDER BY fifo_date ASC) AS 'RowNumber'

FROM #wms_requirements a1

INNER JOIN #wms_batch_queue a2 ON a1.warehouse = a2.warehouse...

sp_who2

"The key, the whole key, and nothing but the key, so help me Codd."

Sorry but I cannot agree with this editorial. Im not a senior dba or developer or...

It's almost a complete cultural shift in the way support is handled these days. "Service" is no longer king and cost-cutting / price takes precedence. Think of the...

I selected "nothing" however I've had issues in the past with the default "max server memory" setting being set at 2147483647 MB on 64 bit systems. Setting the max...

For SQL 2005 sp3

http://www.microsoft.com/downloads/details.aspx?familyid=e6f307c1-8b21-406e-9c6f-b1a3a1e9a98f&displaylang=en

For SQL 2005 sp2

http://www.microsoft.com/downloads/details.aspx?familyid=76d3d653-e9a0-48bc-afae-d3553f7b9235&displaylang=en

SQL 2000 Reporting services

http://www.microsoft.com/downloads/details.aspx?familyid=33554f96-5af7-4683-a537-9db293b67b8d&displaylang=en

How did you resolve this?

I have a very similar issue using the EXECUTE AS statement with sysadmin rights and getting the same error message.

I demand satisfaction! 😀