blocking.....the Giants could use it, not me....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

blocking.....the Giants could use it, not me....

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-19 : 16:29:44

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?

[CODE]

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:\bcpLog.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

[/CODE]

Brett

8-)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-19 : 18:11:19

How about breaking the proc into a multi-step job

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-21 : 21:57:06

I'm a bit surprised this ever works.
You are doing a delete in a transaction which should lock the table (unless maybe it's already empty?).
The bcp will try to insert into that table using another connection and be blocked.

You would usually design it so that the bcp is actioned outside the transaction and only when you move the data to the production tables do you worry about the action being atomic.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-22 : 08:59:47

Thanks for the insight...

Yes I understand...

But how would you clear out a work table before preparing to load it?

Clear it out at the end?

Anyway, that's not the problem anymore.....The select Count(*) seems to make the delete action to be commited so the bcp in works every time....

With reflection, I see how you would think it should never work....

But it does....it's the bcp out now that's blocked...

And again, I see how it should be...

And this is all because they are separate threads, right?

If one had commands to do it from within TSQL, it shouldn't matter, right?

I've always tried to make a sproc be 1 transaction...although, there are some that I didn't..

It seems the suggestion is to make separate trans to do this...

Yes?

Thanks for the replies...

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources