EF4 – The selected stored procedure returns no columns

by

EF doesn’t support importing stored procedures which build result set from:

  • Dynamic queries
  • Temporary tables

The reason is that to import the procedure EF must execute it. Such operation can be dangerous because it can trigger some changes in the database. Because of that EF uses special SQL command before it executes the stored procedure:

SET FMTONLY ON

By executing this command stored procedure will return only “metadata” about columns in its result set and it will not execute its logic. But because the logic wasn’t executed there is no temporary table (or built dynamic query) so metadata contains nothing.

You have two choices (except the one which requires re-writing your stored procedure to not use these features):

  • Define the returned complex type manually (I guess it should work)
  • Use a hack and just for adding the stored procedure put at its beginning SET FMTONLY OFF. This will allow rest of your SP’s code to execute in normal way. Just make sure that your SP doesn’t modify any data because these modifications will be executed during import! After successful import remove that hack.

More Related Contents:

  1. What’s the difference between a temp table and table variable in SQL Server?
  2. Local and global temporary tables in SQL Server
  3. When should I use a table variable vs temporary table in sql server?
  4. Which are more performant, CTE or temporary tables?
  5. T-SQL Dynamic SQL and Temp Tables
  6. error when inserting into table having instead of trigger from entity data framework
  7. Check if a temporary table exists and delete if it exists before creating a temporary table
  8. Why can’t Entity Framework see my Stored Procedure’s column information?
  9. Where clause not working in sql server
  10. Compare the values of column in SQL Server
  11. T-SQL stored procedure that accepts multiple Id values
  12. How do I escape a percentage sign in T-SQL?
  13. Get list of databases from SQL Server
  14. SQL Server: Isolation level leaks across pooled connections
  15. CTE to get all children (descendants) of a parent
  16. How can I automate the “generate scripts” task in SQL Server Management Studio 2008?
  17. EXEC sp_executesql with multiple parameters
  18. SQL Server – Dynamic PIVOT Table – SQL Injection
  19. How to add custom attributes to SQL connection string?
  20. SSIS reading LF as terminator when its set as CRLF
  21. Compute MD5 hash of a UTF8 string
  22. Sort Bullets in Database
  23. How to retrieve field names from temporary table (SQL Server 2008)
  24. How do I remove non-breaking spaces from a column in SQL server?
  25. Why is casting from float to varchar being rounded in SQL Server?
  26. SQL Server query to find all current database names
  27. Data generators for SQL server? [closed]
  28. SQL Server Maximum rows that can be inserted in a single insert statment
  29. sql geography to dbgeography?
  30. SQL Server 2008 – order by strings with number numerically

Leave a Comment