"Selected stored procedure or function does not return columns" for a simple sproc

Question

I read a lot of posts on how to handle this. None of this works. Here is my scenario:

I have the following sproc (exactly the same as the script, except that I changed the names of the schemas, tables, and columns to remove them):

USE [DevelopmentDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO alter PROCEDURE [MyCustomSchema].[GetSomethingINeed] AS BEGIN SELECT things.ThingGroupId FROM ReferenceSchema.SomethingNeeded things (NOLOCK) WHERE things.ThingId = 4655946989 END GO 

When I import this structure into an entity, it works fine. But then when I click the "Get column information" button, I get this message:

The selected stored procedure or function does not return columns.

I tried:

• SET FMTONLY OFF
• SET NOCOUNT OFF
• Moving sproc to dbo
• Even easier with sproc ( SELECT cast (1 AS int) as SomeColumn )
• Removing and re-adding sproc (via the model browser)
• Restarting Visual Studio.

Could this be a SQL Server 2012 issue?

Answer 1

I get it. My connection string in app.config was configured to use an SQL user.

This user has limited permissions. When I changed it to use the built-in protection, it all worked fine.

It would be nice if the Entity Framework informed me that it failed, but now I know and will have the correct permissions ...

Answer 2

Change it to read more ...

 SELECT SomethingNeeded.ThingGroupId FROM ReferenceSchema.SomethingNeeded WHERE SomethingNeeded.ThingId = 4655946989 

EF does not understand your nickname.

And there is absolutely no reason for (NOLOCK)

Also, make sure the table exists.

Answer 3

I had the same problem and did not find a solution among all the places mentioned here and elsewhere. However, adding a complex type manually and setting it as a result of the procedure import function always works. This is 5 minutes of work, the only condition is that the properties must correspond to the columns returned by the procedure by type and by name. You can do this from the model browser or, of course, manually edit the edmx file.