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