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

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.
TomWhy 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

BCP loading failing on timeout error

Hi,

I am trying to load data to SQL server 2005 table using BCP from Cognos data warehouse tool. This works most of the cases but adhocly fails or delays BCP loading considerably.

Failure is every time with timeout error as below

[INTERNAL - 18:56:57] Mem(M): 0.0 [Peak=0.0] (Ref=0.0 Domain=0.0 Pool=0.0)
[DETAIL - 18:56:57] Data Stream row limits: sample rate 1, max rows 0
[DETAIL - 18:56:58] Data Source 'S_AS400AL1_F59125': 7164 physical (7164 logical) rows read, 7164 used
[PROGRESS - 18:56:59] Acquisition: 7164 accepted, 0 rejected
[PROGRESS - 18:56:59] Delivery : 7164 direct, 0 summary/merge, 7164 total
--Delivery 'S_AS400AL1_F4101BCP': BCP Output file--
SQLState = 08001, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to complete login process due to delay in opening server connection
DS-SSLOAD-E060: Delivery 'S_AS400AL1_F4101BCP': BCP process failed (status 1)
DS-BUILD-E005: Delivery 'S_AS400AL1_F4101BCP': failed.
[PROGRESS - 18:58:04] Build 'S_AS400AL1_F4101_Dim_NetChange' Failed
databuild -- failed (21-Apr-2007 18:58:04)

Other cases a Builds using BCP generally takes 25-30 mins but some days takes over an hour.

Has anyone faced similar problems? Please do suggest.

Regards,

Paul Jacob

Hi Paul,

did you use a remote connection?

What about using sqlcmd?

sqlcmd -S <machine name>\SQLEXPRESS ...

CU

tosc

|||There are plenty of builds that gets executed as part of daily etl and hence we have to use cognos tool to extract data from source and load to detination using bcp.|||

Hi Paul,

are you using Cognos 7.2? - The bcp delivery in 7.2 will create a temporary text file in default
data directory, there for make sure you have enough disk space.

....

Which version of SQL-Server and SP are you running?

|||

Yes I am using Cognos 7.3 version. Drive Space for temporary isquite high.

SQL Server 2005

|||

Hi Paul,

what about the version of MDAC ? . . . for both!

CU

tosc

|||Sorry didnt get what MDAC is and where could I find it. Paul|||

Hi Paul,

MDAC = Microsoft Data Access Components this component must be the same for both DS-Server and SQL-Server.

How to check for MDAC version

Are the servers in the same subnet?

CU

tosc

|||

version number is 2.82.1830.0

app. Server and database are on the same box

|||

Hi Paul,

hosting app. server and database on the same box might be a problem for performance.

Did you check it out with perfomance monitoring by bcp upload?

How about using Cognos Data Manager 8.1.1?

CU

tosc

|||

Hi Paul

This error is due to the Output files created by EP not being cleared when a publish completes.

Delete the files in the user profile temp location of the user assigned the run congos ep.

E.g Documents and settings\UserName\local settings\temp

in particular the files named.

GUID.BAT

GUID.out

Re run the Publish for EP and the BCP process will work.

Also make sure your datasource in ep is set to use the sql 2k5 native client rather than sqloledb

cheers

Cognoid