f



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.information_Schema.tables 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


0
nobody (5159)
6/30/2005 3:35:21 PM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

2 Replies
413 Views

Similar Articles

[PageSpeed] 3

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".

0
6/30/2005 3:57:01 PM
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". 


0
nobody (5159)
6/30/2005 6:12:46 PM
Reply:

Similar Artilces:

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100617.6f7b9f3e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? > > > Thanks for your help > &...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril On 10 Aug 2004, jewelk@free.fr wrote: > Hello, > > I need to copy a table from an 8i oracle database to a > sqlserver 2000 database. A few options exist. If this is one-off, just use sqlldr to drop the data to a file and then bcp to get it into SQLServer. > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? Well, I'm sure SQLServer has connectivity to Oracle? If you want to go this route, use that and do this from SQLServer. -- Galen Boyer On 10 Aug 2004 07:15:16 -0700, jewelk@free.fr (Cyril) wrote: >Hello, > >I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > >Is it possible to use the command "COPY FROM ... TO ..." ? >So, what is the correct syntax ? > > >Thanks for your help > >Cyril Read up on using the Heterogeneous Gateway to Sqlserver. -- Sybrand Bakker, Senior Oracle DBA "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100615.6371b40e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "...

MS Access VBA making multiple tables in database from a main table.
I have a database table called "tbl_personal" and struggling to VBA program a bit of code to create multiple tables in a database from the main "tbl_personal" table. In this case the 'color' column is the field I would like to look at. 1. Identify the number of unique records in column 'color' 2. For each color record that is label as red in the "tbl_personal" table, create a table called 'red' and populate with all red records in from the "tbl_personal" table. 3.Then move onto the next unique color and repeat the whole process again. 4. When all unique color tables are created in MS Access database end with a msg "batch process done" name surname age color country aa aaa 1 red UK bb bbb 10 green USA cc ccc 20 blue China dd ddd 30 red Germany ee eee 40 green France ff fff 1 blue Italy gg ggg 20 red Spain hh hhh 40 yellow Canada ii iii 34 black Italy =85 Is this doable automatically in MS Access? Many thanks. brenda Are you sure you want to do this? I would expect 3 tables vis: Table of Personel with PersonelID, Suname, FirstName, ColourID, CountryID & DateOfBirth (Age will be out of date next birthday and should be calculated), Table of Colours ColourID & Colour Table of Countries CountryID & Country. You can then use simple queries to return all the red people or people who live in UK. Am I missing something? Phil <bbcdancer@hotmail.com> wrote in message news:495c4ca6...

Global Temporary Tables
Hi, I am inserting records to a global temporary table(say A) in which a trigger is fired and im getting results from other global tempory tables(say B, C, D,..)... This is my scenario... I am able to fetch the results from the global temporary tables B,C,D,...when inserting directly in the table A from the front end. But I am not able to fetch the results from the global temporary tables B,C,D,...when inserting thru a web service (in dotnet)in the table A from the front end. Can somebody explain the reson for the above and give a solution to fetch the records from the global tempory tables thru webservice..Thanks in advance. ...

Global temporary table
Hi all! I dont know how to declare and use a global temporary table in my java stored procedure.... I used these statements: sql="declare global temporary table session.temp(recordinteger,eventtype character(20)) not logged"; Statement st=con.createStatement(); st.execute(sql); For inserting I'm trying to do.. sql="insert into session.temp values(2003,'fatal error'); st.executeUpdate(sql); But its flagging an error.With just the declare statement there is no error.But i read somewhere that a temporary table space needs to be created.I dont kn...

Global Temporary Table
Hello Oraclegurus, I would like to know if i am doing the right thing or not?? I've created a global temporary table "tempcomp". "create global temporary table tempcomp (col1 number,col2 char,col3varchar2)" . I looked into these groups and it was mentioned somewhere that default is "on commit delete rows"... I am assuming that the rows are being deleted after commit and not stored . We have a web application which would be calling a stored procedure which inserts into a global temporary some values we need the values only for temporary time i.e., till user wants to study those values . The web client users could be many and they all connect the application with their respective id but the database connectivity is using only one database userid "scott" and accessing the schema and shcema procedures. The procedure call does inserts into the temporary global table. The data that would be inserted could be like this 'bike' 'big' 'bum' etc question: If one web user queries the table .... "where col2 like 'b%' will he get the data which was inserted on execution of procedure by the first user who caused the inserts 'bike' 'bum'etc ...???? or each web user is a seperate session and the select query will be unique for each user ...????? Please let me know if i am using t...

Global Temporary Tables
Each, Am wanting to create a temporary table within a PLSQL package. Have used EXECUTE IMMEDIATE 'create global temporary table temp_status........' Problem now is that package wont compile due to insert statements for this table. Compiler has no idea about this temp table or its structure. I take it temporary tables arent designed for this use are am i missing something here? Cheers Roy Munson wrote: > Each, > > Am wanting to create a temporary table within a PLSQL package. Have > used > > EXECUTE IMMEDIATE 'create global temporary table temp_status........' > > Problem now is that package wont compile due to insert statements for > this table. Compiler has no idea about this temp table or its > structure. > > I take it temporary tables arent designed for this use are am i > missing something here? > > Cheers Please read the concepts manual about global temporary tables, and forget those M$ programming practices. There can be no valid reason to create (global temporary or other) tables, using pl/sql (a.k.a. on-the-fly). And now that you're asking - yes you completely missed the purpose of global temporary tables - as well as the Oracle transaction model, I assume. -- Regards, Frank van Bortel "Roy Munson" <mtproc@yahoo.co.uk> wrote in message news:853055e7.0504220914.5e7640b8@posting.google.com... > Each, > > Am wanting to create a temporary table within a PLSQL package...

To retrive a table from one database to and another database(same table)
hi i have emp table in one database , how to retrieve same table another database through query analizer give code ,,, Hi, use the three part notation: SELECT * FROM database.owner.Objectname (replace owner by schema for SQL2k5) HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- Hi surya While the first database is "Current" as would result from: Use FirstDatabaseName select * from OtherDatabaseName..Emp -- -Dick Christoph "surya" <suryaitha@gmail.com> wrote in message news:1142588469.061525.268810@u72g2000cwu.googlegroups.com... > hi > i have emp table in one database , how to retrieve same table another > database > through query analizer > give code ,,, > ...

Global temporary tables performance
Hello, I'm trying to use a global temporary tables with a bulk insert, and the performance for the temporary table is about 5 times LESS than those of a real table - shouldent it be the opposite way? Here is my example: -- Create real table and a temporary one create table realTable( id number(15) ); create global temporary table tmpTable(id number(15)) ON COMMIT DELETE ROWS; -- Insert 50000 records with bulk operation -- into the rea table and the temporary one, and getting the time it took declare tableSize constant number := 50000; type ty_numTable is varray (50000) of number(15); numTable ty_numTable := ty_numTable(); st pls_integer; en pls_integer; begin -- Fill up a data array numTable.extend( tableSize ); for i in 1 .. tableSize loop numTable(i) := i; end loop; -- -- Inserting data to the real table st := dbms_utility.get_time(); forall i in numTable.first .. numTable.last insert into realTable values (numTable(i)); en := dbms_utility.get_Time(); dbms_output.put_line( en-st ); -- Time taken for the insert -- -- Inserting data to the temporary table st := dbms_utility.get_time(); forall i in numTable.first .. numTable.last insert into tmpTable values (numTable(i)); en := dbms_utility.get_Time(); dbms_output.put_line( en-st ); -- Time taken for the insert end; The output is: 11 57 Do you have any idea? TIA. Ronen S. Known bug (at least I think is't known, I've mentioned here a couple of times). if you do a sn...

Global Temporary Table #2
Hello, We are running on Oracle 8.1.7.4 on Unix (AIX 4.3.3.0) I'm facing a problem with a global temporary table (on commit preserve rows) : it is going through the loop : - delete all - insert - update At the beginning of the procedure everything is going fine. I can see how the process is going on in v$session. After half an hour, the speed begins to decrease a lot and there are lot of "db file scattered read" events pointing to my temporary table. It is true that there are lots of FTS on this temporary table, but its content is very small (about 20 rows at each step of the loop) Our temporary tablespace is PERMANENT. Now the process is nearly stucked. What can we do ? What can be the cause of this problem ? Regards, Laly. On 7 Sep 2004 05:33:19 -0700, lalydba@free.fr (Laly) wrote: >Hello, > > >We are running on Oracle 8.1.7.4 on Unix (AIX 4.3.3.0) > >I'm facing a problem with a global temporary table (on commit preserve >rows) : it is going through the loop : >- delete all >- insert >- update > >At the beginning of the procedure everything is going fine. I can see >how the process is going on in v$session. > >After half an hour, the speed begins to decrease a lot and there are >lot of "db file scattered read" events pointing to my temporary table. >It is true that there are lots of FTS on this temporary table, but its >content is very small (about 20 rows at each step of the lo...

EXPLAIN and Global Temporary Tables
I have my developers explaining the stored procedures that they write using visual studio. These stored procedures are for DB2 os390. In one case, one of the developers has defined a global temporary table that he uses to put the input parms into and then uses it to join against to create a dataset. When he runs explain (both visual explain, and CA's explain) it chokes on the SESSION.TABLE saying that it doesn't exist. Is there anyway I can have them explain these when they have global temporary tables defined? ...

DECLARE GLOBAL TEMPORARY TABLE problem
A bit longwinded; apologies in advance. My client has two large iSeries machines, both at V5R2 (they may be logical partitions of the same physical machine; I'm not sure.) The DECLARE GLOBAL TEMPORARY TABLE statement in SQL behaves a little differently on the two machines, causing me problems. I am issuing the statement in the Run SQL Scripts window of the navigator; *SQL naming is in effect (this matters.) On the development machine, the table created is owned by the group profile to which my user profile belongs; there is no *GROUP authority generated, and *PUBLIC authority ...

postgreSQL temporary tables and MS Access
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 is separate connection, so previously created temporary table no longer works for next queries ? Has anybody used postgres temporary tables from MS Access ? Thanks in advance, Zlatko ...

Global Temporary Table Scalability Problem
Hi, We have a complex stored procedure that runs slowly under large volumes of data. After profiling and testing, I found that the use of a single global temporary table (i.e. one w/ only 5 records) caused the problem and that if I replaced the temp table with a permanent table, the results improved dramatically. (20-50x). Some more info: the procedure in question receives as one of its params a comma-delimited-list of "principal Ids". This list is usually very short (1-6 entries). The procedure parses the list and populates a 1field/1key temp table. The procedure then uses that table in a complicated query to return the records "which the user has access to". When I came across the fact that the global temp tables performed so poorly, I tried using a permanent table to store the "principal IDs" (i.e. same key structure). The query ran 50x quicker . (This was a hack. I'd actually need a way to store these values temporary 'per session') Two questions: 1) why these counterintuitive results? Why are global temporary tables so slow? under these conditions? Why do they perform differently from permanent tables? 2) is there any way to improve the performance of global temporary tables for these circumstances? thanks, bill milbratz william milbratz wrote: > Hi, > > We have a complex stored procedure that runs slowly under large > volumes of data. > <snip> > Some more info: > the procedure in question...

global temporary table and connection pooling
The environment I'm running is apache/tomcat and oracle 816. I'm using global temporary tablespaces in certain cases( they were created using on commit preserve rows option as it is a multipart query), and then the table is truncated after the last query has executed. I've noticed that occasionally if I run two independent queries using 2 different sessions, and the second query accesses the temp table before the first session has truncated it, I'm seeing results from the first session. I thought that the global temporary tables were supposed to be restricted to the session. Does this have anything to do with connection pooling? Any help/ideas would be greatly appreciated. http://asktom.oracle.com/pls/ask/f?p=4950:8:2524853494752269270::NO::F4950_P 8_DISPLAYID,F4950_P8_CRITERIA:4541826681375, Just because it is a multipart query doesn't mean you have to "on commit preserve rows". Do your multipart using the same session/connection and rows from the first query will still be there. Change the table to on commit delete, and make sure your session gets committed or rolled back after use. "Barnoit" <jbarney_ca@yahoo.ca> wrote in message news:cd2c60c8.0403050904.3988e24c@posting.google.com... > The environment I'm running is apache/tomcat and oracle 816. I'm using > global temporary tablespaces in certain cases( they were created using > on commit preserve rows option as it is a multipart query), and then > th...

Re: Global temporary tables performance
Hi, Does anyone know the Oracle bug number? thanks Dominic Aviv wrote: > *Hello, > > I'm trying to use a global temporary tables with a bulk insert, and > the performance for the temporary table is about 5 times LESS than > those of a real table - shouldent it be the opposite way? > > Here is my example: > > -- Create real table and a temporary one > create table realTable( id number(15) ); > create global temporary table tmpTable(id number(15)) ON COMMI > DELETE > ROWS; > > -- Insert 50000 records with bulk operation > -- into the rea table and the temporary one, and getting the time it > took > declare > tableSize constant number := 50000; > type ty_numTable is varray (50000) of number(15); > numTable ty_numTable := ty_numTable(); > st pls_integer; > en pls_integer; > begin > -- Fill up a data array > numTable.extend( tableSize ); > for i in 1 .. tableSize loop > numTable(i) := i; > end loop; > -- > -- Inserting data to the real table > st := dbms_utility.get_time(); > forall i in numTable.first .. numTable.last > insert into realTable values (numTable(i)); > en := dbms_utility.get_Time(); > dbms_output.put_line( en-st ); -- Time taken for the insert > -- > -- Inserting data to the temporary table > st := dbms_utility.get_time(); > forall i in numTable.first .. numTable.last > insert into tmpTable values (numTable(i)); > en := dbm...

Converting database to MS-SQLServer from PostGRESQL
Forgive me if this question is a bit too generic, if it is, feel free to just not respond. I have a database which has been running in PostgreSQL for a number of years at this stage which I want to port into MS SQL server. It seems that the SQL that Postgre outputs when I do a backup is not syntactically correct within MS-SQL server. My question is, does anyone have any documentation on how to convert a database from the Postgre platform to SQL server? Is it possible using an ODBC connection to import a database structure including table definitions, views etc into SQL Server? Failing this, does anyone have any suggestions on where I might start - I did attempt to go through the SQL code and modify it to suit SQL server, but it's about 3,500 lines of code excluding the insert statements (which themselves are also wrong) and almost every line needs something changed when comparing SQL syntax from Postgre to MSSQL server Thanks in advance for any comments/suggestions. Engada. -- Posted via a free Usenet account from http://www.teranews.com Engada wrote: > I have a database which has been running in PostgreSQL for a number of > years at this stage which I want to port into MS SQL server. > > It seems that the SQL that Postgre outputs when I do a backup is not > syntactically correct within MS-SQL server. pg_dump has a number of flags that may help, e.g. --inserts What specific types of syntax errors do you encounter? Googling (Postgr...

Error while declaring a global temporary table
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, P_TEST VARCHAR(2) ) RETURNS VARCHAR(1000) SPECIFIC GET_RULE LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA STATIC DISPATCH CALLED ON NULL INPUT EXTERNAL ACTION INHERIT SPECIAL REGISTERS BEGIN ATOMIC DECLARE v_TEST VARCHAR(4000); DECLARE v_TEST_Select VARCHAR(4000); DECLARE v_TEST_Sort VARCHAR(1000); DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST (id INTEGER ) ON COMMIT PRESERVE ROWS; SET v_TEST_Select = NULL; SET v_TEST_Sort = NULL; RETURN(v_TEST); END; It give an error SQL 0104N "An unexpected token "Table" was found following "..are Global Temporary". What is the problem in that? Rahul B wrote: > 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, > P_TEST VARCHAR(2) > ) > RETURNS VARCHAR(1000) > SPECIFIC GET_RULE > LANGUAGE SQL > NOT DETERMINISTIC > READS SQL DATA > STATIC DISPATCH > CALLED ON NULL INPUT > EXTERNAL ACTION > INHERIT SPECIAL REGISTERS > BEGIN ATOMIC > DECLARE v_TEST VARC...

Global Temporary Table performance with inserts.
I am using an Oracle Temporary table to generate a tree of data dependencies. I noticed that Inserts are significantly longer into the Temporary table than if I replaced the Global Temporary Table with a permanent table (just as a test case), almost by a factor of 50. The number of rows being inserted are approx. 100. Has any one experienced this issue and what are the workarounds? I am using Oracle 8.1.7 and have created the Global Temporary Table with "on commit preserve rows" clause. Thank you for your help in advance. Hector wrote: > I am using an Oracle Temporary table to generate a tree of data > dependencies. I noticed that Inserts are significantly longer into the > Temporary table than if I replaced the Global Temporary Table with a > permanent table (just as a test case), almost by a factor of 50. The > number of rows being inserted are approx. 100. Has any one experienced > this issue and what are the workarounds? I am using Oracle 8.1.7 and > have created the Global Temporary Table with "on commit preserve rows" > clause. Thank you for your help in advance. I've never seen this behavior. And with 100 rows I'm quite frankly amazed you can detect the time required at all. But do you really need the "on commit preserve rows"? -- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damorgan@x.washington.e...

Reg: Owner for Declared Global Temporary tables
I am using below Query to find the declared global temporary tables in a Database: Below query is for DB2 version 9.1 , you can get same information by taking snapshot of the tables. SELECT * from SYSIBMADM.SNAPTAB WHERE TABSCHEMA = 'SESSION' ORDER BY TABNAME, DBPARTITIONNUM; Is it possible to find the Owner of the declared global Temporary table ? ( who actually created it) Thanks Ravi Ravi wrote: > I am using below Query to find the declared global temporary tables in > a Database: > Below query is for DB2 version 9.1 , you can get same information by > taking snapshot of the tables. > > SELECT * from SYSIBMADM.SNAPTAB WHERE TABSCHEMA = 'SESSION' ORDER BY > TABNAME, DBPARTITIONNUM; > > Is it possible to find the Owner of the declared global Temporary > table ? ( who actually created it) Hmm.. not sure what you are ultimately after, but if you want to enforce a quota you could control it through the user temp table space. I.e. give each group of users a different temp space. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab On Aug 30, 8:12=A0am, Serge Rielau <srie...@ca.ibm.com> wrote: > Ravi wrote: > > I am using below Query to find the declaredglobaltemporarytablesin > > a Database: > > Below query is for DB2 version 9.1 , you can get same information by > > taking snapshot of thetables. > > > SELECT =A0* from...

global temporary table --> invalid ROWID?
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 following exception: java.io.IOException: ORA-01410: invalid ROWID ORA-06512: at "SYS.DBMS_LOB", line 751 ORA-06512: at line 1 at oracle.jdbc.dbaccess.DBError.SQLToIOException(DBError.java:625) at oracle.jdbc.driver.OracleBlobInputStream.needBytes(OracleBlobInputStream.java:179) at oracle.jdbc.driver.OracleBufferedStream.read(OracleBufferedStream.java:113) at oracle.jdbc.driver.OracleBufferedStream.read(OracleBufferedStream.java:91) ... Just for clarity, here is basically the code I'm using: CallableStatement cstmt2 = db.prepareCall("commit"); CallableStatement cstmt = db.prepareCall("create global temporary table temp_table (blobid NUMBER unique, blob_col BLOB) on commit preserve rows"); cstmt.execute(); cstmt.close(); cstmt2.execute(); CallableStatement cstmt1 = db.prepareCall("insert into temp_table values (1, empty_blob())"); cstmt1.execute(); cstmt1.close(); cstmt2.execute(); cstmt2.close(); CallableStatement plsqlblock = db.prepareCall(stringThatFillsInEmptyBlobInTempTable); plsqlblock.execute();...

Temporary Tables, Permanent Tables
Hallo, we have 4 process which write permanent to the database. The insert into a temporary table this is fast, the insert into permanent table (nologging) is slow. Thanks Thomas Thomas, a little more information is necessary for anyone to offer you useful advice. First, what is the Oracle edition, version, and what platform is Oracle running on. Describe the process in a little more detail and include the insert statement. Include what language/tool the application is written in. HTH -- Mark D Powell -- On Wed, 22 Feb 2006 07:14:12 -0800, tb wrote: > Hallo, > > we have 4 process which write permanent to the database. > The insert into a temporary table this is fast, the insert into > permanent table (nologging) is slow. Four legs are good, two legs are bad. > > Thanks Thomas You're welcome. -- http://www.mgogala.com Mladen Gogala wrote: > On Wed, 22 Feb 2006 07:14:12 -0800, tb wrote: > > > Hallo, > > > > we have 4 process which write permanent to the database. > > The insert into a temporary table this is fast, the insert into > > permanent table (nologging) is slow. > > Four legs are good, two legs are bad. > Soon it is impossible to tell the management from the end users..... ...

Declared Global Temporary Table in multiple Stored Procedures
Hi, As I understand it, Declared Global Temporary Tables (DGTTs) have a scope that is session/connection based. Using the same connection, I have discovered that if I declare a DGTT in one stored procedure, then I can't create a second stored procedure that uses the DGTT, as the DGTT is not "visible". The only way around this is to use dynamic SQL in the second stored procedure. In this way (using dynamic sql) i can create and use the DGTT across multiple stored procedures. Is there perhaps a way to make DGTT visible across multiple stored procedures using static sql, or is this an inherint limitation of DGTTs. Cheers Otto Otto Carl Marte wrote: > Hi, > > As I understand it, Declared Global Temporary Tables (DGTTs) have a > scope that is session/connection based. > Using the same connection, I have discovered that if I declare a DGTT > in one stored procedure, then I can't create a second stored procedure > that uses the DGTT, as the DGTT is not "visible". The only way around > this is to use dynamic SQL in the second stored procedure. In this way > (using dynamic sql) i can create and use the DGTT across multiple > stored procedures. > > Is there perhaps a way to make DGTT visible across multiple stored > procedures using static sql, or is this an inherint limitation of > DGTTs. The limitation is this: To create a procedure that uses a DGTT the table must be declare...

RE: [Info-ingres] global temporary table disappears??
Hi Dennis, We use temporary tables to do exactly the same thing here without any problems. Make sure you're prefixing your temporary table names with session. I'd be happy to take a look at your sql if you want to post it to the group. Regards Jon -----Original Message----- From: info-ingres-admin@cariboulake.com [mailto:info-ingres-admin@cariboulake.com] On Behalf Of Dennis Roesler Sent: 23 February 2005 14:55 To: info-ingres@cariboulake.com Subject: [Info-ingres] global temporary table disappears?? HP-UX 11.11 Ingres 2.6/0305 I'm using a script that creates several global temporary tables. Among other temporary tables created, part of the process creates one table and then creates a second table from some of the data from the previous one. I then do an update to the second one from the first with some additional data; an exercise in getting a unique set of data :-(. The update to the second table complains with the "tuple at tid already deleted" error. And then the next statement that tries to update a different table from the second table complains that the second table doesn't exist or isn't owned by me. This is all happening in the same session so I can't figure out why the one table keeps disappearing. When it's first declared there is no complaint that it couldn't be created, and in fact returns the n rows message. I understand that global temporary tables will be ...

Web resources about - Global temporary table and SP - comp.databases.ms-sqlserver

Temporary - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

Report: Facebook To Open Temporary Vancouver Office To Recruit, Train Engineering Talent
Facebook will set up a temporary office in Vancouver , British Columbia, where it will conduct “boot camps” to train recent software engineering ...

Facebook brings post insights back to Pages Manager app after temporary removal
A recent update to Pages Manager for iOS has returned detailed post insights to the product after a version earlier this month was released without ...

App Store - Bridgeway Temporary Housing
Get Bridgeway Temporary Housing on the App Store. See screenshots and ratings, and read customer reviews.

Temporary public toilets 1 - Flickr - Photo Sharing!
These are positioned by the city every year over the canals. They are located on the river bank and are therefore subject to flooding every few ...

人人建築 Architecture for the Mass - 尼泊爾臨時住所原型 Temporary Shelter Prototype in Nepal - YouTube
Charles Lai from Hong Kong and Takehiko Suzuki from Tokyo completed a prototype for a rapid-erected shelter with bamboo structure and other local ...

Temporary speed reduction on Abu Dhabi-Dubai highway - The National
Radars along a stretch of the road will be set at 121kph, down from 141kph, as roadwork is carried out.

Charlotte Pass accept temporary lease extension
Charlotte Pass ski village has accepted a 28-month extension on their existing lease with the NSW National Parks and Wildlife Service in a deal ...

Morrison says deficit levy 'only temporary'
Treasurer Scott Morrison says a 'deficit levy' on high-income earners was always a temporary measure.

Apple Watch Diary: A (temporary) world without Apple Pay
I said last summer in my Apple Watch Diary series that Apple Pay arriving in the UK was the tipping point for me in transforming the Watch from ...

Resources last updated: 3/22/2016 5:46:38 AM