sporadic ASP.NET data error: "Cannot find table 0"

Question

Having deployed a new build of an ASP.NET site in a production environment, I am logging dozens of data errors every second, almost always with the error "Cannot find table 0." We use datasets and frequently refer to Table[0], and while I understand the defensive coding practice of checking the dataset for tables before accessing Table[0], it's never been a problem in the past. A certain page will load fine one second, and then be missing one of its data-driven components the next. Just seeing if this rings a bell for anyone.

More detail: I used a different build server this time, and while I imagine the compiler settings are the same on both, I have a hard time thinking that there's a switch that makes 50% of my database calls come back with no tables. I also switched the project to VS 2008, but then reverted all of those changes when I switched back to VS 2005. I notice that the built assembly has a new MyLibrary.XmlSerializers.dll, where it didn't used to, but I also can't imagine that that's causing all the trouble. (It also doesn't fall down on calls to MyLibrary, or at least no more than any other time.)

Updated to add: I've discovered that the troublesome build is a "Release" build, where the working build was compiled as "Debug". Could that explain it?

Rolling back to the build before these changes fixed it. (Rebooting the SQL Server, the step we tried before that, did not.)

The trouble also seems to be load-based - this cruised through our integration and QA environments without a problem, and even our smoke test environment - the one that points to production data - is fine under light load.

Does this have the distinguishing characteristics of anything you might have seen in the past?

Answer 1

Bumping this old question because we have encountered the same issue and perhaps our solution would give more insight in what causes this.

Essentially this problem occurs in a production environment that is under very heavy load in a Windows service that uses multiple threads to process several jobs simultaneously (100 users use the same DB via ASP.NET web app and there are about 60 transactions/second on older hardware with SQL Server 2000).

No variables are shared, that is connections are opened anew, transaction is started, operations executed, transaction committed and connection closes.

Under heavy load sometimes one of the following exceptions occurs:

NullReferenceException: Object reference not set to an instance of an
object.
at System.Data.SqlClient.SqlInternalConnectionTds.get_IsLockedForBulkCopy()

or

System.Data.SqlClient.SqlException:
The server failed to resume the transaction. Desc:3400000178  

or

New request is not allowed to start because it should come with valid  transaction descriptor  

or

This SqlTransaction has completed; it is no longer usable

It seems somehow the connection that is within the pool becomes corrupted and remains associated with previously used transactions. Furthermore, if such connection is retrieved from pool then sqlAdapter.Fill(dataset) results in an empty dataset, causing "Cannot find table 0". Because our service would retry the operation (reading job list) on failure and it would always get the same corrupt connection from the pool it would fail with this error until restarted.

We removed the issue by using SqlConnection.ClearPool(connection) on exception to make sure this connection is discarded from the pool and restructuring the application so less threads access the same resources simultaneously.

I have no clue who exactly caused this issue so I am not sure we have really fixed that, maybe just made it so rare it had not occurred again yet.

Answer 2

I've fought precisely this error message before. The key is that an underlying data method is swallowing a timeout exception.

You're probably doing something like this:

var table = GetEmployeeDataSet().Tables[0];

GetEmployeeDataSet is swallowing an exception, probably a timeout exception, which is why it only happens sporadically - it happens under load. You need to do the following to fix it:

1. Modify the underlying code to not swallow the exception, but rather let it bubble up to the next level so you can identify it properly.
2. Identify the query(s) causing the problem, and then rewrite, reindex, denormalize or throw hardware at the problem. See this for more info: System.Data.SqlClient.SqlException: Timeout expired