Tech Dreams

Fixing LINQ to SQL Issue: Stored Procedure Definition Returns int Instead of ResultSet


Problem

Today I was struck with an annoying problem while generating LINQ to SQL dbml file for a stored procedure using Visual Studio 2008.

When the stored procedure is dragged and dropped on the DBML designer, Visual Studio is generating wrong signature for the stored procedure output: instead of returning a result set as output, an int value is set as output.

Possible Cause

Only one stored procedure out of 100s available in the dbml file is affected with the bug in LINQ to SQL class builder. When the stored procedure is closely examined, the sp is returning a single record as output and the values of output columns are calculated in the sp(they are not retrieved from tables directly). Looks like it is the cause for misbehavior of Visual Studio 2008.

Solution or Workaround

After spending couple of unfruitful hours in fixing the issue directly, I Google for solution to the issue. Luckily a blog post gave a tip on work around to sort out this annoying issue. The work around is

  • Create a dummy stored procedure with same signature(input parameters & output columns) by returning dummy values
  • Generate the dbml file using the dummy stored procedure
  • Drop the dummy stored procedure and create the actual stored procedure on the database

Here is the dummy stored procedure  used for generating the dbml file

CREATE PROCEDURE pGetProfileDetails  
(  
 @P_IdentifyingUID BIGINT,  
 @P_IdentifyingType VARCHAR(50)  
    
)  
AS  
BEGIN 
	SELECT	'test' AS Name, 
		'test' AS Industry, 
		'test' AS FunctionName, 
		'test' as JobRoles, 
		'test' as Skills, 
		'test' as Specializations
END

via

19 Comments on Fixing LINQ to SQL Issue: Stored Procedure Definition Returns int Instead of ResultSet

  1. petertraco
    August 3, 2011 at 5:40 am (8 years ago)

    Thanks a lot with solution SET FMTONLY OFF

    Reply
  2. gk
    June 16, 2011 at 8:55 pm (8 years ago)

    How can i use LINQ with stored procedure with dynamic columns? Any idea?

    Reply
  3. Pramod
    September 16, 2010 at 1:02 am (9 years ago)

    I had a similar problem trying to get result from parameterized stored proc. Here is the fix.

    Parametrized stored proc compiles and executes on the runtime so dbml generation tool is not sure of the resulting IsingleResult object .To make this work just copy again your select statement at the end of the stored proc and execute it.Now try dragging storedproc from server explorer and it should work as usual. Don’t forget to takeoff the copied select statement at the end of the storedproc after dbml generation is done.

    Reply
  4. Wayne
    August 4, 2010 at 5:38 am (9 years ago)

    Thank you Max. Brilliant one liner. I’m now a happy chappy

    Reply
  5. PratSol
    June 8, 2010 at 10:08 am (9 years ago)

    I face the same issue, and resolve it by setting FMTonly to off at the last of Stored Procedure before commit.

    and it works fine for me.

    Reply
  6. Xiong Wei
    May 22, 2010 at 4:21 pm (9 years ago)

    I don’t agree above is the best solution, when your stored procedure need to change that add one more column, how to do?

    Because you need to create the strongly type again by another stored procude.

    Reply
  7. Venu
    April 14, 2010 at 12:12 am (9 years ago)

    I have Dynamic Sql Stored Procedure and Temp Table also creates using Dynamic Sql Columns. Anybody have any clues to fix this?

    Thank you.

    Reply
  8. Nk54
    January 19, 2010 at 3:08 pm (9 years ago)

    SET FMTONLY OFF works perfect i put it in all my stored proc now ;) thx a lot !!!!!!!!!!

    Reply
  9. Nathan
    November 23, 2009 at 10:43 pm (9 years ago)

    Thanks alot, I can confirm that setting FMTONLY OFF works. Could someone explain why?

    Thanks.

    Reply
  10. Jon
    November 23, 2009 at 10:20 pm (9 years ago)

    I have dynamic sql in my SP… any ideas how use LINQ?
    thanks

    Reply
  11. nk54
    September 28, 2009 at 2:50 pm (9 years ago)

    SET FMTONLY OFF ? ok i ll try this by the end of this week ! thx a lot :)

    Reply
  12. Mark
    September 17, 2009 at 4:14 am (10 years ago)

    Yes, putting SET FMTONLY OFF at the top of your sproc will fix the issue with #temp tables in the sproc

    Reply
  13. Gopinath
    September 8, 2009 at 11:59 am (10 years ago)

    nk54,
    We noticed this issue when we have temporary tables in the SP.

    Reply
  14. nk54
    August 20, 2009 at 2:23 pm (10 years ago)

    i got the same kind of problem : my sp return a int value instead of a sp_myFunctionResult type.

    Is it because of using #table in my sp ? alias of my my column which may be wrong ?

    Thx 4 your help :)

    Reply
  15. Max
    July 23, 2009 at 10:53 pm (10 years ago)

    Try sticking SET FMTONLY OFF at the top of the stored proc’s code. I ran into this with SQL Reporting Services as well. I’m guessing that FMTONLY gets set to ON right before calling the stored proc with the intention to only get the columns that it should return, but this doesn’t seem to work in alot of cases. Overriding it to force it off will cause it to return data (which is likely just ignored), but it will be able to determine the proper format of the records returned. You may have to set default values on the stored proc parameters that will force it to return some data.

    Reply
  16. Gopinath
    June 25, 2009 at 9:59 am (10 years ago)

    As we progress with the development, more SPs are falling in to this category(it’s bad!). So creation of dummy sp’s is no longer a feasible solution for us.

    We stopped re-generating the full dbml file now. We are manually adding/updating the individual stored procedures as when they are created or modified.

    By the way, sp’s for which dbml file generation failing contains one or more of the following
    1) Temp tables
    2) Using of OUTPUT clause(SQL Server 2005+ feature)
    3) SELECT statements that output a single row of values calculated using formula

    Reply
  17. LINQ Master
    June 16, 2009 at 8:27 pm (10 years ago)

    This generally happens when there is a problem parsing the stored procedure. Usually, the stored procedure has a typo (double-check your table/column names) or a conversion error (double-check that any Convert/Casts are being used correctly).

    Your solution is a good first step, but I think nine times out of ten, it will generate a runtime exception when the sproc executes

    Reply
    • Gopinath
      June 16, 2009 at 10:26 pm (10 years ago)

      Even though I got a work around to this problem, it is very frustrating to create dummy sp and then replace it with actual sp. In the development environment, we regenerate the dbml for stored procedure very often and every time fiddling with this issue is very annoying.

      @LINQ Master,
      My SP returns does not return data from any table/temp table. It does lot of calculations and at the end returns a single record output with the help of SELECT statement.

      Reply

1Pingbacks & Trackbacks on Fixing LINQ to SQL Issue: Stored Procedure Definition Returns int Instead of ResultSet

  1. Anon
    December 16, 2017 at 8:40 pm (1 year ago)

    As mentioned in the other answer, you will need to use SET FMTONLY ON to ensure no data is returned. There are some situations where SET FMTONLY won’t work, e.g. when using #temp tables in your stored procedures, but there is a workaround.

    Reply

Leave a reply