Problems with temporary tables

Problems with temporary tables

Post by Pablo Sanch » Thu, 07 Dec 1995 04:00:00




> We have a developer who is working with temporary tables. He create one
> using a create table statement with the # sign like this:
> CREATE TABLE #TEMP_EMPLOYEE_HRS( COL_1......). After creating the table
> he is unable to drop it with a drop table command (sa can't drop it
> either).

I thought that there was a bug like this????  What does TS say?

Check this out:

501.ppp-mew-[pablo]: isql -U sa -S ACE -P XXXX -w133
1> create table #temp_employee_hrs(col_1 int)
2> go
1> drop table #temp_employee_hrs
2> go
1>

Pablo Sanchez              | Ph # (415) 933.3812        Fax # (415) 933.2821

===============================================================================
"I am accountable for my actions."
   - pablo

 
 
 

Problems with temporary tables

Post by Wendy Perr » Thu, 07 Dec 1995 04:00:00


We have a developer who is working with temporary tables. He create one
using a create table statement with the # sign like this:
CREATE TABLE #TEMP_EMPLOYEE_HRS( COL_1......). After creating the table
he is unable to drop it with a drop table command (sa can't drop it
either). The message we get is that it doesn't exist in the system
catalogues. The weird thing is...it shows up in sysobjects and we can
view it in PowerBuilder in the database painter. If we try to create the
table again, it says that it already exists....but as you know we can't
drop it. I know that Sybase truncates the name down to 13 characters if
the name is too long.....but how can I drop this table without cycling
the server. Does anybody have any ideas???

 
 
 

Problems with temporary tables

Post by Stephen Bake » Fri, 08 Dec 1995 04:00:00




> > We have a developer who is working with temporary tables. He create one
> > using a create table statement with the # sign like this:
> > CREATE TABLE #TEMP_EMPLOYEE_HRS( COL_1......). After creating the table
> > he is unable to drop it with a drop table command (sa can't drop it
> > either).

> I thought that there was a bug like this????  What does TS say?

I have met a bug simular to this.  The temporary table would not be
dropped at the right time.  If I try to drop the table using the "drop"
command I would get a table not found error.

The table contents were deleted by the server, but the entry in
sysobjects was not removed.  I just deleted the entry in sysobjects.
This saved me from cycling the server to clear out tempdb.

http://www.techapp.com/~sbb/Sybase/allow/drop-temptable.html

--
Steve Baker (314)414-0108
Technology Applications, Inc.
http://www.techapp.com/~sbb/

 
 
 

Problems with temporary tables

Post by Mike Lisch » Fri, 15 Dec 1995 04:00:00



>We have a developer who is working with temporary tables. He create one
>using a create table statement with the # sign like this:
>CREATE TABLE #TEMP_EMPLOYEE_HRS( COL_1......). After creating the table
>he is unable to drop it with a drop table command (sa can't drop it
>either). The message we get is that it doesn't exist in the system
>catalogues. The weird thing is...it shows up in sysobjects and we can
>view it in PowerBuilder in the database painter. If we try to create the
>table again, it says that it already exists....but as you know we can't
>drop it. I know that Sybase truncates the name down to 13 characters if
>the name is too long.....but how can I drop this table without cycling
>the server. Does anybody have any ideas???

Have you tried to 'use tempbd' (where the temporary table has been
stored) before you delete it?

I use 4 such tables in a database recreation batch to store
temporarily their entries and it works fine including removing those
tables automatically. Maybe you should frame your statements with an
explicit begin tran ... commit work block.

Hope that helps

Ciao Mike

 
 
 

1. Problem with temporary tables and ALTER TABLE

Hello
    I have a stored procedure which creates a temporary table, as a  result
of some processing additional columns have been added to this temporary
table.  However when I perform a select on the temporary table I get a
problem stating "Insert Error: Column name or number of supplied values does
not match table definition.".

If  I make the table global and execute my SP, then perform the select
within Query Analyser I get the expected results.

Any ideas would be helpful

Thanks

Peter Barber

2. Speak with another port

3. Problem creating Temporary table in Sql Server

4. Anyone Know How Permission on IDS 9.x system calls works?

5. Problem using Temporary Tables within Stored Procedures

6. How do I restore a single table?

7. Another problem with temporary tables

8. Problems with temporary tables in SQL 7

9. Performance Problems with temporary tables in Stored Procedures

10. Problems with temporary tables under System 10

11. Problems with temporary tables in INFORMIX

12. 4GL problem with temporary tables