SqlDataAdapter.Fill returns results of previous command

[kenn]

Here's some psuedocode, (the actual code is a little more complex),
involved in a curious occurence in our application.


// Execute stored procedure get_page_imposition
SqlCommand command = new SqlCommand("get_page_imposition",
new System.Data.SqlClient.SqlConnection("user
id=sa;database=AppDB;server=(local);Connect Timeout=30"));
command.CommandType = CommandType.StoredProcedure;

param = command.Parameters.Add("@JobName", SqlDbType.NVarChar);
param.Value = "TEST 8 PAGE -040312-1";

SqlDataAdapter adapter = new SqlDataAdapter(command);

DataTable table = new DataTable();

command.Connection.Open();

try
{
adapter.Fill(table);
}
catch (Exception e)
{
log.Error(e);
}
finally
{
command.Connection.Close() ;
}


This code executes successfully at certain points in the application,
but at one point an SqlException is thrown by the Fill() method with
the message
"A severe error occurred on the current command. The results, if
any, should be discarded."

I don't know what the cause of this exception is yet, but the reason
I'm posting is because of what happens next.

I break within the exception block and notice that table.Rows is
empty. Also, an SQL Profiler trace which is running shows that the
stored procedure was indeed executed on the database. The exception
is logged and the app continues executing. Shortly after, code
resembling the following is executed, (again this is a simplified
version of the actual code):

// Fill a DataTable with from a query.
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand
= new SqlCommand("Select * from Run where Id = 2",
new System.Data.SqlClient.SqlConnection("user
id=sa;database=AppDB;server=(local);Connect Timeout=30"));

DataTable table = new DataTable();

adapter.SelectCommand.Connection.Open();

adapter.Fill(table);

adapter.SelectCommand.Connection.Close(); // in the actual code
this is called in a finally block


I break at the Fill() statement. At this point I notice two things:

1. The trace shows that the last action on the db was the stored
procedure called from the first code snippet listed above.

2. I know that there is exactly one entry in the Run table and it has
an Id of 2. I can verify this via Query Analyzer.

After stepping past the Fill() statement, table contains the results
of the stored procedure get_page_imposition, not the result of the
query on the Run table. The trace has not changed except for a single
"exec sp_reset_connection" has been appended. There is no trace of
the select on the Run table.

This is just one incarnation of the problem. It doesn't happen every
time, it happens sometimes on queries to tables other than Run,
sometimes results are from stored procedures other than
get_page_imposition, sometimes they are from queries on other tables.
I can make it happen within our full application, but I've been unable
to reproduce it in a smaller, controlled application.

The database is static, i.e. while performing these tests, no changes
are made to the data or schema.

If I turn off connection pooling, I can't produce the problem.

It seems as though a pooled SqlCommand is causing the adapter to fill
a table with data from a previous command.
Has anyone else experienced this? Any ideas?

 

[kenn]

It seems the problem is caused by calling the undocumented extended
procedure, master..xp_subdirs in a certain manner in a user defined
stored procedure.

The procedure resembles this:

CREATE PROCEDURE call_xp_subdirs

AS

CREATE TABLE #SubDir (subdirectory VARCHAR(255))
insert INTO #SubDir
exec master..xp_subdirs 'z:\some\nonexistant\directory\'

-- etc.

GO

After calling that, ADO.NET gets hosed.

I guess that's what you get for using undocumented stored procedures.

This is .NET 1.1 on SQL 2000, by the way.