Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: 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?

    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is BULK INSERT an option? Since it can be part of your transaction, it dodges the bullet on locking/blocking issues.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, it's not the bcp in anymore that's the problem...

    It's the bcp out...

    And as Nigel suggests

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33554

    He's not sure why it works at all....

    All I know is that it does...

    I was assuming....

    DELETE Occurs...

    The fact of this is that there should be a lock on the table


    bcp occurs..separate thread...

    should be totally blocked...and it is....

    Put a SELECT COUNT(*) in before the bcp..

    now the bcp works...why? I have no idea...I guess it shouldn't...

    Now it works...do the replaces....fine..

    I know this is true because I put the bcp out in a comment block and it finishes successfuly...

    take the bcp out, out of the comment block...and it's blocked...

    Guess I need to make separate transactions....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just for the jolly factor, I'd execute sp_lock for your spid before and after the SELECT @@count to see what changed.

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    yeah, I'll have to check it out....

    I just made the sproc have 3 transactions...3 seconds...done...

    Tried to use profiler...anyone got a good template...what a lot of stuff going on....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If anyones' interested....

    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
      COMMIT TRAN
    
      BEGIN TRAN
    	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
      COMMIT TRAN
    
       BEGIN TRAN 
    
    	INSERT INTO ##bcpLog(Col1) 
    	SELECT 'Preparing to Archive Old file To '
    		+ @Path + '_'+ @FName+'_'+REPLACE(REPLACE(REPLACE(CONVERT(varchar(25),GetDate()),'-','_'),':','_'),' ','_')
    
    	SET @cmd = 'MD ' + @Path+
    		+REPLACE(@FName,'.','_')
    		+'_'+REPLACE(REPLACE(REPLACE(CONVERT(varchar(25),GetDate(),120),'-','_'),':','_'),' ','_')
    	INSERT INTO ##bcpLog(Col1)  SELECT @cmd
    	INSERT INTO ##bcpLog(Col1)  EXEC master..xp_cmdShell @cmd
    
    	SET @cmd = 'MOVE '+ @Path + @FName + ' '
    		+ @Path + REPLACE(@FName,'.','_')
    		+'_'+REPLACE(REPLACE(REPLACE(CONVERT(varchar(25),GetDate(),120),'-','_'),':','_'),' ','_')+ '\'+ @FName
    	INSERT INTO ##bcpLog(Col1)  SELECT @cmd
    	INSERT INTO ##bcpLog(Col1)  EXEC master..xp_cmdShell @cmd
    
    	INSERT INTO ##bcpLog(Col1) 
    	SELECT 'Preparing to Write out new file '+ @Path + @FName
    
    
     COMMIT TRAN
    
    	SET @cmd = 'bcp wrk_DataHold out ' + @Path + @FName + ' -S ' + @@SERVERNAME + ' -U -P -c'
    	INSERT INTO ##bcpLog(Col1)  EXEC master..xp_cmdShell @cmd
    
    
      BEGIN TRAN 
    	SET @cmd = 'bcp ##bcpLog out ' + @Path + 'bcpLog.txt -S ' + @@SERVERNAME + ' -U -P -c'
    	SET @cmd = 'EXEC master..xp_cmdShell "'+@cmd+'", no_output' 
    	EXEC(@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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •