VS2010: Why can't I import this Stored Proc?? RRS feed

  • Question

  • I have the following stored proc:



    PROCEDURE [dbo].[sp_GetBaselines]






    -- SET NOCOUNT ON added to prevent extra result sets from interfering with


    -- SELECT statements




    -- create the temporary table


    CREATE TABLE #tmpTblBaselines













    -- insert the records into the temporary table




    SET @SQL = 'INSERT INTO #tmpTblBaselines VALUES ' + @Request




    -- join the table with the real baseline table


    SELECT tblBaseline.MD5, tblBaseline.TemplateID, tblBaseline.FieldID, tblBaseline.Value FROM tblBaseline


    INNER JOIN #tmpTblBaselines




    .MD5 = #tmpTblBaselines.MD5 AND


    .TemplateID = #tmpTblBaselines.TemplateID AND


    .FieldID = #tmpTblBaselines.FieldID



    When I try to import this into my project via Add Data Source, ADO.NET refuses to create a strongly typed DataSet for the stored proc.

    If I remove the inner join portion, it works fine.

    I suppose a work-around would be to comment out the inner join, import and then uncomment it...

    But that would require doing that on every import. We are still in the early dev stage and the DB is changing a lot. I'd rather not do that :).


    Wednesday, July 21, 2010 11:48 PM


All replies

  • This is because the stored procedure must be compiled but not run.  The result set schema cannot be obtained up front when your stored procedure contains a statement which forces recompilation upon execution of the statement.  CREATE TABLE does this.  Consider rewriting to use a @table variable instead which can be used as a substitute for CREATE TABLE #temp.

    See "Declaring a variable of type table" (  Note that there are reasons that this could improve performance and reasons why this could decrease performance.  However, if the number of rows in the temporary table is small, it will likely improve performance.

    Some reading material:

    Thursday, July 22, 2010 2:13 AM
  • Hi BinaryCoder,

    Thanks for the response. Did you mean:

    DECLARE @tmpTblBaselines TABLE
    MD5 varchar(32),
    TemplateID int,
    FieldID int

    Is that what you meant?? That did not work either. Well, what I mean is... both the DECLARE and CREATE TABLE method "work" and return the proper data, but when I go to import with the ADO.NET "Add Data Source" wizard, the wizard can't figure out the schema of the result set and just sticks the sp_GetBaselines in the un-typed TableAdapters class.

    As I mentioned in the previous post, the thing thats confusing the wizard is the INNER JOIN... if I just have

    SELECT tblBaseline.MD5, tblBaseline.TemplateID, tblBaseline.FieldID, tblBaseline.Value FROM tblBaseline

    It can figure it out... if I put back the INNER JOIN part, it gets confused, but I don't really want to have to comment / uncomment on every import.

    • Proposed as answer by DK. Da Tuesday, July 27, 2010 2:25 AM
    Thursday, July 22, 2010 3:29 AM
  • A follow up...

    I can't use the DECLARE @tmpTblBaselines TABLE method because @tmpTblBaselines is not visible to the INNER JOIN ON clause.


    Thursday, July 22, 2010 4:20 PM
  • Compare with this:

    INNER JOIN @tmpTblBaselines t
    ON tblBaseline.MD5 = t.MD5
    AND tblBaseline.TemplateID = t.TemplateID
    AND tblBaseline.FieldID = t.FieldID
    Friday, July 23, 2010 3:01 AM