bcp timeout

We use bcp a lot in production and redistributable software.
We'd like to get bcp to terminate early if it runs for over a certain amount
of time due to locking/deadlock/...
Thus, I'm trying something like and it's not working - ideas?
bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2" queryout
"s24.txt" -c -U abc -P ewe
Thanks.
Tom
Thomas Brown wrote:
> We use bcp a lot in production and redistributable software.
> We'd like to get bcp to terminate early if it runs for over a certain
> amount of time due to locking/deadlock/...
> Thus, I'm trying something like and it's not working - ideas?
> bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2"
> queryout "s24.txt" -c -U abc -P ewe
A lock timeout causes an automatic attention of the query if it runs
into a situation where the query is waiting on a locked resource, in
this case, for more than 2 seconds. It is not the same thing as a query
timeout. That is, it does not terminate the query if the query runs for
more than 2 seconds even if it encounters locks along the way less than
2 seconds each.
OSQL has the -t<timeout> parameter to tell OSQL that max time for the
query. But BCP has no such parameter as far as I can see, probably
because you wouldn't want to stop a BCP operation in the middle.
If BCP encounters a deadlock situation (probably unlikely given that it
is accessing a single table in most cases), the transaction is
terminated. In a locking situation, the BCP operation could slow down
assuming none of the locks exceed the lock timeout. I'm not even sure a
lock timeout like that would work in BCP.
You can use the TABLOCK hint for BCP which gives you a lock on the table
during the operation. That should eliminate any blocking
For data extraction, you can use try using OSQL which supports what you
want.
David Gugick
Imceda Software
www.imceda.com
|||Thomas Brown wrote:
> We use bcp a lot in production and redistributable software.
> We'd like to get bcp to terminate early if it runs for over a certain
> amount of time due to locking/deadlock/...
> Thus, I'm trying something like and it's not working - ideas?
> bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2"
> queryout "s24.txt" -c -U abc -P ewe
A lock timeout causes an automatic attention of the query if it runs
into a situation where the query is waiting on a locked resource, in
this case, for more than 2 seconds. It is not the same thing as a query
timeout. That is, it does not terminate the query if the query runs for
more than 2 seconds even if it encounters locks along the way less than
2 seconds each.
OSQL has the -t<timeout> parameter to tell OSQL that max time for the
query. But BCP has no such parameter as far as I can see, probably
because you wouldn't want to stop a BCP operation in the middle.
If BCP encounters a deadlock situation (probably unlikely given that it
is accessing a single table in most cases), the transaction is
terminated. In a locking situation, the BCP operation could slow down
assuming none of the locks exceed the lock timeout. I'm not even sure a
lock timeout like that would work in BCP.
You can use the TABLOCK hint for BCP which gives you a lock on the table
during the operation. That should eliminate any blocking.
For data extraction, you can use try using OSQL which supports what you
want.
David Gugick
Imceda Software
www.imceda.com