How to get C # Query component to recognize columns returned by data from temporary table in sql stored procedure

Question

I created a stored procedure like the one below (I use this shortened version to try to understand our problem).

CREATE PROCEDURE bsp_testStoredProc AS BEGIN CREATE TABLE #tmpFiles ( AuthorName NVARCHAR(50), PercentageHigh INT ) -- Insert data into temp table SELECT AuthorName, PercentageHigh FROM #tmpFiles ORDER BY PercentageHigh DESC DROP TABLE #tmpFiles RETURN 0 END 

From my C # code in VS2008, I am trying to use the Query component with the Use Existing Stored Procedure parameter to connect it to a DataTable / DataGridView to display the results.

However, since I select from a temporary table, in the properties of the Query component, Visual Studio does not display the columns returned from the stored procedure. I assume that it has problems defining the data types used, since SP is not based on a real schema.

Connecting to various stored procedures that are fetched from real tables really displays the columns correctly.

Does anyone know about this? Is there any hint I can add somewhere to explicitly indicate what data will be returned?

Thanks in advance.

Answer 1

For information, you can use a "table variable" rather than a temporary table (for example, @FOO, not #FOO) - this may help a little, and it certainly helps to solve several problems with tempdb.

With temporary tables - there is no way to explicitly declare an SP schema. Perhaps I suggest using a simplified version of SP when creating your wrapper classes - i.e. Perform a trivial SELECT of the correct form.

Alternatively, I would use LINQ to use UDF, which has an explicit schema.