locked
Stored procedure returning integer instead of ISingleResult RRS feed

  • Question

  • Hello everyone,

     

    I have problems executing stored procedures using LINQ. What I'm trying to do is to execute a stored procedure which in turn executes another procedure.

     

    Something like this

     

    Create Procedure Test

    As

    Exec Test2

    Go

     

    The problem is, that instead of a ISingleResult resultset, the return type is an integer.

     

    Do I manually have to alter the return type in the model code (if its even possible), or is there a more elegant

    and prefered soloution?

     

     

    Wednesday, January 2, 2008 7:49 AM

Answers

  • This problem occurs whenever the designer cannot figure out the return type of the stored procedure.  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.  The only 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.

     

     

     

     

     

     

     

    Wednesday, March 5, 2008 3:40 PM

All replies

  • Hi,

     

    This has been discussed in a previous post which you might want to give a read.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2608121&SiteID=1

     

    The solution is to use Scalar functions instead of stored procedures.

     

    Hope this helps.

     

    Ben

    Wednesday, January 2, 2008 8:27 AM
  • But  according to your response in that thread, scalar functions should be used when you want to return integers. I want to do the opposite, return ISingleResult. But for some reasons, LINQ is unable to figure out what data that should be returned from the procedure.

     

    From the other thread:

    "If you want to return a int then you should look at using a Scalar function in SQL Server"

     

    Wednesday, January 2, 2008 8:47 AM
  • Ahh sorry, I misread your post. It's odd that the code is being generated like this.

     

    Sadly, I don't have VS2008 installed here so I can't have a play.

     

     

    Wednesday, January 2, 2008 8:54 AM
  • Has anyone else seen this problem? It not only happens when using dynamic sql och "sub executing" procedures but also sometimes when updating the data model the return type gets wrong (int instead of result set)

    Wednesday, March 5, 2008 8:32 AM
  • This problem occurs whenever the designer cannot figure out the return type of the stored procedure.  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.  The only 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.

     

     

     

     

     

     

     

    Wednesday, March 5, 2008 3:40 PM
  • I had a stored procedure return a table using exec(@sql). The sql statement is dynamically generated and the select list cannot be fixed. Is it possible let DBML dynamically generate the return type for this stored procedure?
    Tuesday, April 1, 2008 4:10 AM
  • I am facing the same problem, a comment in ScottGu's blog states that http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

    create proc sptest

    as

    begin

    exec('select 1 as One, 2 as Two')

    exec sp_executesql N'select 1 as One, 2 as Two'

    end

    go

    set fmtonly on

    exec sptest

    set fmtonly off


    Another approach seems to be a temporary table http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=1044828&SiteID=1, have not tried the solutions by myself but we will see




    Monday, August 4, 2008 12:13 AM
  • I run into the same problem. VS doesn't recognize the return type (type of id). The problem seems to be the transaction (without transation it works). Can I change my SP or should I write my own result-type?

    Code Snippet

    BEGIN TRANSACTION

     

      EXEC sp_getapplock

      @Resource = 'Lock',

      @LockMode = 'Exclusive',

      @LockOwner = 'Transaction',

      @DbPrincipal = 'public'

      -- do other things like updates

      -- ...

     

      select

        id

      from

        table1

      where

        created = 'test'

     

      EXEC sp_releaseapplock

      @Resource = 'Lock',

      @DbPrincipal = 'public',

      @LockOwner = 'Transaction'

     

    IF @@ERROR <> 0

      ROLLBACK TRANSACTION

    ELSE

      COMMIT TRANSACTION

    RETURN

     

     

     

     

    Friday, September 5, 2008 2:52 PM
  •  Matt Warren - MSFT wrote:

    This problem occurs whenever the designer cannot figure out the return type of the stored procedure.  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.  The only 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.

     

     

     

     

     

     

     


    Hi I had this problem later. after test afew way for handle this problem. i think aesy way is that at the begin of your query write a fake select similar to the complete result set. after darg your procedure into dbml and generate returnType you can go in procedure and make comment your fake select. 

    I become glad to know your idea Dear Mr.Matt Warren about my solution.


    • Proposed as answer by Rajat OO7 Monday, August 20, 2012 1:10 PM
    Monday, September 8, 2008 8:27 AM
  •  

    this problem happen with me when I use a temp table in my SP and resolve this problem by using Multi statement table function the LINQ will read the temp table in this function strongly type

    Wednesday, November 12, 2008 8:12 AM
  • I am having the same problem with an sp that joins to a view.  Going to try the fake select, sounds best to me.

     

    edit:

     

    this worked but i had to change the sp name.  it was cashed somehow, i'll figure out the cashing when i can't change the sp name i guess.

     

    Tuesday, January 27, 2009 7:49 AM
  • Thanks, Ali!  Your solution worked for me!
    Jim Rolph
    Wednesday, October 7, 2009 11:27 PM
  • Plz plz plzzzzzzzzz !! ^^

    Could you write an exemple ?

    I got the same probleme and dont know how to make a fake select to py stored procedure which use #table (temporary table)

    My sp returnType is : <string,Int>

    Should i make a sp which looks like this ? select "blabla" as string and 12 as intCountPeople ?

    Thanks ! plz gimme an answer i m looking for a solution since 2 mounths :'(

    Thursday, October 29, 2009 3:30 PM
  • The solution didn't work for me :'(


    I've written a simple select in my sp :

    >select 'azertyuiop' as theDate, 12 as totCount

    >cut the real code in my stored procedure,

    >drag the procedure in my dblm file,

    (a test of a call and a binding in a datagrid show me that its working good)

    >and finally past the code in the stored procedure.

    And then it doesn't work anymore :/

    The key of error : System.Collections.ListDictionaryInternal
    AsyncCompletedEventArgs.RaiseExceptionIfNecessary()
    nameOfMethodeCompletedEventArgs.getResult()

    My procedure use #temp.

    Any clues, tips that could help me to fix the problem ?

    Thx.
    Friday, October 30, 2009 9:56 AM
  • http://www.techdreams.org/microsoft/fixing-linq-to-sql-issue-stored-procedure-definition-returns-int-instead-of-resultset-2/2752-20090614

    Solution for the problem..

    Worked for me....!!!!!!!!
    Sivabalan K
    Friday, March 5, 2010 12:25 PM
  • Yes, link Sivablan give also fix for me. Follow solution SET FMTONLY OFF

     

    Thanks


    Live
    Wednesday, August 3, 2011 12:13 AM
  • First create the sproc with the coloumns that you want to return as a normal sproc instead of dynamic sql or temp table ....
    execute it and then add that sproc in the Linq to Sql designer and then modify your sproc to your requirement.
    It works....
    Thursday, January 5, 2012 8:59 PM
  • NICE ON BEST WAY and easy.
    Monday, August 20, 2012 1:10 PM