Windows Usenet Topics on Windows Server Programming

07-28-2003

Hi All here,

I'm writing a store procedure to create a temp table. After executing
the store proc, I try to retrieve the data from the temp table in same
session. A message "Invalid object" is shown. Would anyone tell me what's
wrong with that? Thks

Rdgs.
Ellis

07-28-2003

You might post your code for more help, is your created
temp table start with the double pound (##)? are you a
DBO? If not are you using fully qualified name?

Ray Higdon MCSE, MCDBA, CCNA

Quote:

-----Original Message-----
Hi All here,

I'm writing a store procedure to create a temp

table. After executing

Quote:

the store proc, I try to retrieve the data from the temp

table in same

Quote:

session. A message "Invalid object" is shown. Would

anyone tell me what's

Quote:

wrong with that? Thks

Rdgs.
Ellis

.

07-28-2003

From BOL:

"A local temporary table created in a stored procedure is dropped
automatically when the stored procedure completes. The table can be
referenced by any nested stored procedures executed by the stored procedure
that created the table. The table cannot be referenced by the process which
called the stored procedure that created the table."

HTH

"Ellis Yu" <ellis.yu@transfield.comwrote in message
news:umSn$8OVDHA.2016@TK2MSFTNGP09.phx.gbl...

Quote:

Here's the source code of my stored proc.

stored proc.
=========
CREATE PROCEDURE gltrxdet_sp
@EndDate int

AS

BEGIN

CREATE TABLE #balrange (
account_code varchar(32) NOT NULL,
reference_code varchar(32) NOT NULL,
date_applied int NOT NULL,
balance float NOT NULL)

INSERT #balrange(
account_code,
reference_code,
date_applied,
balance)
SELECT gltrxdet.account_code, gltrxdet.reference_code,
gltrx.date_applied, gltrxdet.balance as balance
FROM tg_tsi..gltrx gltrx INNER JOIN
tg_tsi..gltrxdet gltrxdet ON
gltrx.journal_ctrl_num = gltrxdet.journal_ctrl_num INNER JOIN
tg_tsi..glref glref ON
gltrxdet.reference_code = glref.reference_code
WHERE glref.reference_type = 'VSLVOY'
AND gltrx.date_applied <= @EndDate
UNION ALL
SELECT gltrxdet.account_code, gltrxdet.reference_code,
gltrx.date_applied, gltrxdet.balance as balance
FROM ter_cape..gltrx gltrx INNER JOIN
ter_cape..gltrxdet gltrxdet ON
gltrx.journal_ctrl_num = gltrxdet.journal_ctrl_num INNER JOIN
ter_cape..glref glref ON
gltrxdet.reference_code = glref.reference_code
WHERE glref.reference_type = 'VSLVOY'
AND gltrx.date_applied <= @EndDate

END
-------------------------------------------------
I execute the above proc. in SQL Query Analyser, and then select the table
#balrange. Msg "Invalid Object" is shown. But if I type the create and
insert data into temp table statement in SQL Query Analyser directly

instead

Quote:

of in stored procedure, I can retrieve the data from temp table. Any idea?

"Ellis Yu" <ellis.yu@transfield.comwrote in message
news:OQLkoXOVDHA.2032@TK2MSFTNGP11.phx.gbl...

Quote:

Hi All here,

I'm writing a store procedure to create a temp table. After

executing

Quote:

the store proc, I try to retrieve the data from the temp table in same
session. A message "Invalid object" is shown. Would anyone tell me

what's

Quote:

wrong with that? Thks

Rdgs.
Ellis

07-28-2003	#1
..l.. ..	Can't Find Temp Table Hi All here, I'm writing a store procedure to create a temp table. After executing the store proc, I try to retrieve the data from the temp table in same session. A message "Invalid object" is shown. Would anyone tell me what's wrong with that? Thks Rdgs. Ellis

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode