Entity Framework 4 – The selected stored procedure returns no columns
(2011-11-04: I noticed a lot of activity on this post and I realized that the solution I use most is in one of the comments way down the page. So I have written an additional article here, Entity Framework 4 – The selected stored procedure returns no columns (part 2), to highlight it. I suggest you do read the solutions given below because they may apply and could help improve your stored procedure but if you are in a hurry the part 2 article gives you a fast fix.)
You create a stored procedure, you bring it into your entity model, you start to create a function import, you click the “Get Column Information” button and you get “The selected stored procedure returns no columns.”
The reasons I’ve seen for this are:
- You are returning your result set using dynamic sql, so EF can’t figure out at design time what the shape of the results will be.
- You are selecting from a temp table and EF can’t figure out the shape of the results at design time.
- Your stored procedure crosses databases and although you have all the rights you need in the database containing the sproc, you don’t have the necessary rights to the database objects in the other database. E.g., you don’t have select rights on a table in the other database.
The solutions I’ve found are:
- Alter your sproc so the result set structure can be figured out at design time. E.g., put the results into a table variable and select from that.
- Create a dummy sproc with the same name and signature as the final sproc that contains a select statement which creates a result set with the desired structure. (E.g., select cast(1 as int), cast(“a” as varchar(20)) … etc.) Refresh your entity model and add the function import – EF will see the structure and you can create the Entity to receive the results. Now replace the dummy sproc in the database with your final version.
- Use a table variable instead of a temp table
- Give the user that is calling the sproc the correct rights in the other database or databases.
I hope people find that useful.
(2010-12-19: See my reply to minkbear’s comment below for another possible solution.)