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

    Unanswered: Blocking When doing bcp

    Any ideas?

    I execute a sproc from QA.
    It interogates a folder:

    [code]
    Select @Command_String = 'Dir ' + @FilePath + '\*.txt'
    Insert Into XLAT_Folder exec master..xp_cmdshell @Command_String
    [/code[


    Which I then Parse and get the details...

    Set up a CURSOR (Booo hiss....)

    Code:
    DECLARE XLAT_Folder CURSOR FOR
    	  SELECT  Create_Time
    		, File_Size 
    		, [File_Name]
    	    FROM  XLAT_Folder_Parsed
    	ORDER BY [File_Name]


    [EDIT]
    WHILE FETCH STATUS = 0
    BEGIN
    BEGIN TRAN
    [/EDIT]

    Then, based on the methodology that the file name must match the table and format file (I check to make sure everything is out there)

    I then bcp the data in using my connection pooling id (I'm logged on as sa in qa)

    Code:
    		SET @cmd = 'bcp ' + @db_name + '..' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + ' in ' 
    			+ @FilePathAndName
    			+ ' -f' +   'd:\Data\Tax\'  + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + '.fmt'
    			+ ' -S' + @@servername + ' -U -P'
    
    		SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + '''' + ', NO_OUTPUT'
    
    		INSERT INTO #XLAT_BCP_Results(Col1) Exec(@Command_String)

    [EDIT]
    MOVE DATA FILE TO ARCHIVE
    COMMIT TRAN
    ANOTHER FETCH
    [/EDIT]


    A spid is launched to do the bcp...I have 4 files...on the last load The connection Pooling lauched spid gets blocked by the sa spid that launched the sproc...it doesn't happen all the time, just most of the time....

    I've put a trace on in Profiler, but I don't see anything...I've picked the event class of deadlocks...but I never see it...

    When I do sp_who, it shows the blocking...

    I'm so confused....
    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
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Can you get the table/index that has the blocking lock on it?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well the blocking spid I found in sp_who, is the QA window I lauched the sproc from...the blocked spid is the idependent bcp thread (well not so independent)..

    There's no RI between these code tables, and just a trigger...that moves the rows to history table...

    I'll check sp_locks....
    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
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Every single lock on the "parent" spid has a status of "grant" and a type of IX....
    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.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am pretty sure there wasa way of telling a blocking lock from a regular lock. I will have to do a bit of checking, but this job thing has me a bit tied up.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In the output of sp_lock, look for a status of "WAIT". This is the process that is attempting to get at the locked resource, and of course, must wait for the first process to get done with it. Alternatively, you can check the waitresource column in the sysprocesses table. This will also tell you what the processes that is blocked is waiting on.
    This will give you the name of the table/index that you are getting hung up on. After this, I am afraid it is going to be a slog through the code to find the actual stumbling block.
    Let us know how it comes out.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Thanks...

    Been there...

    It seems that even though I expected lines inside of a sproc to be serial, it looks like the delete doesn't complete before the sproc continues to the nect line of code, which is the xp_cmdshell bcp...

    I find this totally unbelievable, but in every instance, the independent xpshell thread (which is executed with a different login) is the one that is being blocked. And it is being blocked by the spid that executed the sproc...

    With me so far...

    I then placed the COMMIT immediatley after the DELETE, and viola!

    No more problems....now tell me...are the lines in a sproc serial?

    I always thought they were, and I'll be damned if I believe any differently, but I have NO other explination.


    Thanks for everyones help.

    WORK AROUND ...HO!
    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.

  8. #8
    Join Date
    Sep 2013
    Posts
    1

    bcp command hangs stored procedure

    Hi,

    I have a similar problem where bcp command is called and executed successfully sometimes but fails randomly.

    My stored procedure looks like this:

    select @SQL = 'select * from [' + @RepositoryName + '] where ( IsNumeric([' + @PhoneField + ']) = ' + CONVERT(varchar(1), @FALSE) + ' OR LEN([' + @PhoneField + ']) <> ' + CONVERT(varchar(2), @ValidLength) + ')'

    execute(@SQL)

    set @Count = @@ROWCOUNT

    if @Count > 0


    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE
    EXEC sp_configure 'xp_cmdshell', 1
    RECONFIGURE

    set @CommandSQL= 'bcp "SELECT * FROM ['+DB_NAME()+'].[dbo].[' + @RepositoryName + '] where ( IsNumeric([' + @PhoneField + ']) = ' + CONVERT(varchar(1), @FALSE) + ' OR LEN([' + @PhoneField + ']) <> ' + CONVERT(varchar(2), @ValidLength) + ')' " queryout "C:\Export\' + @ExportFileName + '.csv" -c -t, -T -S ' + @@SERVERNAME

    BEGIN TRAN
    EXEC master..xp_cmdshell @CommandSQL

    IF @@ERROR <> 0 and @@TRANCOUNT <> 0
    ROLLBACK TRAN

    ELSE IF @@ERROR = 0 and @@TRANCOUNT > 0
    COMMIT TRAN

    set @SQL='delete from [' + @RepositoryName + '] with (Rowlock) where ( IsNumeric([' + @PhoneField + ']) = ' + CONVERT(varchar(1), @FALSE) + ' OR LEN([' + @PhoneField + ']) <> ' + CONVERT(varchar(2), @ValidLength) + ')'

    execute(@SQL)


    The above procedure is called 4 times from another procedure by passing appropriate parameters. Most of the time, it executes successfully for 2 times before failing. When I try to debug, it always hangs at EXEC master..xp_cmdshell @CommandSQL. The task manager shows bcp.exe as running at this point. Interestingly, the required rows are exported using the bcp command, but the stored procedure does not move further until bcp.exe is manually stopped from the task manager.

    Running sp_lock to check for locking processes reveals this:
    spid dbid ObjId IndId Type Resource Mode Status
    52 9 0 0 DB S GRANT
    53 9 0 0 DB S GRANT
    54 9 0 0 DB S GRANT
    55 9 0 0 DB S GRANT
    56 9 0 0 DB S GRANT
    57 9 0 0 DB S GRANT
    58 9 0 0 DB S GRANT
    59 9 0 0 DB S GRANT
    61 5 0 0 DB S GRANT
    62 9 0 0 DB S GRANT
    66 9 0 0 DB S GRANT
    67 1 1131151075 0 TAB IS GRANT
    68 9 0 0 DB S GRANT
    69 9 0 0 DB S GRANT
    70 9 0 0 DB S GRANT

    For SPID 67 as seen above, sysprocesses shows this:
    SPID 67
    KPID 7084
    Blocked 0
    WaitType 0x0000
    LastWaitType 0
    WaitResource MISCELLANEOUS
    DBID 1
    UID 1
    CPU 517
    Physical_IO 16
    MemUsage 2
    Open_Tran 0
    Status runnable
    cmd SELECT
    Stmt_Start 24
    Stmt_End -1
    Request_ID 0

    Any help would be appreciated. Thanks!
    Last edited by Supriya Krishna; 09-30-13 at 18:14.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Using the tools available inside of a stored procedure, this kind of blocking is both intermittent and not uncommon. Depending on your SQL Version and Service Pack the culprit can be any of a number of system tasks which are automagically invoked by SQL Server after a DELETE operation.

    I prefer to move this kind of operation to SSIS. It has better tools for transparently managing the problems that can occur.

    As a poor second best, you can restructure your code to do the extract operations in one pass with the HOLDLOCK hint, then do the DELETE operations in a second pass.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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