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

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
LVL 1
ccsenetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raynard7Commented:
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)
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.