Entiy Framework Add Function Import - Get Column Information returns nothing..

I needed to update a sql stored procedure in our entity data model. In the Model Browser I clicked "Update Model from Database" and then double clicked on the SP under functions import to open the "Edit function import" wizard so I could make the complex return type from the SP. To my surprice the import wizard told me that "The selected stored procedure returns no columns"? I knew for a fact that the SP returned several rows.

After a little Google searching I found a solution in this forum post from "Brian”: Microsoft forum

Quote: "

it seems Entity Framework will try to get the columns by executing your Stored Procedure, passing NULL for every argument. You can see this by putting a trace on your SQL Server.

So - first make sure your S_P will return something under these circumstances. Note it may have been smarter for Entity Framework to execute the Stored Proc with Default Values for arguments, as opposed to NULLS. Never mind - nothing we can do about that!

However - before trying to run the Stored Procedure, ER does this

SET FMTONLY ON

This will break your stored procedure in various circumstances, in particular, if it uses a temp table.

So, add to the start of your Stored Procedure:

SET FMTONLY OFF;

This worked for me - hope it works for you too.

brian

"

One other work around or maybe a better solution is to not use temp tables at all, if you don’t need too index those tables that is.

If you use table variables, the SET FMTONLY ON; option will not break in your stored procedure. Performance wise, except indexing option on temp tables, they seem quite the same.

Ref this article:

SQL Rocks Temp Tables vs Table Variables