Possible Duplicate:
EF4 - The selected stored procedure returns no columns
I have a stored procedure which populates a #temp table, does things to its records and then SELECTs the records.
Problem is, when I try to create a function import and click on [Get Column Information], the results pane shows the message "The selected stored procedure or function returns no columns".
Now, I KNOW for a fact that it does indeed return columns because if I run it directly from the db, I get the expected resultset back.
The stored procedure can be summarised as follows:
SELECT P.PersonID, P.Surname, P.NickName, P.DateofBirth
INTO #SeriesCompleted
FROM
Table1 T (NOLOCK)
INNER JOIN
Table2 P (NOLOCK) ON T.PID = P.PID
;
Select r.PID, SUM(rt.Distance) 'Distance'
INTO #Distance
FROM
#SeriesCompleted sc
inner join table3 rsr (NOLOCK) on rsr.SeriesId = sc.SeriesId
inner join table4 r (NOLOCK) on r.PID = sc.PID
inner join table5 rt (NOLOCK) on rt.RouteID = r.RouteID
GROUP BY r.PID;
UPDATE #SeriesCompleted
SET Distance = d.Distance
FROM #SeriesCompleted sc
INNER JOIN #Distance d on d.PID = sc.PPID;
--Here is where the result is returned.
SELECT distinct sc.PersonID, sc.NickName, sc.Surname, sc.DateofBirth, sc.NumberFinished, sc.Distance
FROM #SeriesCompleted SC
After googling furiously, I came across the answer: EF4 - The selected stored procedure returns no columns
EF cannot get metadata from a stored proc that uses dynamic queries or temp tables. The solution was to either manually create the complex returned type OR put
SET FMTONLY OFF
in my stored proc definition. The danger with the second option, of course is that the stored proc will be executed when Visual Studio executes the metadata call, so this would ideally only be used if the stored procedure doesn't change anything.
UPDATE: An alternative is to make sure that the stored procedure actually works. Another thing you could do is to create a dummy stored procedure that returns the columns you want, bind to it and then do the actual logic.