FillSchema fails if stored proc uses temp table




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
using (SqlConnection connection = new SqlConnection(connectionString))
SqlCommand command = new SqlCommand(storedProcedureName,
command.CommandTimeout = 0;
command.CommandType = CommandType.StoredProcedure;
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

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

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

Thanks in advance!

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question