My Software Notes

Useful things I discover

Entity Framework 4 – The selected stored procedure returns no columns

with 10 comments

(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:

  1. 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.
  2. You are selecting from a temp table and EF can’t figure out the shape of the results at design time.
  3. 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:

For #1

  1. 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.
  2. 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.

For #2

  1. Use a table variable instead of a temp table

For #3

  1. 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.)

Advertisements

Written by gsdwriter

November 2, 2010 at 1:07 pm

10 Responses

Subscribe to comments with RSS.

  1. FYI seem add this line on your store procedure and it’s work.

    SET FMTONLY OFF

    ref: http://forums.silverlight.net/forums/p/155683/437649.aspx

    minkbear

    December 16, 2010 at 2:18 pm

    • Hey minkbear,

      Thanks for the suggestion.

      That solution worked in one very, very specific case. I haven’t tried it myself, because I haven’t needed it, but I don’t think it would work in most cases. (Anybody got any experience with using SET FMTONLY?)

      In looking at the link you provided I see a couple of problems with the stored procedure that would not give up its structure to EF:
      1. It used dynamic sql when it didn’t need to.
      2. It had weird selects in it like “select * from ( another query)”. What’s the point in such a statement? (Anybody?)
      3. It used a lot of temp tables. I didn’t spend a lot of time going over it, but usually when I see so many temp tables being used I suspect that the person could have spent a little more time finding ways to write better queries rather than using temp tables so much.

      Having said all that, there was something VERY valuable on that thread. A guy from MS Online Community Support said:

      I did some research on how Visual Studio gets the column information. If you open SQL Server profiler when you click “Get Column Information”, you will find that Visual Studio queries against database to get the column information. One query I notice is that Visual Studio tries to execute the store procedure with all parameters set to be null. I am not sure if this is the way Visual Studio determines whether there’s column information or not. But I suggest you trying to execute your query in this way to see if there’s any column returned by your store procedure. Of course you can monitor the profiler your own to find more useful information.

      That suggests a great way to handle any situation where you have applied the solutions I gave in the article or where you are forced to use temp tables because temp variables won’t do and you still can’t get the columns:

      Stick an “if” statement at the top of your sproc that checks if all parameters are null and if true returns the structure.

      For Example:
      if @param1 is null and @param2 is null then
      begin
        select
          cast('' as varchar(10)) as Column1,
          cast(0 as bit) as Column2,
          cast(0 as decimal) as Column3
      end

      A variation would be to declare variables of the correct data types and put those in the select.

      Some people might regard this solution as a hack, but it could also be looked upon as an adaptation of the “Null Object” or “Special Case” design pattern.

      Thanks minkbear for the input. You got me thinking about this more and you’ve helped provide me with another possible solution. That’s the beauty of blogs and forums: you get to learn so much from interaction with others.

      gsdwriter

      December 19, 2010 at 10:21 am

      • A guy from MS Online Community has done has done a really good job in finding out how VS behaves on the click on a button ‘Get Column Information’…

        Srikanth

        August 20, 2012 at 4:53 am

  2. Thank You This helped

    Jaiju

    November 1, 2011 at 4:32 am

    • Hi, i fixed just now my problem with your suggested solution . Many thanks !!!
      ElricM

      ElricM

      February 6, 2012 at 9:24 am

  3. […] original post on this topic, Entity Framework 4 – The selected stored procedure returns no columns, has been getting quite a lot of hits recently and I realize that of all the solutions I gave on […]

  4. just for the record, the solution that worked for me was replacing the code with the most simplified select statement returning the field names, creating the Function Import then replacing the correct code in the SP. I think the problem I had was related to the fact that I was querying a full-text search and EF did not like that

    Mark Walters

    February 8, 2012 at 4:30 am

    • @Mark Walters

      That’s definitely another successful way to handle it:
      1. create a version of the sproc that EF can extract the metadata from,
      2. import it into your model,
      3. create the function import and then
      4. replace the innards of the sproc with the final code.

      Thanks for posting that.

      gsdwriter

      February 13, 2012 at 8:31 am

  5. Thanks, it was very helpful!

    trinko

    August 30, 2012 at 7:18 am

  6. Spent 2 hours on this issue. Thanks for this post. Saved my finally.

    Suresh

    April 17, 2013 at 5:16 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: