473,560 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Global temporary table and SP

Hello all,

I'm using SS2K on W2K.

Brieffing: Many months ago, I created a stored procedure only used by those
with admin rights in SS. Now, someone else (without admin rights) has to run
it. I gave him rigth to execute the SP but, at the second and more
execution, he got a error message concerning a temp table already existing
(see further).

The SP:
------------------------------------------------------
CREATE PROCEDURE MySP
@Type INT

DECLARE @strSQL AS VARCHAR(4000)

IF EXISTS (SELECT table_name FROM tempdb.informat ion_Schema.tabl es WHERE
table_name = '##MyTmpTable')
DROP TABLE ##MyTmpTable

SELECT @strSQL = 'SELECT MyField1, MyField2, MyField3 INTO ##MyTmpTable FROM
MyTable'

EXECUTE(@strSQL )

IF @Type = 1
SELECT MyField1
FROM ##MyTmpTable
ELSE IF @Type = 2
SELECT MyField2
FROM ##MyTmpTable
ELSE
SELECT MyField3
FROM ##MyTmpTable
GO
------------------------------------------------------

The error I got on the second time the user run the sp is: "Table
##MyTmpTable already exists." The front-end where this SP is run is A97.
That's where I got this message. This SP looks like a simple SELECT query
from A97 users perspective.

Please, do no argue about the way of doing the work done! It is simplified
at most in order to make it short and easy to read. I have to use the
command "EXECUTE(String )" and, because of this, I connot use a local
temporary table instead of a global one.

I suspect non-admin user cannot drop global temporary table, but the error
message makes me believe that this code line is not even run, as if the
condition "IF EXISTS(...)" return false even if the table actualy exists.

Anybody can help about this? What should I do to solve this problem?

Yannick
Jul 23 '05 #1
2 3370
If two people attempt to execute that procedure at the same time they
will get a conflict because it already exists. You could wrap the whole
thing in an execute statement and change the global temporary table to
a local (using a single #).

Also, try creating the temporary table first and inserting data into it
rather than creating it on the fly. You reduce the amount of locks on
"MyTable".

Jul 23 '05 #2
It solved the problem. Many thanks.

Yannick
Also, try creating the temporary table first and inserting data into it
rather than creating it on the fly. You reduce the amount of locks on
"MyTable".

Jul 23 '05 #3

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

Similar topics

4
13689
by: Corrine | last post by:
Hi, I am creating a global temporary table that is session-specific. I insert a BLOB into this table, and then select the BLOB from this table into a ResultSet. The ResultSet sees this BLOB object, and I am able to get the binary input stream from this blob. However, when I invoke InputStream.read(byte) on this input stream, I get the...
5
33615
by: Jim Garrison | last post by:
Scenario: 1) Create a GLOBAL TEMPORARY table and populate it with one (1) row. 2) Join that table to another with about 1 million rows. The join condition selects a few hundred rows. Performance: 4 seconds, the system is doing a full-table scan of the second table, and the Explain Plan output
10
10797
by: Ranga | last post by:
I was unable to run the statement "CREATE GLOBAL TEMPORARY TABLE" on unix version of DB2, it gave the follwing error db2 => create global temporary table temp ( OGI_SYS_NR char(8) ) DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:...
2
2343
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,...
2
3345
by: David Parker | last post by:
In our application we have a table that tracks network sessions. The usage is: 1) create a session record 2) read/update the record several times during the session 3) delete the session record when the session ends Update activity on this table is going to be pretty intense, and the transient nature of the data makes it a good candidate...
2
4111
by: chettiar | last post by:
I am creating a procedure A which is creating a global temporary table DECLARE GLOBAL TEMPORARY TABLE session.temp (Service CHAR(2), CustomerServiceTypeId INTEGER) WITH REPLACE ON COMMIT PRESERVE ROWS; I am able to compile the proceudre. But when I try to compile procedure B which is referencing the temporary table in procedure A, I get...
0
2665
by: sgueder | last post by:
Hi there, hope anybody can help me: I want to create a temporary table, want to fill that table with data and finally I want to use that table as my base-table for running some selects on it (DB2). So I used the following SQL: DECLARE GLOBAL TEMPORARY TABLE SESSION.SG_ADVICE (ID DECIMAL(11,0), ZAEHLER INTEGER) ON...
5
8802
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,
1
1860
by: Ragianand | last post by:
Hi All, I have created a stored procedure which populates a global emporary table...Now i want to write a function which returns a view that contains the data from the global temporary table.Can anyoune suggest a solution for this...Any sample code to return a view from a function with data from a global temporary table in another procedure. ...
0
7631
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...
0
7551
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7840
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8063
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...
1
7595
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...
0
7916
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...
0
5175
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...
0
3605
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...
0
874
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...

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.