defining a stored proc for use with dataset using tableadapter wizard

[EDN Admin]

<!--StartFragment --> <span id="_ctl0_MainContent_PostFlatView <span>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 :
<font color="#0000ff" size="2
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 </font><font size="2 dbo.rpt_IPR_Enhanced </font> <font size="2 @AssessmentID </font><font color="#0000ff" size="2 varchar</font><font size="2 (50),</font> <font size="2 </font> <font size="2 @RaterID </font><font color="#0000ff" size="2 int </font><font size="2 =0, </font> <font size="2 </font> <font size="2 @LocaleID </font><font color="#0000ff" size="2 int </font><font size="2 =1033</font> <font size="2 </font> <font color="#0000ff" size="2 AS</font> <font color="#008000" size="2 /*REVISIONS </font><font size="2 </font> <font color="#008000" size="2 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.</font> <font size="2 </font> <font color="#008000" size="2 ASH 002 01/02/04 Altered call to UDF_TranslateDADVals function and the sorting.</font> <font color="#008000" size="2 */ </font><font color="#0000ff" size="2 </font> <font color="#0000ff" size="2 SELECT </font><font size="2 Review.ReviewID,</font> <font size="2 </font> <font size="2 Review.ReviewType,</font> <font size="2 </font> <font size="2 Review.Description,</font> <font size="2 </font> <font size="2 Review.EmpID,</font> <font size="2 </font> <font size="2 Review.WorkflowID,</font> <font size="2 </font> <font size="2 Employee.FullName,</font> <font size="2 </font> <font size="2 Employee.PayGrade,</font> <font size="2 </font> <font size="2 Employee.BuildingLocation,</font> <font size="2 </font> <font size="2 Employee.Title,</font> <font size="2 </font> <font size="2 Employee.JobGroup,</font> <font size="2 </font> <font size="2 Employee.SupervisorID,</font> <font size="2 </font> <font size="2 Employee.PrimarygroupID,</font> <font size="2 </font> <font size="2 EmployeeGroup.GroupName,</font> <font size="2 </font> <font size="2 Supervisor.FullName </font><font color="#0000ff" size="2 as </font><font size="2 SupName,</font> <font size="2 </font> <font size="2 Supervisor.SupervisorID </font><font color="#0000ff" size="2 as </font><font size="2 SupSupID,</font> <font size="2 </font> <font size="2 Supervisor2.FullName </font><font color="#0000ff" size="2 as </font><font size="2 Sup2Name, </font> <font size="2 </font> <font size="2 L.AssessmentItemID,</font> <font size="2 </font> <font size="2 L.AssessmentItemType,</font> <font size="2 </font> <font size="2 L.ItemPosition,</font> <font size="2 </font> <font size="2 L.Weight,</font> <font size="2 </font> <font size="2 AI.Name,</font> <font size="2 </font> <font size="2 AI.ShortName,</font> <font size="2 </font> <font size="2 AI.Text </font><font color="#0000ff" size="2 as </font><font size="2 AIText,</font> <font size="2 </font> <font size="2 AI.ScalesetID,</font> <font size="2 </font> <font size="2 AI.RelatedAssessmentID,</font> <font size="2 </font> <font size="2 AI.RelatedAssessmentType,</font> <font size="2 </font> <font size="2 AI.AdmDesc,</font> <font size="2 </font> <font size="2 AI.CommentsOption,</font> <font size="2 </font> <font size="2 ITR.RaterID </font><font color="#0000ff" size="2 as </font><font size="2 IRRaterID,</font> <font size="2 </font> <font size="2 Score.RawScore,</font> <font size="2 </font> <font size="2 dbo.UDF_TranslateDADVals(Score.RawScore) </font><font color="#0000ff" size="2 AS </font><font size="2 [RawScoreByUDFScale],</font> <font size="2 </font> <font size="2 Com.CompetencyName </font><font color="#0000ff" size="2 As </font><font size="2 [Competency],</font> <font size="2 </font> <font size="2 Com2.CompetencyName </font><font color="#0000ff" size="2 As </font><font size="2 [ParentCompetency],</font> <font size="2 </font> <font size="2 Score.Comment,</font> <font size="2 </font> <font color="#0000ff" size="2 ISNULL</font><font size="2 (Score.FlaggedNA, 0) </font><font color="#0000ff" size="2 as </font><font size="2 FlaggedNA,</font> <font size="2 </font> <font color="#0000ff" size="2 ISNULL</font><font size="2 (Score.Completed, 0) </font><font color="#0000ff" size="2 as </font><font size="2 Completed,</font> <font size="2 </font> <font size="2 Scaleitem.ItemDescription </font><font color="#0000ff" size="2 as </font><font size="2 ScaleDesc,</font> <font size="2 </font> <font size="2 Review.Step,</font> <font size="2 </font> <font size="2 Score.RaterID,</font> <font size="2 </font> <font color="#0000ff" size="2 ISNULL</font><font size="2 (AI.AllowNA, 0) </font><font color="#0000ff" size="2 as </font><font size="2 AllowNA</font> <font color="#0000ff" size="2 </font> <font color="#0000ff" size="2 INTO </font><font size="2 #TmpResults</font> <font color="#0000ff" size="2 </font> <font color="#0000ff" size="2 FROM </font><font size="2 Review </font> <font size="2 </font> <font color="#0000ff" size="2 LEFT JOIN </font><font size="2 Employee </font><font color="#0000ff" size="2 ON </font><font size="2 Employee.EmpID=Review.EmpID</font> <font size="2 </font> <font color="#0000ff" size="2 LEFT JOIN </font><font size="2 Employee Supervisor </font><font color="#0000ff" size="2 ON </font><font size="2 Supervisor.EmpID=Employee.SupervisorID</font> <font size="2 </font> <font color="#0000ff" size="2 LEFT JOIN </font><font size="2 Employee Supervisor2 </font><font color="#0000ff" size="2 ON </font><font size="2 Supervisor2.EmpID=Supervisor.SupervisorID</font> <font size="2 </font> <font color="#0000ff" size="2 LEFT JOIN </font><font size="2 EmployeeGroup </font><font color="#0000ff" size="2 ON </font><font size="2 EmployeeGroup.GroupID=Employee.PrimaryGroupID</font> <font size="2 </font> <font color="#0000ff" size="2 LEFT JOIN </font><font size="2 AssessmentItemLink L </font><font color="#0000ff" size="2 ON </font><font size="2 L.AssessmentID=</font><font color="#0000ff" size="2 CAST</font><font size="2 (Review.ReviewID </font><font color="#0000ff" size="2 as varchar</font><font size="2 ) </font> <font size="2 </font> <font color="#0000ff" size="2 LEFT JOIN </font><font size="2 AssessmentItem AI </font><font color="#0000ff" size="2 ON </font><font size="2 AI.AssessmentItemID=L.AssessmentItemID </font><font color="#0000ff" size="2 AND </font><font size="2 AI.LocaleID=@LocaleID</font> <font size="2 </font> <font color="#0000ff" size="2 LEFT JOIN </font><font size="2 Competency Com </font><font color="#0000ff" size="2 ON </font><font size="2 Com.CompetencyID = AI.CompetencyID</font> <font size="2 </font> <font color="#0000ff" size="2 LEFT JOIN </font><font size="2 Competency Com2 </font><font color="#0000ff" size="2 ON </font><font size="2 Com2.CompetencyID = Com.ParentID</font> <font size="2 </font> <font color="#0000ff" size="2 LEFT JOIN </font><font size="2 IRAssessmentItemToRater ITR </font><font color="#0000ff" size="2 ON CAST</font><font size="2 (ITR.ReviewID </font><font color="#0000ff" size="2 as varchar</font><font size="2 )=L.AssessmentID </font><font color="#0000ff" size="2 AND </font><font size="2 ITR.AssessmentItemID=L.AssessmentItemID</font> <font size="2 </font> <font color="#0000ff" size="2 LEFT JOIN </font><font size="2 Score </font><font color="#0000ff" size="2 ON </font><font size="2 Score.AssessmentID=</font><font color="#0000ff" size="2 CAST</font><font size="2 (Review.ReviewID </font><font color="#0000ff" size="2 as varchar</font><font size="2 ) </font><font color="#0000ff" size="2 AND </font><font size="2 Score.RateeID=Review.EmpID</font> <font size="2 </font> <font color="#0000ff" size="2 AND </font><font size="2 Score.AssessmentID=L.AssessmentID </font><font color="#0000ff" size="2 AND </font><font size="2 Score.AssessmentItemID=L.AssessmentItemID</font> <font size="2 </font> <font color="#0000ff" size="2 AND </font><font size="2 Score.RaterID=ITR.RaterID </font><font color="#0000ff" size="2 AND </font><font size="2 Score.AssessmentID=</font><font color="#0000ff" size="2 CAST</font><font size="2 (ITR.ReviewID </font><font color="#0000ff" size="2 as varchar</font><font size="2 ) </font><font color="#0000ff" size="2 AND </font><font size="2 Score.AssessmentItemID=ITR.AssessmentItemID</font> <font size="2 </font> <font color="#0000ff" size="2 LEFT JOIN </font><font size="2 ScaleItem </font><font color="#0000ff" size="2 ON </font><font size="2 ScaleItem.ItemPosition = Score.RawScore </font><font color="#0000ff" size="2 AND </font><font size="2 ScaleItem.ScaleSetID=AI.ScaleSetID </font><font color="#0000ff" size="2 AND </font><font size="2 ScaleItem.LocaleID=@LocaleID</font> <font color="#0000ff" size="2 </font> <font color="#0000ff" size="2 WHERE </font><font size="2 Review.ReviewID=@AssessmentID</font> <font size="2 </font><font color="#0000ff" size="2 </font> <font color="#0000ff" size="2 IF </font><font size="2 @RaterID > 0</font> <font color="#0000ff" size="2 BEGIN </font><font size="2 </font> <font color="#008000" size="2 /*Limit to this rater - only get items they are assigned to*/</font> <font size="2 </font> <font color="#0000ff" size="2 DELETE FROM </font><font size="2 #TmpResults </font><font color="#0000ff" size="2 WHERE </font><font size="2 IRRaterID<>@RaterID </font><font color="#0000ff" size="2 OR </font><font size="2 (IRRaterID </font><font color="#0000ff" size="2 IS NULL AND </font><font size="2 AssessmentItemType<>0)</font> <font color="#0000ff" size="2 </font> <font color="#0000ff" size="2 END</font> <font color="#0000ff" size="2 </font> <font color="#0000ff" size="2 SELECT </font><font size="2 * </font><font color="#0000ff" size="2 FROM </font><font size="2 #TmpResults </font><font color="#0000ff" size="2 ORDER BY </font><font size="2 ItemPosition, [Name]</font> <font size="2

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

<!--StartFragment --> <span id="_ctl0_MainContent_PostFlatView <span> 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.



</font>

View the full article