Entity Framework 4: The selected stored procedure returns no columns

Question

In my SP, I mix static SQL and dynamic SQL:

declare @result table
(
 RowNum bigint,
 Id_and_Title varchar(max),
 DaysLeft int,
 cat_id int
);

then, in the dynamic SQL I insert the result to that table:

DECLARE @TSQL NVARCHAR(max);
SET @TSQL = ......

(I use print @TSQL so I'm sure that the query is OK)

insert into @result
EXECUTE sp_executesql @TSQL

select * from @result

but, when I try to import taht SP in VS 2010 Ultimate I see the message as I mentioned in the title. What causes that ? For many times I've occured that error but I still don't know what causes that

at what point do you get the error? when you "Update Model from Database", or when you create the function import, or when you create the complex type... — RPM1984
– RPM1984, Commented Dec 18, 2010 at 0:04
I delete the SP from the model, then I update it from the Database, and when I import that SP I get that error — Tony
– Tony, Commented Dec 18, 2010 at 9:06
inside that dynamic SQL I use my function, that causes the problem, but why ? I don't have a clue... — Tony
– Tony, Commented Dec 18, 2010 at 15:35

Aidan Black · Accepted Answer · 2011-01-26 03:07:48Z

31

Try adding this line to the beginning of your stored procedure:
SET FMTONLY OFF
You can remove this after you have finished importing.

It's mentioned in this article (kind of a long way down) and it worked for me, although I have ended up going back to ADO.NET for the times when I am relying heavily on Stored Procedures. Bear in mind that if your columns change depending on the dynamic SQL your entity model will break.

answered Jan 26, 2011 at 3:07

Aidan Black

5936 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robert Noack Over a year ago

awesome. the article link is now not working for me, but I found the reason is that the select statement doesn't get evaluated until execution, so EF doesn't detect the columns when adding the import. set fmtonly off forces the column information to be sent.

TheRedDwarf Over a year ago

this worked for me. My proc returns a select from a temp table and EF was unable to get the column data. many thanks!

Mauricio Ramalho · Accepted Answer · 2013-11-13 14:39:51Z

1

I don't know exactly what your return type is, but if you have a finite (and relatively small) number of return types, you can create a User-Defined Table Type and return that.

CREATE TYPE T1 AS TABLE 
( ID bigint NOT NULL
  ,Field1 varchar(max) COLLATE Latin1_General_CI_AI NOT NULL
  ,Field2 bit NOT NULL
  ,Field3 varchar(500) NOT NULL
  );
GO

Then in the procedure:

DECLARE @tempTable dbo.T1

INSERT @tempTable (ID, Field1, Field2, Field3)
SELECT .....

....

SELECT * FROM @tempTable

Now EF should be able to recognize the returned columns type.

answered Nov 13, 2013 at 14:39

Mauricio Ramalho

8791 gold badge8 silver badges15 bronze badges

Collectives™ on Stack Overflow

Entity Framework 4: The selected stored procedure returns no columns

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related