LINQ to SQL — Can't modify return type of stor

Question

When I drag a particular stored procedure into the VS 2008 dbml designer, it shows up with Return Type set to "none", and it's read only so I can't change it. The designer code shows it as returning an int, and if I change that manually, it just gets undone on the next build.

But with another (nearly identical) stored procedure, I can change the return type just fine (from "Auto Generated Type" to what I want.)

I've run into this problem on two separate machines. Any idea what's going on?

Here's the stored procedure that works:

USE [studio]
GO
/****** Object:  StoredProcedure [dbo].[GetCourseAnnouncements]    Script Date: 05/29/2009 09:44:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[GetCourseAnnouncements]
    @course int
AS
SELECT * FROM Announcements WHERE Announcements.course = @course
RETURN

And this one doesn't:

USE [studio]
GO
/****** Object:  StoredProcedure [dbo].[GetCourseAssignments]    Script Date: 05/29/2009 09:45:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[GetCourseAssignments]
    @course int
AS
SELECT * FROM Assignments WHERE Assignments.course = @course ORDER BY date_due ASC
RETURN

Answer 1

The way to get around this issue is:

1. Add "set fmtonly off;" to the beginning of your stored procedure.
2. After adding that statement get DBML generate the code for your stored procedure.

If your stored procedure's return type is still 'int' in your DBML code, comment the entire code of stored procedure, create a new SELECT statement whose returning fields types and names match the original's SELECT statement and get DBML regenerate the code again. It has to work!

Answer 2

This also happens when using sql user-defined types as parameters in stored procedures

http://alejandrobog.wordpress.com/2009/09/23/linq-to-sql-%e2%80%94-can%e2%80%99t-modify-return-type-of-stored-procedure/

Answer 3

I had a similar mapping problem, but I found the culprit in my case.

If your procedure or any subprocedure that gets called has temporary objects like

CREATE TABLE #result (
   ID INT,
   Message VARCHAR(50)
)

then you're in trouble, even if you don't select anything of these temporaries.

The mapper has a general problem with these temporary objects, because the type can be changed outside the procedure in the session context. Temporary objetcs are not typesafe for the mapper and he refuses the usage os them.

Replace them by table variables and you're back in business

DECLARE @result AS TABLE (
   ID INT,
   Message VARCHAR(50)
)

Answer 4

I managed to work out an easier way, which just wasn't obvious at the time, but sounds straight forward when written down:

1. Delete the stored procedure from the design surface of the .dbml file
2. Click Save All files
3. Click Refresh in Server Explorer on the list of Stored Procedures
4. Add (drag) the stored procedure back onto the design surface of the .dbml file
5. Click Save All
6. Click Build
7. Check the designer.cs code file and you will have the updated C# code for the new version of the stored procedure

check http://www.high-flying.co.uk/C-Sharp/linq-to-sql-can-t-update-dbml-file.html

Answer 5

I had the same problem, but only happens if my sp uses FTS, what i did was "cheat" the dbml designer, I remove the fts language stuff and works perfectly, now i can change the return type. Later i go to the sp and add the fts again and works pefectly!. Hope this help.

Answer 6

Better solution found here: http://tonesdotnetblog.wordpress.com/2010/06/02/solution-my-generated-linq-to-sql-stored-procedure-returns-an-int-when-it-should-return-a-table/