[Solved] SQL Server BCP Errors

[rory]

Hi,

We've been experiencing intermittent errors during the BCP phase of some decisionstream processes.

The behaviour we see is that one build will fail, then the following day might run successfully, or a different build will fail.

The SQL server has essentially been rebuilt since we first started getting these errors, so while hardware errors might explain what's happening, our infrastructure team are sceptical of a problem with the server.


[PROGRESSÃ,  Ã, - 01:42:48] DeliveryÃ,  Ã, : 231352 direct, 0 summary/merge, 231352 total
-----Delivery 'ET_ARPaymentDetail_BCP': BCP Output file-----
SQLState = S1T00, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Timeout expired
------------------------------------------------------------

DS-SSLOAD-E060: Delivery 'ET_ARPaymentDetail_BCP': BCP process failed (status 1)

DS-BUILD-E005: Delivery 'ET_ARPaymentDetail_BCP': failed.

Does anyone have suggestions as to what might be occurring and any available workarounds?

Thanks,

Rory.

[rory]

Hi,

Bit more info which may help.

We're using v7.2.

The behaviour suggests there may be something like a memory leak i.e. if we reboot the servers (DS and SQL which are on two separate boxes) everything runs fine for a number of days (say 5-6) then we get one BCP error, then the next night another (different) one, then the next night a dozen.

The fact that rebooting servers fixes the problem suggests to me that we're talking SQL Server config issues or application memory leaks.Ã,  Is it common practice to regularly reboot servers when using DS?

Regards,

Rory.

[CoginAustin]

have you tried using the OLE-DB drivers for the connection? I honestly do not know how you are doing BCP via OBCD anything as DS doesn't even present you with connections that are ODBC.

[rory]

Hi,

Thanks and a great question - didn't even notice that.  The DS connections are all OLE-DB, but having checked the BCP doco I found...

"Note  The bcp utility is written using the ODBC bulk copy application programming interface (API). Earlier versions of the bcp utility were written using the DB-Library bulk copy API."

I have no idea of what the implications of this may be.  However, the same software on the dev server seems fine, so unless there is a difference in the configuration (which I can't spot) I would have expected it to work OK.  There seems to be no relationship to the size of the build, and as I said the build which fails today may run successfully tomorrow.

Since my first post, we've had a few spooky things on the server like a blue screen error a couple of hours after reboot, and startup errors (which I had been advised were corrected).  Hardware folk are examining.

Regards,

Rory

[JGirl]

Sorry for my flaky post (can you tell im not a techie?).....Don't know if it will help or not, but.....

You might want to do a search on the server for other BCP.exe files for some hints as to what could be causing the issue.

I came across a similar problem, caused by the fact that the server had another bcp.exe file installed on it for sybase open client, in addition to the bcp.exe for MSSQL Server.  The PATH environment variable might point you in the right direction.

In my case, the solution was to rename the sybase BCP.exe to something else and I had no more cognos problems (But...I have no idea of what impact this would have on whatever sybase process uses it)....

Good Luck!
J

[rory]

Thanks J,

We did a clean install on the server, and have only the (standard) two copies of the BCP.exe file in the default directories.

We have found that the tempdb was set up to increment in 1M blocks.  We've just changed this to 10% and so far it's working.  If our manual job completes, and the overnight run is successful, hopefully I can close this.

Thanks to all...

Rory.

[rory]

Hi,

Looks like this is resolved. After moving the boxes to the same subnet, making efficiency improvements and a number of other things, we found that the DS box and the SQL Server box were running different versions of MDAC.  The SQL server was on 2.8 SP2 and the DS box was on 2.8 RTM.

Now we're not 100% certain that the MDAC was the whole problem, but the error's gone away...

Thanks again!