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

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
Why don't you try the otherway?
Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
can improve performance of the bulk copy operation due to reduced lock
contention on the table".
Since this may ensure faster bcp, there will be less locking due to users
transaction.
Thanks
GYK
|||We do use TABLOCK in our production code, and we do see perf. gains and a
reduction in locking contention. However, on our larger tables(10 GB) we see
MS SQL fall over into a state of blocking and deadlock.
We have partitioned our larger tables several times over and can't really do
that anymore to meet business requirements.
"GYK" wrote:

> Why don't you try the otherway?
> Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
> can improve performance of the bulk copy operation due to reduced lock
> contention on the table".
> Since this may ensure faster bcp, there will be less locking due to users
> transaction.
> Thanks
> GYK
sql