I figured I’d write a blog detailing the nightmare scenario I encountered in regards to Entity Framework and Stored Procedures in SQL Server, and trying to get Entity Framework to generate the metadata.  It all started when creating a complicated search stored procedure, which included subqueries, inner and outer joins, tables variables, etc.  For some reason, Entity Framework wouldn’t generate the complex type associated with the stored procedure result set.  Everytime I tried to create the function import, I get the error: “The Selected Stored Procedure Returns No Columns”.

Doing some googling found that Entity Framework 4 runs the command SET FMTONLY ON before every stored procedure call.  This prevents the stored procedure from actually running against the database, potentially trying to insert data erroneously.  But this command, in some if not many/all scenarios, screws up all of us developers in running stored procedure imports.  So I went through the cyclical process:

  • Script the proc as an alter statement in SQL Server Management Studio (SSMS)
  • Add SET FMTONLY OFF in the proc body (after the as keyword)
  • Run it
  • (I can’t remember if I had to delete/recreate the proc in EF at the moment)
  • Ensure the complex type/function import doesn’t exist.  Delete if it does.
  • Go to EF model, right-click the stored procedure to import, select Add Function Import.  Select Create New Complex Type, and it works, no “The Selected Stored Procedure Returns No Columns” message.
  • Remove the SET FMTONLY OFF line from the proc, and reexecute the alter statement in SSMS.
Table variables get interesting.  Reading in another blog, if a table variable is selected from, the SET FMTONLY OFF is not needed; in fact, in one example, using SET FMTONLY OFF caused the “no columns” message to appear!  So leaving the default behavior worked OK, and I could do this just fine:
create procedure dbo.CustomersSelectAll
as
declare @table table
(
    — fields
)
insert into @table
select — long complex query
select * from @table
whereas, without the table variable, I needed:
create procedure dbo.CustomersSelectAll
as
set FMTONLY off
select — long complex query
Now we get to the most interesting part: when it comes to selecting data from another database, or through a synonym pointing to another database, entity framework doesn’t work at all, unless you use the table variable approach.  Without using a table variable, specifying set FMTONLY on or set FMTONLY off fails to work on either accord. The only way I could even get it to work was to insert the results into a table variable, and send the table variable results to the caller.
Has anyone experienced this personally?  Have you managed to get a cross-database stored procedure to work without a table variable?  The result set was so small that it was OK to use the table variable without any performance degradation.  However, it would be good to understand why this is an issue, so if anyone can leave a comment, I would appreciate it.