Blocking When doing bcp

Any ideas?

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

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]


WHILE FETCH STATUS = 0
BEGIN
BEGIN TRAN

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)

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)


MOVE DATA FILE TO ARCHIVE
COMMIT TRAN
ANOTHER FETCH

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...Can you get the table/index that has the blocking lock on it?|||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...|||Every single lock on the "parent" spid has a status of "grant" and a type of IX...|||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.|||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.|||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!