none
VB.NET LINQ : Auto generated code in designer file for Stored Procedure RRS feed

  • Question

  • Hello Team,
    I am developing an desktop application using LINQ. I am very new to LINQ. I have a stored proc in which I am using the Temporary table and also returning a temporary table. When I add this stored proc in the DBML file it does not allow me to select the 'Return Type' but if I give a simple select query in the stored proc it gives the (Auto-Generate Retrun Type).

    I don't know how to set the return values to such procedures. I tried searching on the internet, but didnt get any proper solution to it. WHen i looked into the designer the stored proc in which i am using the temporary table is retruning an integer whereas the simple select query return an row. I fail to understand this logic.
    Can you please help and tell me how to set the return type for the stored procs returning the values from the temporary table ??

    Monday, July 13, 2009 1:53 PM

All replies

  • I ran into something simmiliar. See if this helps. I implemented option (2), and got what I wanted.

    -- ERROR WHEN dragging this stored procecure onto the Linq data designer surface in Visual Studio 2008:

    -- "One or more selected database objects returns a schema that does not match the schema of the target data class."

    -- WORK AROUND (from http://www.sqlservercentral.com/articles/Integration+Services/61824/):

    --Well the most likely case is that you are selecting from a temporary table as the last statement in the source stored procedure,
    and in this case there is no metadata returned to the designer.

    --(1) One possibility is to change the SQL Command Text as follows and add the italicised text:

    --

    --SET FMTONLY OFF

    --SET NOCOUNT ON

    --exec myproc

    --

    --This forces the parser to actually run the query to return the metadata and reverse the SET FMTONLY ON that it implicitly uses.

    --

    --(2) Use a Table Variable instead

    Tuesday, July 14, 2009 4:15 PM
  •  

    My previous post did not work. This did: 

    -- 2009/09/30 Paul Player
    --
    -- ERROR WHEN dragging this stored procecure onto the Linq data designer surface in Visual Studio 2008:

     

    -- ' C:\sourceFiles\CFAB\MMS\DesktopModules\draReports\ViewStatusCountsForConsulting.ascx.vb
    --
    -- This means:
    -- ' linq data designer missing result class for stored procedure
    --
    -- ' Solution:
    --
    -- ' 1. Define the result set in the SQL Stored Procedure as a table variable
    -- This means GUTTING everything out of the Stored Procedure except
    -- for Parameters and the Declare @tempFinalResultSet AS Table ... and the final statement,
    -- which must be SELECT * FROM @tempFinalResultSet.
    --
    -- ' 2. Delete and re-add the stored procedure in the Visual Studio data designer.
    -- You may need to refresh the stored procedure in data source first.
    --
    -- 3. Restore the Stored Procedure to its Pre-Gutted state.
    --
    Paul Guernsey Player

    --' Error 1 Type 'dra_GetMemberStatusCountsForConsultingResult' is not defined
    • Proposed as answer by TheGuern Thursday, October 1, 2009 3:07 AM
    Thursday, October 1, 2009 3:06 AM