Create temporary table for specific user sessions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Create temporary table for specific user sessions

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-07-12 : 16:16:33

Hello,

I have a stored procedure which creates a local temporary table which I would like to access after the stored procedure has finished executing. However I don't want other users to access this table (global table is thus out). Is there anyway to achieve this?

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-12 : 16:43:53

You can create the temp table before the sp is executed and have the sp insert into it rather than creating it. Then it will be in the scope of the session that created it.

--Ken
Your Kung-Fu is not strong. -- 'The Core'

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-12 : 17:04:45

quote:
Originally posted by kselvia

You can create the temp table before the sp is executed and have the sp insert into it rather than creating it. Then it will be in the scope of the session that created it.

How do you get a stored procedure to see a temp table created from another session? Is this possible?

-ec

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-12 : 17:06:30

Not another session. The session that invokes the stored procedure can create it, then the sp will see it, and it will still exist after the sp exits, until the session that created it disconnects or drops it.

--Ken
Your Kung-Fu is not strong. -- 'The Core'

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-07-12 : 17:32:59

Hi,

Not sure if that will work in my case:

The table is created in one SP:


CREATE TABLE #MyTableVar (January nvarchar(10))
INSERT #MyTableVar SELECT January FROM dbo.CrosstabFunc()

SELECT  January as A FROM #MyTableVar

which is used by a report.

I then want to reuse the resultset, but in another SP:


SELECT January FROM  #MyTableVar

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-12 : 17:37:50

CREATE TABLE #MyTableVar (January nvarchar(10))

exec sp_1
insert into #MyTableVar

... view report etc.

exec sp_2
... select * from #MyTableVar

Inorder to compile sp_1 and sp_2 you will have to run

CREATE TABLE #MyTableVar (January nvarchar(10))

first or they will not be able to resolve #MyTableVar

--Ken
Your Kung-Fu is not strong. -- 'The Core'

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-07-12 : 22:21:46

Hi,

That scenario wouldn't work, because the SPs are invoked using two distinct passthrough queries, one which serves as the recordsource for a main report, and the other one for a subreport.

1. My understanding of temporary tables is that a temporary table is only viewable within the user session in which it was created. So why can't my second passthrough query see the temp table created by the first passthrough query but they are both part of an application connected to my SQL Server database?

2. Another option may be to create a table and give it a unique name for the application which is currently connected to the database (MyTableVarblinton25). So when my subreport needs to access this table, it knows that it is called MyTableVarblinton25. However, since it is possible for 2 users to have the same username, is there some unique value that I can use for this purpose?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources