473,883 Members | 2,484 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Temporary tables

If a stored procedure invokes another stored procedure that creates a
temporary table why can't the calling procedure see the temporary table?
CREATE PROCEDURE dbo.GetTemp
AS
CREATE TABLE #Test
(
[id] int not null identity,
[name] as char(4)
)
INSERT INTO #Test ([name]) VALUES ('Test')
CREATE PROCEDURE dbo.Test
AS
EXEC dbo.GetTemp
SELECT * FROM #Test -- Invalid object name '#Test'.

Thanks,
TP
Jul 20 '05 #1
4 14421
SQL 2000&7.0 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."

Try creating temp table in dbo.Test stored procedure. That way you'll be able to insert records into it
in dbo.GetTemp stored procedure and fetch those records in dbo.Test.

--
Dean Savovic
www.teched.hr
"tperovic" <tp******@compu mation.com> wrote in message news:vTjpb.9136 7$e01.318593@at tbi_s02...
If a stored procedure invokes another stored procedure that creates a
temporary table why can't the calling procedure see the temporary table?
CREATE PROCEDURE dbo.GetTemp
AS
CREATE TABLE #Test
(
[id] int not null identity,
[name] as char(4)
)
INSERT INTO #Test ([name]) VALUES ('Test')
CREATE PROCEDURE dbo.Test
AS
EXEC dbo.GetTemp
SELECT * FROM #Test -- Invalid object name '#Test'.

Thanks,
TP

Jul 20 '05 #2
"tperovic" <tp******@compu mation.com> wrote in message news:<vTjpb.913 67$e01.318593@a ttbi_s02>...
If a stored procedure invokes another stored procedure that creates a
temporary table why can't the calling procedure see the temporary table?
CREATE PROCEDURE dbo.GetTemp
AS
CREATE TABLE #Test
(
[id] int not null identity,
[name] as char(4)
)
INSERT INTO #Test ([name]) VALUES ('Test')
CREATE PROCEDURE dbo.Test
AS
EXEC dbo.GetTemp
SELECT * FROM #Test -- Invalid object name '#Test'.

Thanks,
TP


A temp table created in a stored procedure is dropped when the
procedure completes - see CREATE TABLE in Books Online. You can either
create the table in the 'outer' stored procedure, or perhaps some of
the approaches described here may be useful:

http://www.algonet.se/~sommar/share_data.html

Simon
Jul 20 '05 #3
I looked in BOL but couldn't find it.

Hvala vam za informaciju.

"Dean Savovic" <aj************ ****@dati.hmhmh .com> wrote in message
news:bo******** **@brown.net4u. hr...
SQL 2000&7.0 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."
Try creating temp table in dbo.Test stored procedure. That way you'll be able to insert records into it in dbo.GetTemp stored procedure and fetch those records in dbo.Test.

--
Dean Savovic
www.teched.hr
"tperovic" <tp******@compu mation.com> wrote in message

news:vTjpb.9136 7$e01.318593@at tbi_s02...
If a stored procedure invokes another stored procedure that creates a
temporary table why can't the calling procedure see the temporary table?
CREATE PROCEDURE dbo.GetTemp
AS
CREATE TABLE #Test
(
[id] int not null identity,
[name] as char(4)
)
INSERT INTO #Test ([name]) VALUES ('Test')
CREATE PROCEDURE dbo.Test
AS
EXEC dbo.GetTemp
SELECT * FROM #Test -- Invalid object name '#Test'.

Thanks,
TP


Jul 20 '05 #4
Nema na cemu.

--
Dean Savovic
www.teched.hr
"tperovic" <tp******@compu mation.com> wrote in message news:8EXpb.1080 69$Fm2.92888@at tbi_s04...
I looked in BOL but couldn't find it.

Hvala vam za informaciju.

"Dean Savovic" <aj************ ****@dati.hmhmh .com> wrote in message
news:bo******** **@brown.net4u. hr...
SQL 2000&7.0 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."

Try creating temp table in dbo.Test stored procedure. That way you'll be

able to insert records into it
in dbo.GetTemp stored procedure and fetch those records in dbo.Test.

--
Dean Savovic
www.teched.hr
"tperovic" <tp******@compu mation.com> wrote in message

news:vTjpb.9136 7$e01.318593@at tbi_s02...
If a stored procedure invokes another stored procedure that creates a
temporary table why can't the calling procedure see the temporary table?
CREATE PROCEDURE dbo.GetTemp
AS
CREATE TABLE #Test
(
[id] int not null identity,
[name] as char(4)
)
INSERT INTO #Test ([name]) VALUES ('Test')
CREATE PROCEDURE dbo.Test
AS
EXEC dbo.GetTemp
SELECT * FROM #Test -- Invalid object name '#Test'.

Thanks,
TP



Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2095
by: Soefara | last post by:
Dear Sirs, I have been developing an application on Windows with MySQL 3.23, making use of temporary tables. Now when I try to port the application to a Unix box running also MySQL 3.23, I keep running into "access denied" errors with the queries which employ temporary tables. This only happens with CREATE TEMPORARY TABLE type queries, all other queries work fine.
2
4025
by: Ryan | last post by:
Just a quicky about temporarary tables. If using QA, when you create a temporary table, it gets dropped if you close the query. Otherwise you need to state 'DROP TABLE myTable' so that you can re-run the query without the table being there. Sometimes, you can have quite lengthy SQL statements (in a series) with various drop table sections throughout the query. Ideally you would put these all at the end, but sometimes you will need to...
11
16303
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the equivalent to the SQL 'with' using TSQL? If there is not one, what is the TSQL solution to creating a temporary table that is associated with an SQL statement? Examples would be appreciated. Thank you!!
1
2614
by: Sampath Reddy | last post by:
Hi Everybody, We are using UDB v8.1 I will explain about my Stored procedures which we are executing in UDB AIX box. We have 3 millions(apporox) of data in 22 tables. By applying the business logic through Stored procedures on 22 tables and writing into 3 new tables. We have used all temporary tables except starting 22 tables. The Stored procedures we have used nearly 6 temporary tables to handle the business logic. Finally we are loading...
2
6984
by: Keith Watson | last post by:
Hi, we are currently implementing an application running on DB2 V7 on Z/OS using largely COBOL stored procedures, managed using WLM. Some of these stored procedures declared global temporary tables, which are declared with ON COMMIT DELETE ROWS to perform work on and then return these temporary tables to the client (which is a message driven EJB connecting via DB2 connect). The client reads the data in the result sets, creates some XML...
2
2370
by: Chuck Crews | last post by:
I am interested in declaring a global temporary table within an application. The application processes 1 set of 600 or less rows each iteration. Multiple programs can and do call this one application at the same time. Today, we are using a permanent DB2 table and getting all kinds of locking even with row level locking on. In addition, our performance is slow as we do two inserts and two deletes on each row per program iteration. ...
3
3281
by: Mike Ridley | last post by:
I have 2 databases called (for example) "progs.mdb" and "files.mdb". Both these databases reside on computer "myserver". The progs database has links to the tables in the files database. "myclient1" runs a shortcut "msaccess.exe \\myserver\progs.mdb /x startmacro". "myclient2" runs his shortcut "msaccess.exe \\myserver\progs.mdb /x startmacro". If I use a temporary table I take it that it will be created on "myserver" in the...
0
1366
by: Zlatko Matić | last post by:
I tried to work with postgres temporary tables from MS Access, but unsuccessfully... I was able to create temporary table by pass-through query, also I succeeded in creating linked table through code, but when I try to open it, an error apears and Access says there is no schema... Also, I couldn't link table using ODBC wizard... Can someone explain why I can't use temporary tables from MS Access ? Does it mean that every query execution...
1
27044
by: Stefan van Roosmalen | last post by:
Hi there, Is there a way to list the TEMPORATY tables? I have tried SHOW TABLES, but this command only list the regular tables. Thank you very much for your answer. Regards, Stefan.
5
8850
by: Rahul B | last post by:
Hi, I have very little knowledge about creating Procedures/functions in DB2. When i tried to create the test function like CREATE FUNCTION GET_TEST (P_TEST_ID INTEGER, P_SEL_OR_SORT INTEGER,
0
9944
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10762
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10863
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10422
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7977
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7136
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5807
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4228
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3241
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.