FillSchema fails if stored proc uses temp table

[Joe]

Hi,

I am using DataAdapter.FillSchema() so that I can retrieve an XML schema
from any arbritrary stored procedure. I am also using
SqlCommandBuilder.DeriveParameters() before making the call to FillSchema().
Full code is:

public string GenerateSchema(string connectionString, string
storedProcedureName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(storedProcedureName,
connection);
command.CommandTimeout = 0;
command.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(command);
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
DataSet ds = new DataSet();
// line below fails for sproc with temp table
// da.FillSchema(ds,SchemaType.Mapped, "ReportData");
// line below succeeds but takes a long time
da.Fill(ds, "ReportData");
return ds.GetXmlSchema();
}
}
}

The reason for all of this is that I need to get the schema to convert ADO
based Crystal reports to ADO.NET based Crystal reports that require the
schema.

When I call FillSchema() on a stored procedure that does an "insert into
#<temp table>", FillSchema throws a SqlException() with the message "invalid
object name #<temp table>".

I can execute the stored procedure with no problems in Query Analyzer, and I
can execute the stored procedure with a DataAdapter.Fill() from ADO.NET, but
as mentioned above, I cannot execute the stored procedure with FillSchema().

Does anyone have any insights into why I am getting this message and is
there another way I can call FillSchema() or retrieve schema information
without calling Fill(). The latter takes too long and in some cases leads to
timeouts.

I have to keep this fairly generic -- again so I can get any schema for any
stored proc call.

Thanks in advance!