SqlDataAdapter.FillSchema with stored procedure that has temporary table

I'm running into a problem similar to this question here (Display DataType and Size of Column from SQL Server Query Results at Runtime) which has not had a solid solution.

I wonder if anyone has found a solution or a work around for it. I can use SqlDataAdapter.Fill() with no problem, but .FillSchema() will report an error that the temp table I create in the stored procedure doesn't exist.

My goal is to be able to fill the DataTable with data and the schema from the source.

I ran into this same issue with SqlDataAdapter. I was able to resolve the issue by changing my code to use SqlDataReader instead.

If you do not need to modify the schema at all

The following code segments will work fine if you do not need to manipulate the schema of your DataTable object and you also plan to use the data returned from the stored procedure.

var command = new SqlCommand("stored procedure name")
{
    CommandType = CommandType.StoredProcedure 
};

var adapter = new SqlDataAdapter(command);
var dt = new DataTable();

adapter.Fill(dt);

var command = new SqlCommand("stored procedure name")
{
    CommandType = CommandType.StoredProcedure 
};

var reader = command.ExecuteReader();
var dt = new DataTable();

dt.Load(reader);

Both methods will populate a data table with column names and types that correspond to the names and types returned from the stored procedure call. If you need information about the schema, you can access it via the DataTable.Columns property.

If you need to modify the schema

If you need schema information only, or if you need to manipulate the schema in the DataTable object before populating it with data, the following method will work, even if SqlDataAdapter.FillSchema does not.

var command = new SqlCommand("stored procedure name")
{
    CommandType = CommandType.StoredProcedure 
};

var reader = command.ExecuteReader();

var schemaTable = reader.GetSchemaTable();

SqlDataReader.GetSchemaTable() will return a DataTable object with column metadata populated as rows.

You can enumerate the results to build and/or manipulate columns in a DataTable object that will hold the records returned by the SqlDataReader.

In my case, I needed every column to be a string to avoid any formatting "help" from Microsoft Excel when exporting the data to a spreadsheet. As an example, my code looks like this:

var dt = new DataTable();

foreach(DataRow row in schemaTable.Rows)
{
    dt.Columns.Add((string)row["ColumnName"], typeof(string));
}

dt.Load(reader);

SqlDataAdapter.FillSchema with stored procedure that has , i wonder if has found solution or work around it. can use sqldataadapter.fill() no problem, .fillschema() report error temp table create in stored  In order to use stored procedures containing temporary tables, you have to turn off FMTONLY in a seemingly never executed statement. Thus the data schema of temporary tables can be read by the SqlDataSource Wizard as each state of IF-ELSE statements is evaluated when the SQL query is run by the SqlDataSource Wizard.

Stored procedures and temp tables generally don't mix well with strongly typed implementations of database objects.

If you change your #temp table to a table @variable that should solve your issue.

An example of how to change from temp table to table variable would be like:

    CREATE TABLE #tempTable (ID INT, Val VARCHAR(50))

to

    DECLARE @tempTable TABLE(ID INT, Val VARCHAR(50))

SQLDataAdapter filled from temporary table, VS has only a limited ability to retrieve tabular metadata from stored procedures, and as you have discovered, temp tables are beyond those limits. You should DataAdapter.FillSchema under the covers to retrieve schema Stored procedures and temp tables generally don't mix well with strongly typed implementations of database objects. If you change your #temp table to a table @variable that should solve your issue. An example of how to change from temp table to table variable would be like: CREATE TABLE #tempTable (ID INT, Val VARCHAR(50)) to

If you do not need DataSet, but just one DataTable, you can use

DataTable dt= new DataTable();

dt.Load(cmd.ExecuteReader());

For a DataSet, you must at least tell the names of the tables (this means the number) that the SP is returning.

DataSet ds = new DataSet();
SqlDataReader sdr = cmd.ExecuteReader();
ds.Load(sdr, LoadOption.OverwriteChanges,"Table1");

can't create tableadapter from stored procedure with temp table , Using VS2010 sp1. I have a SQL 2008 stored procedure that uses a temp table to hold some temporary data that is then joined to an existing  You can copy the results set from a stored procedure to a local temp table in a three-step process. In the first step, create a fresh copy of the stored procedure with a select statement that generates a results set whose output you want to persist. In the second step, create a local temp table outside of the stored procedure.

FillSchema from Stored Procedure, I can use SqlDataAdapter.Fill() with no problem, but .FillSchema() will report an error that the temp table I create in the stored procedure doesn't exist. My goal is​  Though you can access a temporary table inside another connection when creating a stored procedure, you cannot access a temporary table when “executing” a stored procedure inside another connection. To execute a stored procedure that accesses a temporary table, you have to be inside the connection that created the temporary table.

DataAdapter.FillSchema Method (System.Data.Common), I've got a stored procedure that creates a temporary table and then does a SqlDataAdapter da = new SqlDataAdapter(oCommand); da. This posting is provided "AS IS" with no warranties, and confers no rights. > Hi,. How can I use an IF statement in my stored procedure to check if my temporary table has rows? I want to check if #TempDataTable has rows then I would do either of the two statements, insert or update depending on the output. I did the following:

Using SQL Server's Table Valued Parameters, If a unique clustered index is defined on a column or columns in a SQL Server table and the primary key constraint is defined on a separate set of columns, then​  If you do this, always declare your #temp table at the top of the function. SQL will force a recompile of your stored proc when it sees the create table statement.so if you have the #temp table declaration in the middle of the stored proc, you stored proc must stop processing and recompile.

Comments
  • you did not like that approach?