BCP Prob: locking table , how to unlock?

BCP Prob: locking table , how to unlock?

Post by mg » Wed, 20 Aug 2003 09:13:24



Hi,

Is there anyway to unlock tables that BCP has locked. I
was running the BCP command thru sql analyser, and it hung
and could not cancel it. I Exited sql analyser. Tables
still locked. How do i unlock them.
I think bcp locked a file on the file system as well?

When we go to system activity Enterprise manager, it times
out. If we restart server the the tables are unlocked.

Q1) is there a way to unlock a table without restarting
the SQL server.

We think its being caused by bcp, locking a file on the
system? not sure

Q2) Do anyone else has this problem?

Thanks
MIc

 
 
 

BCP Prob: locking table , how to unlock?

Post by Jacco Schalkwij » Wed, 20 Aug 2003 10:10:03


Hi,

It looks like bcp still has a transaction open. Run EXEC sp_who2 from Query
Analyzer and for a process that is used by bcp (check the ProgramName
column). Get the SPID and kill the process with KILL<SPID>. The transaction
will now be rolled back and the locks will be released. This can still take
a long time (several minutes) depending on the amount of work that has to be
rolled back.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


Quote:> Hi,

> Is there anyway to unlock tables that BCP has locked. I
> was running the BCP command thru sql analyser, and it hung
> and could not cancel it. I Exited sql analyser. Tables
> still locked. How do i unlock them.
> I think bcp locked a file on the file system as well?

> When we go to system activity Enterprise manager, it times
> out. If we restart server the the tables are unlocked.

> Q1) is there a way to unlock a table without restarting
> the SQL server.

> We think its being caused by bcp, locking a file on the
> system? not sure

> Q2) Do anyone else has this problem?

> Thanks
> MIc


 
 
 

1. Locking and unlocking table records...

I have a client application that sets a flag in a table when a record is
being edited and the resets the flag when the record is closed.  The flag is
used to prevent other users from trying to edit the record at the same time.
This design works assuming that nobody powers off their machine while a
record is being edited leaving the flag set preventing all users from
accessing the record and having someone accessing the raw data and resetting
the flag.  I need to implement something more robust to lock the record
while the user has the record open but to unlock the record if the user
machine goes down while the record is locked.  Any ideas would be much
appreciated. Thanks in advance.

JW

2. FM CALCulation fields

3. unlock the sql server lock on table

4. Delphi components for Microsoft Access

5. Urgent Help!-locked paradox tables, can't unlock

6. Problem with zombie processes in Oracle on Ultrix3.1

7. How to lock/unlock table rows with JDBC?

8. Update locks

9. TABLE LOCK/UNLOCK, PLEASE HELP!!!

10. LOCK TABLE never unlocks...

11. How unlock the locked table?

12. ADO - LOCK TABLE never unlocks

13. HELP!! LOCKING TABLES PROB