Everything You Wanted To Know About Blocking...but Were Afraid To Ask

OK...this is driving me nuts....

In the First DELETE and bcp I was getting the thread being launched by xp_cmdshell was being blocked by the parent thread...

put in WAITFOR...sometime it worked...started with an empty table..it worked....left the 28k rows, blocked...

Now, put SELECT COUNT(*)...works each and every g-d damn time...

HUH?

Now I get to the bcp out..

added the same code WAITFOR/SELECT *...

blocks each and ever g-d damn time....

I'm very reticent to COMMIT and start another tranny block...

Anyone have any ideas?



SET NOCOUNT ON

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_DataHold]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_DataHold]
GO

CREATE TABLE wrk_DataHold(Col1 varchar(8000))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_OldNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_OldNew]
GO

CREATE TABLE wrk_OldNew(Old varchar(255),New varchar(255))
GO


INSERT INTO wrk_OldNew(Old,New)
SELECT 'SEVERAL EE~S', ''
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_ModifyRows]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ModifyRows]
GO
CREATE PROC usp_ModifyRows
@Path sysname
, @FName sysname
AS

SET NOCOUNT ON

BEGIN TRAN

DECLARE @cmd varchar(8000), @Servername sysname, @rc int, @error int, @rowcount int
, @Old varchar(255), @New varchar(255), @x int

CREATE TABLE #bcpLog(Col1 varchar(8000))

SET @rc = 0

DELETE FROM wrk_DataHold

SELECT @error = @@error, @rowcount = @@ROWCOUNT
IF @error <> 0
BEGIN
SET @rc = -1
GOTO usp_ModifyRows_Error
END

SELECT @x=COUNT(*) FROM wrk_DataHold
WAITFOR DELAY '000:00:10'

SET @cmd = 'bcp wrk_DataHold in ' + @Path + @FName + ' -S ' + @@SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @cmd

DECLARE OldNew CURSOR FOR SELECT Old, New FROM wrk_OldNew

OPEN OldNew

FETCH NEXT FROM OldNew INTO @Old, @New

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE wrk_DataHold
SET Col1 = REPLACE(Col1,@Old,@New)
WHERE Col1 LIKE '%'+@Old+'%'

SELECT @error = @@error, @rowcount = @@ROWCOUNT
IF @error <> 0
BEGIN
SET @rc = -1
GOTO usp_ModifyRows_Error
END

INSERT INTO #bcpLog(Col1)
SELECT 'REPLACE "'+ RTRIM(@Old) + '" With "' + RTRIM(@New)+ '"' UNION ALL
SELECT '('+CONVERT(varchar(25),@rowcount)+' row(s) affected)'

FETCH NEXT FROM OldNew INTO @Old, @New
END

CLOSE OldNew
DEALLOCATE OldNew

SELECT @x=COUNT(*) FROM wrk_DataHold
WAITFOR DELAY '000:00:10'

SELECT @FName = SUBSTRING(@FName,1,CHARINDEX('.',@FName)-1)+'.new'

INSERT INTO #bcpLog(Col1)
SELECT 'Preparing to Write out new file '+ @Path + @FName
/*
SET @cmd = 'bcp wrk_DataHold out ' + @Path + @FName + ' -S ' + @@SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @cmd

SET @cmd = 'bcp #bcpLog out D:cpLog.txt -S ' + @@SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @cmd
*/

COMMIT TRAN

usp_ModifyRows_Exit:

SELECT * FROM #bcpLog
DROP TABLE #bcpLog
SET NOCOUNT OFF
RETURN @rc

usp_ModifyRows_Error:

CLOSE OldNew
DEALLOCATE OldNew
ROLLBACK TRAN
GOTO usp_ModifyRows_Exit

GO

SET NOCOUNT OFF