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.