Skip to content
Advertisement

BCP queryout hangs with READ COMMITTED transaction isolation

Using SQL Server 2016 and bcp version 14

My query returns about 16k rows and runs quickly to completion in SSMS with the default (READ COMMITTED) isolation level however if I try to save the output of the same query using BCP the tool hangs. If I set isolation level to READ UNCOMMITTED in the BCP query then it completes quickly.

Any ideas on why BCP needs no transaction isolation with this particular query? I have other queries against other tables in the database that do not have the problem with BCP.

Advertisement

Answer

Actually the bcp behavior described in my question was due to the table being queried was locked by an application that misbehaved and stranded the connection leaving the table locked until the DBA killed the session the next day!

so there was no mystery about bcp.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement