Hi Craig,

Thank you for your interest --- it turns out replicating actually involves a little bit more work. The insert procedure is called by another and it only blocks when called by the other proc. Here's the code (and version info as well):

create table dbo.test(

id int

);

go

create procedure [p_test]

as

begin tran

insert into dbo.test(id) values (1);

commit tran

waitfor delay '02:00:00';

select 1;

go

create procedure [dbo].[p_call_p_test]

as

create table #temp_table(

blah sysname

);

insert into #temp_table (blah)

exec p_test;

go

Now calling "exec dbo.p_call_p_test" in one tab and in another "select * from dbo.test" will result in blocking until the called proc is canceled or 2 hrs later when the delay is finished. I have noticed that only when the caller procedure is inserting into the temp table the results from p_test does this occur. So that insert into the temp table is perhaps causing all objects in p_test to lock.

@@version:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)

Jul 9 2008 14:17:44

Copyright (c) 1988-2008 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

Thank you