none
defining a stored proc for use with dataset using tableadapter wizard RRS feed

  • Question

  •  things look good till I reach the Bind Commands to Existing Stored procedures page of the wizard and I select my target spproc (rpt_IPR_...) then I  hit the finish button and see the message :

       invalid object name '#TmpResults'

    now the dataset seems to setup correctly but was wondering why we are getting this message and should I be concerned. The same proc in Access had no issues.
    Here is the proc:


    ALTER PROCEDURE
    dbo.rpt_IPR_Enhanced

    @AssessmentID varchar(50),

    @RaterID int =0,

    @LocaleID int =1033

    AS

    /*REVISIONS

    ASH 001 11/24/03 Created based on rtp_IPR_Results, to which this sproc is identical except for the call to the UDF_TranslateDADVals function and the sorting.

    ASH 002 01/02/04 Altered call to UDF_TranslateDADVals function and the sorting.

    */

    SELECT Review.ReviewID,

    Review.ReviewType,

    Review.Description,

    Review.EmpID,

    Review.WorkflowID,

    Employee.FullName,

    Employee.PayGrade,

    Employee.BuildingLocation,

    Employee.Title,

    Employee.JobGroup,

    Employee.SupervisorID,

    Employee.PrimarygroupID,

    EmployeeGroup.GroupName,

    Supervisor.FullName as SupName,

    Supervisor.SupervisorID as SupSupID,

    Supervisor2.FullName as Sup2Name,

    L.AssessmentItemID,

    L.AssessmentItemType,

    L.ItemPosition,

    L.Weight,

    AI.Name,

    AI.ShortName,

    AI.Text as AIText,

    AI.ScalesetID,

    AI.RelatedAssessmentID,

    AI.RelatedAssessmentType,

    AI.AdmDesc,

    AI.CommentsOption,

    ITR.RaterID as IRRaterID,

    Score.RawScore,

    dbo.UDF_TranslateDADVals(Score.RawScore) AS [RawScoreByUDFScale],

    Com.CompetencyName As [Competency],

    Com2.CompetencyName As [ParentCompetency],

    Score.Comment,

    ISNULL(Score.FlaggedNA, 0) as FlaggedNA,

    ISNULL(Score.Completed, 0) as Completed,

    Scaleitem.ItemDescription as ScaleDesc,

    Review.Step,

    Score.RaterID,

    ISNULL(AI.AllowNA, 0) as AllowNA

    INTO #TmpResults

    FROM Review

    LEFT JOIN Employee ON Employee.EmpID=Review.EmpID

    LEFT JOIN Employee Supervisor ON Supervisor.EmpID=Employee.SupervisorID

    LEFT JOIN Employee Supervisor2 ON Supervisor2.EmpID=Supervisor.SupervisorID

    LEFT JOIN EmployeeGroup ON EmployeeGroup.GroupID=Employee.PrimaryGroupID

    LEFT JOIN AssessmentItemLink L ON L.AssessmentID=CAST(Review.ReviewID as varchar)

    LEFT JOIN AssessmentItem AI ON AI.AssessmentItemID=L.AssessmentItemID AND AI.LocaleID=@LocaleID

    LEFT JOIN Competency Com ON Com.CompetencyID = AI.CompetencyID

    LEFT JOIN Competency Com2 ON Com2.CompetencyID = Com.ParentID

    LEFT JOIN IRAssessmentItemToRater ITR ON CAST(ITR.ReviewID as varchar)=L.AssessmentID AND ITR.AssessmentItemID=L.AssessmentItemID

    LEFT JOIN Score ON Score.AssessmentID=CAST(Review.ReviewID as varchar) AND Score.RateeID=Review.EmpID

    AND Score.AssessmentID=L.AssessmentID AND Score.AssessmentItemID=L.AssessmentItemID

    AND Score.RaterID=ITR.RaterID AND Score.AssessmentID=CAST(ITR.ReviewID as varchar) AND Score.AssessmentItemID=ITR.AssessmentItemID

    LEFT JOIN ScaleItem ON ScaleItem.ItemPosition = Score.RawScore AND ScaleItem.ScaleSetID=AI.ScaleSetID AND ScaleItem.LocaleID=@LocaleID

    WHERE Review.ReviewID=@AssessmentID

     

    IF @RaterID > 0

    BEGIN

    /*Limit to this rater - only get items they are assigned to*/

    DELETE FROM #TmpResults WHERE IRRaterID<>@RaterID OR (IRRaterID IS NULL AND AssessmentItemType<>0)

    END

    SELECT * FROM #TmpResults ORDER BY ItemPosition, [Name]



    Microsoft Visual Studio 2005
    Version 8.0.50727.26  (RTM.050727-2600)
    Microsoft .NET Framework
    Version 2.0.50727

    Installed Edition: Enterprise

    Microsoft Visual Basic 2005   55603-000-0000016-00751
    Microsoft Visual Basic 2005

    Microsoft Visual C# 2005   55603-000-0000016-00751
    Microsoft Visual C# 2005

    Microsoft Visual Studio Tools for Office   55603-000-0000016-00751
    Microsoft Visual Studio Tools for the Microsoft Office System

    Microsoft Visual Web Developer 2005   55603-000-0000016-00751
    Microsoft Visual Web Developer 2005

    Visual Studio 2005 Team Edition for Architects   55603-000-0000016-00751
    Microsoft Visual Studio 2005 Team Edition for Software Architects

    Visual Studio 2005 Team Edition for Developers   55603-000-0000016-00751
    Microsoft Visual Studio 2005 Team Edition for Software Developers

    Visual Studio 2005 Team Edition for Testers   55603-000-0000016-00751
    Microsoft Visual Studio 2005 Team Edition for Software Testers

    Crystal Reports    AAC60-G0CSA4B-V7000AY
    Crystal Reports for Visual Studio 2005

     

    More info .....
    Because we where not seeing info on the spproc parms we began to suspect that the query made by the IDE to get metadata was failing and that indeed is the case. (we also switch to non-production database to reproduce the issue)

    after some great SQL debuging from Kevin Currier here at Mindsolve the problem seems to be the SET FMTONLY OFF; SET FMTONLY ON; that the framework is using to get metadata info for the spproc;

    sql profiler trace:
    SET FMTONLY OFF; SET FMTONLY ON;
    exec MVP_512.dbo.rpt_IPR_FullDetails @filterQuery = NULL

    when these sql commands are feed into query analyser we see the result:
    (0 row(s) affected)


    (0 row(s) affected)


    (0 row(s) affected)

    Server: Msg 208, Level 16, State 1, Procedure rpt_IPR_FullDetails, Line 30
    Invalid object name '#AvgScore'.

    as soon as we insert a "SET FMTONLY OFF" into the proc then things work as expected.

    Hopefully someone can recommend another solution as we do not want to change all the spprocs.




    Monday, October 31, 2005 2:55 PM

Answers

  • Hi,
    The problems stems from using a #Temp table.  The infrastructure we use to determine the resultset without requiring parameter values to be filled doesn't support the usage of #Temp tables. 

    Tuesday, November 1, 2005 3:46 AM
    Moderator

All replies

  • Hi,
    The problems stems from using a #Temp table.  The infrastructure we use to determine the resultset without requiring parameter values to be filled doesn't support the usage of #Temp tables. 

    Tuesday, November 1, 2005 3:46 AM
    Moderator
  • Thank you for the answer.  I've been pulling my hair out on this one.

    Although, MS, this is a huge problem. 
    Thursday, November 17, 2005 5:27 PM
  • Hi Steve,

    What exactly is the infrastructure you use to determine the resultset fo a stored proc? I guess you use a strig Parser of some sort followed by a Execute query. And is that parser available in the .Net Framework.

    thanks
    Thursday, December 1, 2005 2:24 PM
  • I just ran headlong into this same issue.  After some profiling in SQL Server found out about the SET FMTONLY ON; option, and then found this page.

    So, in the event that someone else gets this far, I'll offer my workaround: 

    1. Create a new stored procedure that exposes the same API as your real proc, except without using #tempTables anywhere.
    2. Drag-n-drop the new proc onto the DataSet designer surface.
    3. Open the xsd file with the XML editor (right click the file->Open With)
    4. Find all of the references to the new stored proc's name and change them back to the old proc's name.
    5. Save, and preview the data.  Viola!

    In code, notice how both of the following proc return the same metadata, but only the second one will compile with SET FMTONLY ON;

    Hope this helps someone else, but if you've gotten far enough to figure out the google search query necessary to find this post, you've probably already figured out this workaround.


    CREATE PROCEDURE RealStoredProc

    AS

    SELECT NULL ColumnA, 1 ColumnB

    INTO #aTempTable;

    SELECT * FROM #aTempTable;

    GO


    CREATE PROCEDURE VSDesignerStoredProc

    AS

    SELECT NULL ColumnA, 1 ColumnB;

    GO

     

     

    Monday, April 24, 2006 10:32 PM
  • Try the dragging-and-dropping the following stored proc onto the design surface.

    CREATE PROCEDURE uspSqlOptionInjectionTest

    AS

    -- exec uspSqlOptionInjectionTest

    SET FMTONLY OFF;

    SELECT NULL Foo

    INTO #tempFoo;

    SELECT * FROM #tempFoo;

    GO

    Monday, April 24, 2006 10:59 PM
  • Well it's good to know there is a reason..but...um...temp tables are a pretty accepted method for getting report data together for display.   I'm a bit surpised this wasn't fixed before release - it's a pretty serious issue with the whole designtime IDE.  It's interesting that it seems to work at runtime, which is good, but it really sucks that the schema can't be read to use during design time GUI work.  I provided default parameters so it's not just a problem with not having parameters filled - it still doesn't refresh the schema.  Is there a work around to get the schema to refresh?

    Thanks for the info,

    Nick H

    Sunday, June 25, 2006 10:55 PM
  • Just ran into this again and had to figure the solution again. Try using a Table var instead of Temp Table.
    Wednesday, May 2, 2007 7:52 PM
  • Like David suggests, using the SET FMTONLY OFF option did work for me. However, I first needed to delete the storedproc from my XSD file - and then re-add it for it to finally be able to use Reader as the ExecutionMode. Now it works like a charm.

    Before it just wanted to display a single value: Scalar or Non-query.

    Thursday, May 3, 2007 6:41 PM
  •  

    I just worked around this problem using non-deterministic table value functions instead of temp tables.

     

    This may work long term for us as the result sets are fairly small and joined on a PK, which apparenly can be indexed. Further performance testing pending.

    Sunday, June 3, 2007 11:04 PM
  • I have been struggeling with this and related table adapter wizard magic for sometime now.   I found table variables to be a very good solution early on in our development.  Unfortuantely, as user demands for data increase the table variable performance issues related to large results sets have dictated some new approach (which I have yet to find).I have found numerous suggestions for back door manipulation of "phantom" procedures, "phoney" views, "pseudo" tables etc. 

    I find all of these approaches to be the complete antithesis of any rational software development process.  They reek of the kinds of tricks we pulled in the bad old days of self modifying code, bit packed structures, overlayed common data blocks, etc.

    I believe the Adapter Wizard is trying to be too smart.  I have found many occassions where it rejects code that the SQL engine executes with out problem.   Additionally, I find it unacceptable that the wizard will decide there is no schema (even though I have specified a tabular result) but will not provide a reasonable diagnostic to help me resolve the problem.

    I reject the notion that the Wizard can't find the temp table becasue it does not exist.  The only differences in my procedure code are "Declare @myTab table ()" versus "Create table #MyTab".  The entire schema definition remains unchanged otherwise.  In fact the only other differences in the procedures are subsitituing the @ for # and removing (commenting out) the "Drop Table" statement.

    Surely, MS can find a reasonable and clean solution to this issue.  How about something as simple as a pair of "metadata" tags in a SQL comment (ala HTML or XML) in the stored procedure that identify the start and end of the procedure's result set definition.

     

    Thursday, June 7, 2007 8:36 PM
  • This works for me.  Add these lines to the beginning of your sp:

    IF 1=0 BEGIN
        SET FMTONLY OFF
    END

    Obviously that statement is never executed, but for whatever reason it gets the job done.
    • Proposed as answer by mwdenko Monday, June 13, 2011 7:46 PM
    Thursday, November 15, 2007 6:30 PM
  • That worked great.

     

    Sunday, April 27, 2008 7:56 PM
  • The solution to this problem is simple. The dataset designer requires meta data for access. So this problem can be easily sorted out by changing the temporary table (#) to global temporary table (##) in the stored procedure and construct the dataset.

    Go back to the stored procedure and change global temporary table (##) to temporary table (#) after dataset construction.

    This worked very well for me.

    Friday, September 19, 2008 2:54 PM
  • This works for me.  Add these lines to the beginning of your sp:

    IF 1=0 BEGIN
        SET FMTONLY OFF
    END

    Obviously that statement is never executed, but for whatever reason it gets the job done.


    Was Scraching My Head for Hourss.....this worked like a Charm....offcourse it's a reply to an old post....but saved me.......Greatful to you......BISDamonL Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals

    • Proposed as answer by ronnotel Tuesday, October 18, 2011 3:51 PM
    Tuesday, October 13, 2009 7:14 PM
  • You rock.   Thanks!

    I'm glad you replied to this old post.
    Friday, October 23, 2009 3:07 PM
  • I know this is super old.  But, It helped so much.

     

    Thanks!

    Monday, June 13, 2011 7:47 PM
  • This works for me.  Add these lines to the beginning of your sp:

    IF 1=0 BEGIN
        SET FMTONLY OFF
    END

    Obviously that statement is never executed, but for whatever reason it gets the job done.


    Was Scraching My Head for Hourss.....this worked like a Charm....offcourse it's a reply to an old post....but saved me.......Greatful to you......BISDamonL Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals

    Freepin' jeepers - whodathunkit. I added these lines just after my ALTER PROC myProc AS and it worked like a charm.

    I've absolutely detested the whole TableAdapter model ever since I first laid eyes on it 10 years ago. That something like this is required is just nuts.

    Tuesday, October 18, 2011 3:54 PM
  • I've absolutely detested the whole TableAdapter model ever since I first laid eyes on it 10 years ago. That something like this is required is just nuts.


    All the more reason why I dumped using TableAdapters.  I'm tellin' ya, best thing I've done --- you can still build Strongly Typed DataSets outside of the designers and without those TableAdapters.
    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Tuesday, October 18, 2011 5:19 PM