exception problem with odbc and C++ causes bizarre database behavior

Question

I have a C++ application (compiled with VS2003 running on windows 2000 server against SQL 2000 database) that ran fine for years and now is crashing.

My logging shows that the database connection (ODBC using the SQL Server driver) will be working fine (read and update database) then after an exception throws (and is caught/logged but no real detail) the connection can read data from the database but can no longer update the database.

So I see a thread is reading the next row to process, sending data out a socket to another server, tries to update the record as processed and BOOM.

The only error info I get back from the failed query is:

[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.  [37000]

followed by a blank like where you would normally see the reason the query failed (bad syntax, table not found, whatever, but this time nothing).

This code processes tens of thousands of records, may run for months and shortly after this thread (always seems to be the first victim, may just be the busiest and gets whacked first) starts choking all the other connections appear to have the same problem - read is allowed but updates throw this exception.

I got a quick look at the task manager the last time (it gets bounced quick so I can't examine for long) and the app was using the typical 5 to 6 Meg of RAM and the other resources looked normal.

Other applications are using the database and have no errors during this time.

I can't find anything online where you get this reported error result when the query is valid...

HELP!

Answer 1

From the error, "[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. [37000]" I assume the ODBC Driver is trying to prepare a statement. This has caused us lot of erratic problems.

In the definition of the ODBC Data Source, uncheck the "Create temporary stored procedures for prepared SQL ... " option, and see if this fixes the error.

Answer 2

Any chance that the update statement is writing data too large for its field or null when nulls aren't allowed?

Perhaps you could log the updates to a file to determine if the problem is data-driven.

I'm not sure if SQL Server's ODBC driver uses ADO, but I think that ADO has a leak in its update and insert code that will gradually consume all of your memory. If you get the crash consistantly after approximately the same number of records, you may want to investigate.