'Invalid object name' for temp table when executing a SP
in [Programming SQL]
|
From: dbuchanan on 6 Jul 2008 23:00 I am getting the message 'Invalid object name' when I am attempting to execute a stored procedure in which there is a temporary table. The message reads: "There is an error in the query. Invalid object name '#UsersEntities'." I do not receive this error when I execute the stored procedure from the query window inside SSMS I receive this error when I am trying to access the stored procedure from another application - Visual Studio 2005.- during an attempt to author a SQL Server Reporting Services report. I recieve this error when I click "next" after supplying the execute string in the "Query String" window inside Report Wizard. See below ================================= EXEC [dbo].[rsp_rptCompletedReportWLvTmpTbl2] 26183, N'148,453054852,453055212,453055335,453055431,453055432', '1900-01-01', '2079-06-06', '1900-01-01', '2079-06-06', N'1787,1826,1877,1990,646,1786,1845,1831,1015' ================================= The strange thing is that I was able to author a report with a similar stored procedure containing a temp table. This was just last week Yet now with this new stored procedure (and also now with the older stored procedure I referred to above - with both) I get the same "Invalid object name" error. Could this be a permission thing? It is possible that my permissions were changed to try to fix something else. If it is a permission thing what do I tell our jounior DBA that is running things this week? Thank you, Doug
From: Uri Dimant on 7 Jul 2008 00:53 Hi You said that it did work last week. It seems that something changed either in zour stored procedure or permissions. Do you create a tempdb inside the SP ? Can you show the source? "dbuchanan" <dbuchanan(a)newsgroup.nospam> wrote in message news:%23XUJE093IHA.5088(a)TK2MSFTNGP03.phx.gbl... >I am getting the message 'Invalid object name' when I am attempting to >execute a stored procedure in which there is a temporary table. The message >reads: > > "There is an error in the query. Invalid object name '#UsersEntities'." > > I do not receive this error when I execute the stored procedure from the > query window inside SSMS > > I receive this error when I am trying to access the stored procedure from > another application - Visual Studio 2005.- during an attempt to author a > SQL Server Reporting Services report. > > I recieve this error when I click "next" after supplying the execute > string in the "Query String" window inside Report Wizard. See below > > ================================= > EXEC [dbo].[rsp_rptCompletedReportWLvTmpTbl2] > 26183, > N'148,453054852,453055212,453055335,453055431,453055432', > '1900-01-01', > '2079-06-06', > '1900-01-01', > '2079-06-06', > N'1787,1826,1877,1990,646,1786,1845,1831,1015' > ================================= > > The strange thing is that I was able to author a report with a similar > stored procedure containing a temp table. This was just last week Yet now > with this new stored procedure (and also now with the older stored > procedure I referred to above - with both) I get the same "Invalid object > name" error. > > Could this be a permission thing? It is possible that my permissions were > changed to try to fix something else. If it is a permission thing what do > I tell our jounior DBA that is running things this week? > > Thank you, > Doug >
From: dbuchanan on 7 Jul 2008 02:47 Hi Uri, Here is the beginning of the Stored Procedure that worked last week in development. It is currently working in production. I can run the stored procedrue in a query window but not within the development environment. (Visual Studio Web app development RDLC or RDL. ======== ALTER procedure [dbo].[rsp_rptCompletedReportWLvTmpTbl2] ( @EntityID int -- ,@CourseID nvarchar(1000) -- (multi~value) ,@CourseDateRangeStart smalldatetime ,@CourseDateRangeEnd smalldatetime ,@HireDateRangeStart smalldatetime ,@HireDateRangeEnd smalldatetime ,@RoleID nvarchar(1000) -- (multi~value) ) AS Set NoCount ON declare @SQLQuery nvarchar(4000) ,@ParamDef nvarchar(200) -- Create table to get hierarchy entities at and under the logged in user create table #HierarchyEntitiesAtAndUnderLoggedInUser (eid int) insert into #HierarchyEntitiesAtAndUnderLoggedInUser exec rsp_getHierarchyEntities @EntityID ======== more context of its use... Where a.EntityID in (select eid from #HierarchyEntitiesAtAndUnderLoggedInUser) ======== I am using a temp table instead of a table variable because: "EXECUTE cannot be used as a source when inserting into a table variable." Thanks, Doug
From: "Charles Wang [MSFT]" on 7 Jul 2008 02:56 Hi Doug, I agree with Uri. To narrow down this issue, could you please elaborate your temp table @UsersEntities here and give us more information about your stored procedure rsp_rptCompletedReportWLvTmpTbl2 that used the temp table? Best regards, Charles Wang Microsoft Online Community Support =========================================================== Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg(a)microsoft.com. =========================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ============================================================ This posting is provided "AS IS" with no warranties, and confers no rights. =========================================================
From: Uri Dimant on 7 Jul 2008 03:12
Hi You said that the error is "There is an error in the query. Invalid object name '#UsersEntities'." But I only see #HierarchyEntitiesAtAndUnderLoggedInUser temporary table. Where does #UsersEntities' reside? In rsp_getHierarchyEntities ? "dbuchanan" <dbuchanan(a)newsgroup.nospam> wrote in message news:%23o$6Jz$3IHA.4332(a)TK2MSFTNGP06.phx.gbl... > Hi Uri, > > Here is the beginning of the Stored Procedure that worked last week in > development. > It is currently working in production. > I can run the stored procedrue in a query window but not within the > development environment. (Visual Studio Web app development RDLC or RDL. > > ======== > ALTER procedure [dbo].[rsp_rptCompletedReportWLvTmpTbl2] > ( > @EntityID int -- > ,@CourseID nvarchar(1000) -- (multi~value) > ,@CourseDateRangeStart smalldatetime > ,@CourseDateRangeEnd smalldatetime > ,@HireDateRangeStart smalldatetime > ,@HireDateRangeEnd smalldatetime > ,@RoleID nvarchar(1000) -- (multi~value) > ) > > AS > Set NoCount ON > > declare > @SQLQuery nvarchar(4000) > ,@ParamDef nvarchar(200) > > -- Create table to get hierarchy entities at and under the logged in user > create table #HierarchyEntitiesAtAndUnderLoggedInUser (eid int) > insert into #HierarchyEntitiesAtAndUnderLoggedInUser exec > rsp_getHierarchyEntities @EntityID > > ======== > more context of its use... > > Where a.EntityID in (select eid from > #HierarchyEntitiesAtAndUnderLoggedInUser) > ======== > I am using a temp table instead of a table variable because: > > "EXECUTE cannot be used as a source when inserting into a table variable." > > Thanks, > Doug > |