Wednesday, August 25, 2010

Error: Unknown Return Type, The return types for the following stored procedures could not be detected….(LINQ).

Error: Unknown Return Type, The return types for the following stored procedures could not be detected…..

Error:

The return types for the following stored procedures could not be detected. Set the return type for each stored procedure in the Properties window.

Situations

This error usually comes when you are trying to add your Stored Procedure into DBML (LINQ) file.

Cause

This problem occurs whenever the designer cannot figure out the return type of the SP. This usually happens when the stored procedure has multiple results or uses a temp table. The SQL Server feature that attempts to derive the Meta data of the function reports no columns for the result shape, so the designer defaults to thinking it only has the single int return value.

Solutions

· The one way to get these types of stored procedures to work is to edit DBML by hand or write your own method signature for the procedure in a partial class To Handle multiple record set return from stored procedure by LINQ see the link here.

· The second way is to avoid using #temp Table in your stored procedure, instead of you can use Table type variable like below (@TempTable)

Ex:

DECLARE @TempTable TABLE

(

AttributeID INT,

Value NVARCHAR(200)

)


INSERT INTO @TempTable Select * from Attribute

OR

--Execute SP and insert results into @TempTable

INSERT INTO @TempTable Exec GetAttribute @Id

You can do all operation which you was doing with #Temp table like Join, Insert, Select etc.

12 comments:

bandika said...

Ok, i have a simple SP it runs on SQL2008. I use VS2010 and try to drag and drop the procedure to LINQ file. For other SP's it works ok. Here is the Query

ALTER procedure [dbo].[_p11_Add_Acces_Tert_User] @UserID uniqueidentifier, @Tert nvarchar(20), @UserGUID uniqueidentifier, @OK Bit output, @ErrorMessage nvarchar(4000) output
AS
set @OK = 1
set @ErrorMessage = ''

insert into _p11_Add_Acces_Tert_User (UserID, Tert)
select @UserID, @Tert

Pär Sandgren said...

I sure owe u a beer for this one.
Perfect example, error->cause->solution. Just awesome. I had no idea that temptables could cause those problems.

Jules Bartow said...

Ritesh, Mucho Gracias Amigo. I also owe you a beer. Who'd of thought temp tables defined by

CREATE TABLE #temp (Col A type, ...
(doesn't work)
versus
DECLARE @temp TABLE (Col A type, ...
(does work)

would cause a problem for LINQDataSources?

marcoz80 said...

Hi,
I understand your solution, but this doesn't work in my case.
I have a query that with a @table variable need 20 seconds, with a temp table it takes only 1 second.
The matter is that @table variables doesn't support indexes.
Do you have any suggestion?

Thank You
Marco

Bob Filur said...

@marcoz80
Create a new wrapper stored procedure in which you declare a table variable with the same columns as the temp table in your original sproc.

DECLARE @TempTable TABLE
(
...
)

Then in your wrapper, like Ritesh suggested, call

INSERT INTO @TempTable EXEC spWhatEver ...

Then in LinqToSql, import your wrapper sproc.

sheen said...

Thanx ritesh it works fine
and
@marcoz80
Refer link below to know more about table variable
http://odetocode.com/code/365.aspx

ankit baria said...

Thanks for the useful information.

Andy Todd said...

Quick workaround if you are in a hurry...

In SSMS, temporarily alter your stored proc by cutting out the majority of code so that you just have the final select in place.

In visual studio, refresh and pull the proc into your dbml. Because you've simplified your proc, you shouldn't see any linq-to-sql objections.

In SSMS, paste back in the code you previously cut out and execute so that your stored proc is back as it first was.

Everyone's happy!

Janie Pumphrey said...

I got this error message with a plain "select * from ____ join ____" stored procedure - it turned out to be a typo in one of the table names, but SQL Server didn't say anything about the typo when the stored procedure was altered. So if the temp table or multiple result fix doesn't apply, make sure your stored procedure can run without errors.

Le-roy Staines said...

Here's how I fixed the problem for On SQL Azure I was having this problem until I ran GRANT EXECUTE ON [storedprocedurename] TO [databaseUserOfProject]

Chris Catto said...

Hey Now Ritesh Kesharwani;

This post was very helpful in resolving an error adding a proc to the dbml. Once I changed the temp tables to table variables the issue was resolved. I appreciate your information.
Thank you;
Catto

anusha rose said...

Thank u very much.. Fixed my problem !!!