What causes DbDataAdapter.Fill to fill a DataSet with the incorrect number of columns?

Question

I have a windows service connecting to a SqlServer database on the local box. This works fine most all the time. At a large customer, however, the database connectivity gets corrupted for some rare and unknown reason. When this happens, calls to DbDataAdapter.Fill return a DataSet with a different number of columns than in the select command passed in (validated by trace logs showing the sql expected, and the number of columns returned). For the first few errored DataSets it appears the adapter instance is returning results from the previous query. This is happening even though the adapter objects are unique instances, and the connection/adapter creation calls are thread protected (as DbProviderFactory is not thread safe).

Has anyone experience this or something similar? Does anyone know what could cause this? At this moment in time, I'm having a hard time thinking up new ideas for a cause of this.

Answer 1

Are you filling a strongly-typed dataset (STDS)? If you are filling an STDS, if the column names and types do not match up, you will get tables with some phantom columns... namely those that were defined in the schema of the STDS, and not in your returned recordset.

Answer 2

Try turning connection pooling off for a while, see if that eases the problem. I have seen this question asked somewhere else, I think connection pooling was involved but I can't be sure. I'll see if I can find the other forum, or maybe someone else remembers it as well?