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, Dec 18 '10 at 00: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, Dec 18 '10 at 09:06
inside that dynamic SQL I use my function, that causes the problem, but why ? I don't have a clue... — Tony, Dec 18 '10 at 15:35

score 31 · Accepted Answer · answered Jan 26 '11 at 03:07

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 '11 at 03:07

Aidan Black

593
6
17

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. – Robert Noack Feb 20 '13 at 19:07
this worked for me. My proc returns a select from a temp table and EF was unable to get the column data. many thanks! – TheRedDwarf Sep 25 '13 at 15:18

score 1 · Answer 2 · answered Nov 13 '13 at 14:39

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.

Entity Framework 4: The selected stored procedure returns no columns

2 Answers2

Linked