CODEWITHSUNDEEP
.netentity-frameworkvisual-studio-2012entity-framework-5sql-server-2012
Vaccano
Vaccano

Reputation: 82341

"The selected stored procedure or function returns no columns" for a simple sproc

I have read a lot of the posts on SO about how to deal with this. None of that is working. Here is my scenario:

I have the following sproc (exactly as scripted, except I changed schema, table and column names to de-identify it):

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 that into entity framework it seems to work fine. But then when I click on the "Get Column Information" button, I get this message:

The selected stored procedure or function returns no columns.

I have tried:

Could this be a SQL Server 2012 issue?

Upvotes: 4

Views: 3341

Answers (3)

Mladen Nikolov
Mladen Nikolov

Reputation: 191

I had a same problem and didn't find a solution among all mentioned here and on other places. However, adding complex type manually and set it as a result of the function import of the procedure allways works. It is 5 minutes work, only condition is that the properties have to match the columns returned by the procedure by type and by name. You can do it from the model browser or of course manually edit edmx file.

Upvotes: 1

Vaccano
Vaccano

Reputation: 82341

I figured it out. My connection string in app.config was setup to use a SQL User.

That user has limited permissions. When I changed it to use integrated security it all worked fine.

It would be nice if Entity Framework would let me know that it failed, but now I know and will make sure I have the right permissions...

Upvotes: 2

Jasmine
Jasmine

Reputation: 4029

Change it to read more like this...

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

The EF doesn't understand your alias.

And there is absolutely no reason for (NOLOCK)

Also, make sure that table exists.

Upvotes: 2

Related Questions