May 16, 2002 at 11:32 am
I have a stored procedure that is giving me an 'invalid object name #tablename' error.
One of the first things this stored procedure does is create this temp table and loads it with data. Later on, it opens and closes a cursor that updates this temp table. Right after the close of the cursor, I am getting this error? Is this a nesting level problem? Why am I getting this error?
May 16, 2002 at 11:34 am
Can you post your Script it's kind of hard to tell without seeing it.
At least for this type of error.
quote:
I have a stored procedure that is giving me an 'invalid object name #tablename' error.One of the first things this stored procedure does is create this temp table and loads it with data. Later on, it opens and closes a cursor that updates this temp table. Right after the close of the cursor, I am getting this error? Is this a nesting level problem? Why am I getting this error?
May 16, 2002 at 11:34 am
Here it is. The name of the temp table is #SaAging.
May 16, 2002 at 11:38 am
Can you post complete CREATE PROC Scipt
nor just the table name.
quote:
Here it is. The name of the temp table is #SaAging.
May 16, 2002 at 11:39 am
Sorry...
CREATE PROCEDURE dbo.sp_saAccountAging
@AsOfDate datetime,
@CampusID int,
@TermID int = 0,
@StatusIDs varchar(1000),
@ProgramIDs varchar(1000)
AS
Set NoCount On
-- Get the options for the where clause
Declare @WhereClause varchar(1000)
Declare @SQL varchar(2000)
--Select @WhereClause = rtrim(WhereClause)
-- From SyReportOptions Where SyReportOptionsID = @OptionsID
-- Create a temporary table for the aging
--Create Table #SaAging
--(SyStudentID int,
-- SyCampusID int,
-- ARBalance money,
-- BalCurrent money,
-- BalOver30 money,
-- BalOver60 money,
-- BalOver90 money,
-- BalOver120 money)
-- Set parameters
if @StatusIDs = 'ALL'
Select @StatusIDs = ''
if @ProgramIDs = 'ALL'
Select @ProgramIDs = ''
-- define the query to update the table
Select @SQL = '
Select SyStudent.SyStudentID as SyStudentID,
' + rtrim(convert(char(12),@CampusID)) + ' as SyCampusID,
convert(money,0) as ARBalance,
convert(money,0) as BalCurrent,
convert(money,0) as BalOVer30,
convert(money,0) as BalOVer60,
convert(money,0) as BalOVer90,
convert(money,0) as BalOVer120
into #SaAging
From SyStudent (nolock)
Where Exists (Select * from SaTrans (nolock)
where satrans.systudentid = systudent.systudentid
and satrans.sycampusid = ' + convert(char(10),@CampusID)
If @TermID > 0
BEGIN
Select @SQL = @SQL + ' AND SaTrans.AdTermID = ' + convert(char(10),@TermID)
END
Select @SQL = @SQL + ' ) '
If rtrim(@ProgramIDs) > ''
BEGIN
Select @SQL = @SQL + ' AND SyStudent.AdProgramID in (' + @ProgramIDs + ') '
END
If rtrim(@StatusIDs) > ''
Select @SQL = @SQL + ' AND SyStudent.SySchoolStatusID in (' + @StatusIDs + ') '
-- Execute the query to build the work table
exec(@SQL)
-- Do the Aging
Declare C Cursor READ_ONLY For
Select SaTrans.SyStudentID,
SaTrans.SyCampusID,
Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END As Amount,
Case SaTrans.Type
When 'P' Then 3000
When 'C' Then 3000
Else DateDiff(dd, SaTrans.Date, @AsOFDate) END As ItemAge
From SaTrans (NoLock)
Where Convert(DateTime, Convert(char(10), SaTrans.[Date],101)) <= @AsOfDate
And (SaTrans.AdTermID = @TermID OR @TermID = 0)
And SaTrans.SyCampusID = @CampusID
Declare @StudentID int,
@Campus int,
@Amount money,
@ItemAge float
Open C
Fetch Next From C
Into @StudentID, @Campus, @Amount, @ItemAge
While @@Fetch_Status = 0
BEGIN
Update #SaAging
Set ARBalance = ARBalance + @Amount,
BalCurrent = BalCurrent + Case When @ItemAge >= 0 And @ItemAge < 31 Then @Amount else 0 End,
BalOver30 = BalOver30 + Case When @ItemAge > 30 And @ItemAge < 61 Then @Amount else 0 End,
BalOver60 = BalOver60 + Case When @ItemAge > 60 And @ItemAge < 91 Then @Amount else 0 End,
BalOver90 = BalOver90 + Case When @ItemAge > 90 And @ItemAge < 121 Then @Amount else 0 End,
BalOver120 = BalOver120 + Case When @ItemAge > 120 Then @Amount else 0 End
Where SyStudentID = @StudentID
AND SyCampusID = @Campus
Fetch Next From C
Into @StudentID, @Campus, @Amount, @ItemAge
END
Close C
Deallocate C
-- Update Aging Work table to move credit balance to current
Update #SaAging
Set BalOver90 = BalOver90 + BalOver120,
BalOVer120 = 0
Where BalOver120 < 0
Update #SaAging
Set BalOver60 = BalOver60 + BalOver90,
BalOVer90 = 0
Where BalOver90 < 0
Update #SaAging
Set BalOver30 = BalOver30 + BalOver60,
BalOver60 = 0
Where BalOver60 < 0
Update #SaAging
Set BalCurrent = BalCurrent + BalOver30,
BalOVer30 = 0
Where BalOver30 < 0
-- Return the Aging recordset
Select @SQL = 'Select #SaAging.* '
Select @SQL = @SQL + ', rtrim(SyStudent.LastName) + '', '' + rtrim(SyStudent.FirstName) As StudentName '
Select @SQL = @SQL + ', SyStudent.SSN '
Select @SQL = @SQL + ', SyStudent.StuNum '
Select @SQL = @SQL + ', SyCampus.Descrip As CampusDescrip '
Select @SQL = @SQL + ', AdProgram.Descrip As ProgramDescrip '
Select @SQL = @SQL + ', SySchoolStatus.Descrip As StatusDescrip '
Select @SQL = @SQL + ' From #SaAging '
Select @SQL = @SQL + ' Join SyStudent (nolock) ON SyStudent.SyStudentID = #SaAging.SyStudentID '
Select @SQL = @SQL + ' Left Join SyCampus (nolock) ON SyCampus.SyCampusID = #SaAging.SyCampusID '
Select @SQL = @SQL + ' Left Join AdProgram (nolock) ON AdProgram.AdProgramID = SyStudent.AdProgramID '
Select @SQL = @SQL + ' Left Join SySchoolStatus (nolock) ON SySchoolStatus.SySchoolStatusID = SyStudent.SySchoolStatusID '
Select @SQL = @SQL + ' Where #SaAging.ARBalance != 0 '
Select @SQL = @SQL + ' Order By SyStudent.Lastname, SyStudent.FirstName '
exec(@SQL)
Drop Table #SaAging
Return
GO
May 16, 2002 at 11:43 am
You have to use a static table not a temp table if you want to use EXECUTE()
Execute func does not see temporary table neither local or global.
And I do not understand why you are executing it as a string, I did not see any variables that you are putting in to build the select statement. it looks static to me.
Instead of using Execute just run the select by itself.
May 16, 2002 at 11:47 am
the funny thing is that it was working when using a global temp table, but it would only work for 1 concurrent user. But when I changed it to a local, this error happened.
May 16, 2002 at 11:58 am
I tried it with a static table and got the same error.
May 16, 2002 at 1:04 pm
I got it to work! I tried it with a static CREATE TABLE statement for the temp table. It looks like the dynamic SQL has a problem generating temp tables and keeping them in the session when you use cursors to update them.
Thanks!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply