EF function imports do not recognize columns returned by StoredProc
Possible duplicate:
EF4 - Selected stored procedure does not return columns
I have a stored procedure that populates the #temp table, does things in its records, and then SELECT writes the records.
The problem is that when I try to create a function import and click [Get Column Information], the results pane displays the message "The selected stored procedure or function does not return columns."
Now I know that it does indeed return columns because if I run it directly from db I get the expected result.
The stored procedure can be summarized 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
source to share
After a furious search I came across the answer: EF4 - Selected stored procedure does not return columns
EF cannot get metadata from a stored procedure that uses dynamic queries or temporary tables. The solution was to either manually create a complex return type OR put
SET FMTONLY OFF
in my saved proc definition. Of course, the danger with the second option is that the stored procedure will execute when Visual Studio makes a metadata call, so ideally this should only be used if the stored procedure doesn't change anything.
UPDATE. An alternative is to test the health of the stored procedure. Another thing you can do is create a dummy stored procedure that returns the columns you want, bind to it, and then execute the actual logic.
source to share