Run-time error '40002' S1T00:[Microsoft][ODBC SQL Server Driver] Timeout Expired

ccsenet
ccsenet used Ask the Experts™
on
Hi,
I am testing some applications which are written in VB with backend as SQL Server 2000. This applications runs for hours since the applications has to deal with huge text files (in GBs). they usually bulk insert these huge files into Database and then does some operations on the DB. While testing these applications i am recieving three types of Errors (Each from different application):

Error 1:
Run-time error '40002' S1T00:[Microsoft][ODBC SQL Server Driver]Timeout Expired

Error 2:
Bulk Insert Fails. Column too long. Make sure field terminator and row terminator are specified correctly. Run-time error -21472117900(80040e14)

Error 3:
Could not bulk insert because file 'e:\code\abc.txt' could not be opened. Operating system error code 32 (The process cannot use this file because it is being used by another process.)

These two errors are generated from two different Applications. please send me an appropraite cause of these errors.

Regards,
CCSE
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006
Commented:
1.  The odbc connection waits for a certain period of time fo the query to finish executing
2.  The file that you are reading is missing row terminators in certain locations - or they are wrong, ie you wee expecting a carraige return and line feed and only had a line feed
3.  You have opened this file at the same time (ie attempting to load the file and then tried to load it again)
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
>Error 1:
>Run-time error '40002' S1T00:[Microsoft][ODBC SQL Server Driver]Timeout Expired

well, the query you tried to execute simply took longer than the command timeout (usually 30 seconds).

solutions:
* tune the query
* run the query in the background (job), and let the application continue
* increase the timeout

>Error 2:
>Bulk Insert Fails. Column too long. Make sure field terminator and row terminator are specified correctly. Run-time error -21472117900(80040e14)

that should be "obvious", however the reason is usually
* the file having an field that is longer than the table column
* the field/row terminators are not specified correctly
* the input file content is not following the rules for the import

>Error 3:
>Could not bulk insert because file 'e:\code\abc.txt' could not be opened. Operating system error code 32 (The process cannot use this file because it >is being used by another process.)

Again, the error message is clear: some other process has still a lock on the file. with tools like "handle.exe" (from www.sysinternals.com) you can try to identity the application/process, and kill that one.
Usually, this happens when the file is being copied to the destination and while it's still being copied, a process like bulk insert is already trying to use it.


In all 3 cases, these are possible causes, but not necessary the excat problem in your case.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial