Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Successfully creating already existing temp tables in proc?

14 posts in General Discussion Last posting was on 2011-08-18 22:52:58.0Z
Simon Scott Posted on 2011-08-01 01:58:39.0Z
Sender: 2f84.4e2e10ac.1804289383@sybase.com
From: Simon Scott
Newsgroups: sybase.public.ase.general
Subject: Successfully creating already existing temp tables in proc?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e36084f.3bea.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 31 Jul 2011 18:58:39 -0700
X-Trace: forums-1-dub 1312163919 10.22.241.41 (31 Jul 2011 18:58:39 -0700)
X-Original-Trace: 31 Jul 2011 18:58:39 -0700, 10.22.241.41
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30428
Article PK: 72607

Hi all

This is weird - I hope Im not just suffering Monday-itis.

I have a proc that creates temp tables, uses them for
processing, and spits out some results.

If I create the temp tables in my session, and then call the
proc, Id expect the create table statements to fail, right?
For some reason, it appears to be successfully creating,
using, and dropping the temp tables - all in the same
session. I can even insert data, check the spid etc..

ie

ADS session 53
* Create #table
* Insert into #table (1)
* Exec sp_proc
* create #table (no error)
* select @@spid (53)
* select from #table (empty)
* select from #table (returns row (1))


Can anyone think of a reason this might be possible?

thanks


Simon Scott Posted on 2011-08-01 02:21:59.0Z
Sender: 3ccc.4e360d32.1804289383@sybase.com
From: Simon Scott
Newsgroups: sybase.public.ase.general
Subject: Re: Successfully creating already existing temp tables in proc?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e360dc7.3ced.1681692777@sybase.com>
References: <4e36084f.3bea.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 31 Jul 2011 19:21:59 -0700
X-Trace: forums-1-dub 1312165319 10.22.241.41 (31 Jul 2011 19:21:59 -0700)
X-Original-Trace: 31 Jul 2011 19:21:59 -0700, 10.22.241.41
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30429
Article PK: 72608


>
> Can anyone think of a reason this might be possible?

Curiouser and curiouser

I selected from sysobjects before the call, and during the
proc:

Before:

id uid name
loginame
---------- ------ ------------------------------------
------------
1289306246 2 #Sales_listing00000530019936003
jdvnwd_admin
1305306303 2 #Sales_list_cnt00000530019936003
jdvnwd_admin
1321306360 2 #Sales_list_total00000530019936003
jdvnwd_admin


During:

id uid name
loginame
---------- ------ ------------------------------------
------------
1253830122 2 #Sales_listing01000530019936003
jdvnwd_admin
1269830179 2 #Sales_list_cnt01000530019936003
jdvnwd_admin
1285830236 2 #Sales_list_total01000530019936003
jdvnwd_admin
1289306246 2 #Sales_listing00000530019936003
jdvnwd_admin
1305306303 2 #Sales_list_cnt00000530019936003
jdvnwd_admin
1321306360 2 #Sales_list_total00000530019936003
jdvnwd_admin


Wow.


Simon Scott Posted on 2011-08-01 05:11:17.0Z
Sender: 3ccc.4e360d32.1804289383@sybase.com
From: Simon Scott
Newsgroups: sybase.public.ase.general
Subject: Re: Successfully creating already existing temp tables in proc?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e363575.4ba0.1681692777@sybase.com>
References: <4e360dc7.3ced.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 31 Jul 2011 22:11:17 -0700
X-Trace: forums-1-dub 1312175477 10.22.241.41 (31 Jul 2011 22:11:17 -0700)
X-Original-Trace: 31 Jul 2011 22:11:17 -0700, 10.22.241.41
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30430
Article PK: 72609

> Curiouser and curiouser


Or not, for a certain definition of "curiouser"

I realise now my mistake with the nesting levels, and you do
in fact get a new set of temp tables at a new nesting level.

This is the first time Ive ever created temp tables with the
view that later creations of the same tables would fail (Im
trying to reuse some procs that create their own temp
tables).

Question remains tho, is there any way to avoid this
behaviour short of changing the procs to test @@nestlevel
before creating the temp tables?

thanks


Rob V [ Sybase ] Posted on 2011-08-01 07:00:35.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Successfully creating already existing temp tables in proc?
References: <4e360dc7.3ced.1681692777@sybase.com> <4e363575.4ba0.1681692777@sybase.com>
In-Reply-To: <4e363575.4ba0.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e364f13@forums-1-dub>
Date: 1 Aug 2011 00:00:35 -0700
X-Trace: forums-1-dub 1312182035 10.22.241.152 (1 Aug 2011 00:00:35 -0700)
X-Original-Trace: 1 Aug 2011 00:00:35 -0700, vip152.sybase.com
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30431
Article PK: 72610


On 01-Aug-2011 07:11, Simon Scott wrote:
>> Curiouser and curiouser
>
> Or not, for a certain definition of "curiouser"
>
> I realise now my mistake with the nesting levels, and you do
> in fact get a new set of temp tables at a new nesting level.
>
> This is the first time Ive ever created temp tables with the
> view that later creations of the same tables would fail (Im
> trying to reuse some procs that create their own temp
> tables).
>
> Question remains tho, is there any way to avoid this
> behaviour short of changing the procs to test @@nestlevel
> before creating the temp tables?
>
> thanks

There is no way to change this; it is how #temp tables work.
You can create your own 'regular' table in tempdb with a unique name of
course, for example by putting the spid and kpid values (from
sysprocesses) into the name, or by using newid() to generate a name.
However, such tables are not automatically dropped at the end of the
procedure, so you need to do the housekeeping yourself.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Simon Scott Posted on 2011-08-01 08:53:39.0Z
Sender: 5fa1.4e366939.1804289383@sybase.com
From: Simon Scott
Newsgroups: sybase.public.ase.general
Subject: Re: Successfully creating already existing temp tables in proc?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e366993.5fd5.1681692777@sybase.com>
References: <4e364f13@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Aug 2011 01:53:39 -0700
X-Trace: forums-1-dub 1312188819 10.22.241.41 (1 Aug 2011 01:53:39 -0700)
X-Original-Trace: 1 Aug 2011 01:53:39 -0700, 10.22.241.41
Lines: 12
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30432
Article PK: 72612


> There is no way to change this; it is how #temp tables
> work. You can create your own 'regular' table in tempdb
> with a unique name of course, for example by putting the
> spid and kpid values (from sysprocesses) into the name,
> or by using newid() to generate a name. However, such
> tables are not automatically dropped at the end of the
> procedure, so you need to do the housekeeping yourself.

What's the best way to check if the temp table already
exists? I tried isnull(object_id('#table'),0) = 0, but it
appears that it returns 0 regardless......


HarryLai Posted on 2011-08-01 13:35:39.0Z
From: "HarryLai" <nospam_harrylhy@bigfoot.com>
Newsgroups: sybase.public.ase.general
References: <4e364f13@forums-1-dub> <4e366993.5fd5.1681692777@sybase.com>
In-Reply-To: <4e366993.5fd5.1681692777@sybase.com>
Subject: Re: Successfully creating already existing temp tables in proc?
Lines: 1
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="big5"; reply-type=original
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 15.4.3538.513
X-MimeOLE: Produced By Microsoft MimeOLE V15.4.3538.513
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e36abab$1@forums-1-dub>
Date: 1 Aug 2011 06:35:39 -0700
X-Trace: forums-1-dub 1312205739 10.22.241.152 (1 Aug 2011 06:35:39 -0700)
X-Original-Trace: 1 Aug 2011 06:35:39 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30433
Article PK: 72611

Try:

if object_id('#table') <> null
begin
do something ...
end


"Simon Scott" 礎b繞l瞼籀簣i繞K瞻繙簧e瞼D礎簧 4e366993.5fd5.1681692777@sybase.com 瞻瞻翹繞翹g...

> There is no way to change this; it is how #temp tables
> work. You can create your own 'regular' table in tempdb
> with a unique name of course, for example by putting the
> spid and kpid values (from sysprocesses) into the name,
> or by using newid() to generate a name. However, such
> tables are not automatically dropped at the end of the
> procedure, so you need to do the housekeeping yourself.

What's the best way to check if the temp table already
exists? I tried isnull(object_id('#table'),0) = 0, but it
appears that it returns 0 regardless......


Simon Scott Posted on 2011-08-02 01:24:37.0Z
Sender: 5fa1.4e366939.1804289383@sybase.com
From: Simon Scott
Newsgroups: sybase.public.ase.general
Subject: Re: Successfully creating already existing temp tables in proc?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e3751d5.4dab.1681692777@sybase.com>
References: <4e36abab$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Aug 2011 18:24:37 -0700
X-Trace: forums-1-dub 1312248277 10.22.241.41 (1 Aug 2011 18:24:37 -0700)
X-Original-Trace: 1 Aug 2011 18:24:37 -0700, 10.22.241.41
Lines: 73
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30436
Article PK: 72615


> Try:
>
> if object_id('#table') <> null
> begin
> do something ...
> end

Yeh, obvious, right?

When I construct a test case, everything works fine.

create proc sp_temptest
as
begin
if object_id('#t') = null
select 'null'
else
select 'not null'
end
go
create table #t (i integer)
go
exec sp_temptest
go

column1
----------
not null


Fine.

However, I have a proc here that cant seem to see the temp
tables.

The first line in it reads (the select is just for testing):
if object_id('#Sales_list_cnt') = null
begin
select 'creating temp tables!'

However, when I run it:


begin
create table #Sales_list_cnt(cnt int)
select object_id('#Sales_list_cnt')
exec sp_myproc 2006,'','Y'
end

column1
----------
1688135898

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data:
0/ms]

column1
----------
(null)

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data:
0/ms]

column1
---------------------
creating temp tables!


ie, the object_id of #Sales_list_cnt becomes NULL
immediately after the exec.


This has got me stuffed.


Simon Scott Posted on 2011-08-02 01:38:31.0Z
Sender: 5fa1.4e366939.1804289383@sybase.com
From: Simon Scott
Newsgroups: sybase.public.ase.general
Subject: Re: Successfully creating already existing temp tables in proc?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e375517.4e8a.1681692777@sybase.com>
References: <4e3751d5.4dab.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Aug 2011 18:38:31 -0700
X-Trace: forums-1-dub 1312249111 10.22.241.41 (1 Aug 2011 18:38:31 -0700)
X-Original-Trace: 1 Aug 2011 18:38:31 -0700, 10.22.241.41
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30437
Article PK: 72616

> This has got me stuffed.

Ahh, it clicked.

When compiling the proc the optimiser must be forcing the if
statement because the tables it thinks it needs are defined
there. That is, it is grabbing the object_id at compile
time.

Changing the table creation lines into an execute immediate
solved the problem.

Not sure this is all that great to be honest, but at least I
found a work-around.


hy Posted on 2011-08-02 03:24:18.0Z
From: "hy" <nospam_harrylhy@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4e3751d5.4dab.1681692777@sybase.com> <4e375517.4e8a.1681692777@sybase.com>
In-Reply-To: <4e375517.4e8a.1681692777@sybase.com>
Subject: Re: Successfully creating already existing temp tables in proc?
Lines: 2
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="big5"; reply-type=original
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8117.416
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8117.416
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e376de2$1@forums-1-dub>
Date: 1 Aug 2011 20:24:18 -0700
X-Trace: forums-1-dub 1312255458 10.22.241.152 (1 Aug 2011 20:24:18 -0700)
X-Original-Trace: 1 Aug 2011 20:24:18 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30438
Article PK: 72617

FYI:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag145.htm


If you temp table name start from '#', it only exist in the current session, and cannot share. So it will be failed if you create
this temp table in your current sp (e.g.: sp1) then execute another sp (sp2) to use it, even this sp2 execute in your sp1 (because
different sp execution will have their own session, they are unique!). The solution like Rob said, create the temp table WITHOUT
'#', and create it on tempdb, the table will exist until you manually delete it with your code, or delete automatically with next
ASE server restart.


Sorry for my english and HTH :)


"Simon Scott" 礎b繞l瞼籀簣i繞K瞻繙簧e瞼D礎簧 4e375517.4e8a.1681692777@sybase.com 瞻瞻翹繞翹g...

>
>> This has got me stuffed.
>
> Ahh, it clicked.
>
> When compiling the proc the optimiser must be forcing the if
> statement because the tables it thinks it needs are defined
> there. That is, it is grabbing the object_id at compile
> time.
>
> Changing the table creation lines into an execute immediate
> solved the problem.
>
> Not sure this is all that great to be honest, but at least I
> found a work-around.


DBADUFF Posted on 2011-08-18 19:19:25.0Z
Sender: 347d.4e4d643e.1804289383@sybase.com
From: DBADUFF
Newsgroups: sybase.public.ase.general
Subject: Re: Successfully creating already existing temp tables in proc?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e4d65bd.3530.1681692777@sybase.com>
References: <4e376de2$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Aug 2011 12:19:25 -0700
X-Trace: forums-1-dub 1313695165 10.22.241.41 (18 Aug 2011 12:19:25 -0700)
X-Original-Trace: 18 Aug 2011 12:19:25 -0700, 10.22.241.41
Lines: 97
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30473
Article PK: 72655

I'm having a similar problem as Simon but the answers here
don't fit with my experience in ASE 15.5. Consider this
piece of code:

set nocount on
DECLARE @Start_Id_Nbr int
DECLARE @End_Id_Nbr int
DECLARE @SameLine bit
DECLARE @Common_Ln int
DECLARE @level int
DECLARE @rowcount int

set @Start_Id_Nbr = 412
set @End_Id_Nbr = 373
set @SameLine = 0

IF @SameLine = 1
BEGIN
print 'same line is 1.'
END
ELSE
BEGIN
IF object_id('#Start_Point_Lines') is not null
DROP TABLE tempdb.dbo.#Start_Point_Lines
IF object_id('#End_Point_Lines') is not null
DROP TABLE tempdb.dbo.#End_Point_Lines

CREATE TABLE #Start_Point_Lines (
Line_Nbr int,
Parent_Line_Nbr int,
depth tinyint)
set @level = 0
INSERT INTO #Start_Point_Lines
SELECT pl_ln_nbr, pl_ln_nbr, @level
FROM acctg_pt
WHERE PT_ID_NBR = @Start_Id_Nbr
set @level = @level+1
set @rowcount = @@Rowcount
END

The first time I run this everything is fine. It creates the
table and populates it. I can even do a select against the
temporary table as a separate execution in the same isql
window. The second time I run it I get this error:

Cannot create temporary table '#Start_Point_Lines'. Prefix
name
'#Start_Point_Lines' is already in use by another temporary
table
'#Start_Point_Lines'.

If I highlight the individual drop table statements and run
them, and then run the whole script again it works. It
appears that the temp tables are not session specific nor
nesting level specific. Also, I have run this in RapidSQL
and iSQL with the same results.

Tim

> FYI:
>
>
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag145.htm
>
>
> If you temp table name start from '#', it only exist in
> the current session, and cannot share. So it will be
> failed if you create this temp table in your current sp
> (e.g.: sp1) then execute another sp (sp2) to use it, even
> this sp2 execute in your sp1 (because different sp
> execution will have their own session, they are unique!).
> The solution like Rob said, create the temp table WITHOUT
> '#', and create it on tempdb, the table will exist until
> you manually delete it with your code, or delete
> automatically with next ASE server restart.
>
>
> Sorry for my english and HTH :)
>
>
> "Simon Scott" ¦b¶l¥ó±i¶K¤º®e¥D¦®
> 4e375517.4e8a.1681692777@sybase.com ¤¤¼¶¼g... >
> >> This has got me stuffed.
> >
> > Ahh, it clicked.
> >
> > When compiling the proc the optimiser must be forcing
> > the if statement because the tables it thinks it needs
> > are defined there. That is, it is grabbing the object_id
> > at compile time.
> >
> > Changing the table creation lines into an execute
> > immediate solved the problem.
> >
> > Not sure this is all that great to be honest, but at
> > least I found a work-around.
>


"Mark A. Parsons" <iron_horse Posted on 2011-08-18 19:58:42.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Successfully creating already existing temp tables in proc?
References: <4e376de2$1@forums-1-dub> <4e4d65bd.3530.1681692777@sybase.com>
In-Reply-To: <4e4d65bd.3530.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e4d6ef2@forums-1-dub>
Date: 18 Aug 2011 12:58:42 -0700
X-Trace: forums-1-dub 1313697522 10.22.241.152 (18 Aug 2011 12:58:42 -0700)
X-Original-Trace: 18 Aug 2011 12:58:42 -0700, vip152.sybase.com
Lines: 109
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30474
Article PK: 72652

You're receiving the error during the *parse-n-compile* phase; this error is generated for 'create proc' and SQL batches
(like your example) when a #temp table already exists.

In Simon's example he obtains multiple #temp tables (same name, different nesting levels) during the *execution* phase;
this is possible with stored procs.

Obviously (?) for Simon's example to work, the #temp table cannot exist at the time the stored proc is *created* (else
he gets the same *parse-n-compile* error about the #temp table already existing), but the #temp table can exist when he
*executes* the proc. This is a ... unique? ... special? ... 'feature' with #temp tables in ASE.

On 08/18/2011 15:19, DBADUFF wrote:
> I'm having a similar problem as Simon but the answers here
> don't fit with my experience in ASE 15.5. Consider this
> piece of code:
>
> set nocount on
> DECLARE @Start_Id_Nbr int
> DECLARE @End_Id_Nbr int
> DECLARE @SameLine bit
> DECLARE @Common_Ln int
> DECLARE @level int
> DECLARE @rowcount int
>
> set @Start_Id_Nbr = 412
> set @End_Id_Nbr = 373
> set @SameLine = 0
>
> IF @SameLine = 1
> BEGIN
> print 'same line is 1.'
> END
> ELSE
> BEGIN
> IF object_id('#Start_Point_Lines') is not null
> DROP TABLE tempdb.dbo.#Start_Point_Lines
> IF object_id('#End_Point_Lines') is not null
> DROP TABLE tempdb.dbo.#End_Point_Lines
>
> CREATE TABLE #Start_Point_Lines (
> Line_Nbr int,
> Parent_Line_Nbr int,
> depth tinyint)
> set @level = 0
> INSERT INTO #Start_Point_Lines
> SELECT pl_ln_nbr, pl_ln_nbr, @level
> FROM acctg_pt
> WHERE PT_ID_NBR = @Start_Id_Nbr
> set @level = @level+1
> set @rowcount = @@Rowcount
> END
>
> The first time I run this everything is fine. It creates the
> table and populates it. I can even do a select against the
> temporary table as a separate execution in the same isql
> window. The second time I run it I get this error:
>
> Cannot create temporary table '#Start_Point_Lines'. Prefix
> name
> '#Start_Point_Lines' is already in use by another temporary
> table
> '#Start_Point_Lines'.
>
> If I highlight the individual drop table statements and run
> them, and then run the whole script again it works. It
> appears that the temp tables are not session specific nor
> nesting level specific. Also, I have run this in RapidSQL
> and iSQL with the same results.
>
> Tim
>
>> FYI:
>>
>>
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag145.htm
>>
>>
>> If you temp table name start from '#', it only exist in
>> the current session, and cannot share. So it will be
>> failed if you create this temp table in your current sp
>> (e.g.: sp1) then execute another sp (sp2) to use it, even
>> this sp2 execute in your sp1 (because different sp
>> execution will have their own session, they are unique!).
>> The solution like Rob said, create the temp table WITHOUT
>> '#', and create it on tempdb, the table will exist until
>> you manually delete it with your code, or delete
>> automatically with next ASE server restart.
>>
>>
>> Sorry for my english and HTH :)
>>
>>
>> "Simon Scott" ¦b¶l¥ó±i¶K¤º®e¥D¦®
>> 4e375517.4e8a.1681692777@sybase.com ¤¤¼¶¼g...>
>>>> This has got me stuffed.
>>>
>>> Ahh, it clicked.
>>>
>>> When compiling the proc the optimiser must be forcing
>>> the if statement because the tables it thinks it needs
>>> are defined there. That is, it is grabbing the object_id
>>> at compile time.
>>>
>>> Changing the table creation lines into an execute
>>> immediate solved the problem.
>>>
>>> Not sure this is all that great to be honest, but at
>>> least I found a work-around.
>>


DBADUFF Posted on 2011-08-18 20:43:19.0Z
Sender: 347d.4e4d643e.1804289383@sybase.com
From: DBADUFF
Newsgroups: sybase.public.ase.general
Subject: Re: Successfully creating already existing temp tables in proc?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e4d7967.3e86.1681692777@sybase.com>
References: <4e4d6ef2@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Aug 2011 13:43:19 -0700
X-Trace: forums-1-dub 1313700199 10.22.241.41 (18 Aug 2011 13:43:19 -0700)
X-Original-Trace: 18 Aug 2011 13:43:19 -0700, 10.22.241.41
Lines: 134
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30475
Article PK: 72654

...This is a ... unique? ... special? ... 'feature'...design
flaw with #temp tables in ASE....

So as I see it, there is no way around this because during
the parse-n-compile phase it doesn't see the table because
its a nested execution level(?). Then it runs the commands
and magically the temp table is visible. Isn't that one of
the explicit purposes of the IF object_id() or IF exists()
commands?

I know I'm being snarky about this and I apologize, it just
seems such an obvious requirement that should have been
tested for.

Tim

> You're receiving the error during the *parse-n-compile*
> phase; this error is generated for 'create proc' and SQL
> batches (like your example) when a #temp table already
> exists.
>
> In Simon's example he obtains multiple #temp tables (same
> name, different nesting levels) during the *execution*
> phase; this is possible with stored procs.
>
> Obviously (?) for Simon's example to work, the #temp table
> cannot exist at the time the stored proc is *created*
> (else he gets the same *parse-n-compile* error about the
> #temp table already existing), but the #temp table can
> exist when he *executes* the proc. This is a ... unique?
> ... special? ... 'feature' with #temp tables in ASE.
>
>
> On 08/18/2011 15:19, DBADUFF wrote:
> > I'm having a similar problem as Simon but the answers
> > here don't fit with my experience in ASE 15.5. Consider
> > this piece of code:
> >
> > set nocount on
> > DECLARE @Start_Id_Nbr int
> > DECLARE @End_Id_Nbr int
> > DECLARE @SameLine bit
> > DECLARE @Common_Ln int
> > DECLARE @level int
> > DECLARE @rowcount int
> >
> > set @Start_Id_Nbr = 412
> > set @End_Id_Nbr = 373
> > set @SameLine = 0
> >
> > IF @SameLine = 1
> > BEGIN
> > print 'same line is 1.'
> > END
> > ELSE
> > BEGIN
> > IF object_id('#Start_Point_Lines') is not null
> > DROP TABLE tempdb.dbo.#Start_Point_Lines
> > IF object_id('#End_Point_Lines') is not null
> > DROP TABLE tempdb.dbo.#End_Point_Lines
> >
> > CREATE TABLE #Start_Point_Lines (
> > Line_Nbr int,
> > Parent_Line_Nbr int,
> > depth tinyint)
> > set @level = 0
> > INSERT INTO #Start_Point_Lines
> > SELECT pl_ln_nbr, pl_ln_nbr, @level
> > FROM acctg_pt
> > WHERE PT_ID_NBR = @Start_Id_Nbr
> > set @level = @level+1
> > set @rowcount = @@Rowcount
> > END
> >
> > The first time I run this everything is fine. It creates
> > the table and populates it. I can even do a select
> > against the temporary table as a separate execution in
> > the same isql window. The second time I run it I get
> this error: >
> > Cannot create temporary table '#Start_Point_Lines'.
> > Prefix name
> > '#Start_Point_Lines' is already in use by another
> > temporary table
> > '#Start_Point_Lines'.
> >
> > If I highlight the individual drop table statements and
> > run them, and then run the whole script again it works.
> > It appears that the temp tables are not session specific
> > nor nesting level specific. Also, I have run this in
> > RapidSQL and iSQL with the same results.
> >
> > Tim
> >
> >> FYI:
> >>
> >>
> >
>
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag145.htm
> >>
> >>
> >> If you temp table name start from '#', it only exist in
> >> the current session, and cannot share. So it will be
> >> failed if you create this temp table in your current
> sp >> (e.g.: sp1) then execute another sp (sp2) to use it,
> even >> this sp2 execute in your sp1 (because different
> sp >> execution will have their own session, they are
> unique!). >> The solution like Rob said, create the temp
> table WITHOUT >> '#', and create it on tempdb, the table
> will exist until >> you manually delete it with your code,
> or delete >> automatically with next ASE server restart.
> >>
> >>
> >> Sorry for my english and HTH :)
> >>
> >>
> >> "Simon Scott"
¦b¶l¥ó±i¶K¤º®e¥D¦®
> >> 4e375517.4e8a.1681692777@sybase.com
¤¤¼¶¼g...>
> >>>> This has got me stuffed.
> >>>
> >>> Ahh, it clicked.
> >>>
> >>> When compiling the proc the optimiser must be forcing
> >>> the if statement because the tables it thinks it needs
> >>> are defined there. That is, it is grabbing the
> object_id >>> at compile time.
> >>>
> >>> Changing the table creation lines into an execute
> >>> immediate solved the problem.
> >>>
> >>> Not sure this is all that great to be honest, but at
> >>> least I found a work-around.
> >>


"Mark A. Parsons" <iron_horse Posted on 2011-08-18 21:33:12.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Successfully creating already existing temp tables in proc?
References: <4e4d6ef2@forums-1-dub> <4e4d7967.3e86.1681692777@sybase.com>
In-Reply-To: <4e4d7967.3e86.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e4d8518$1@forums-1-dub>
Date: 18 Aug 2011 14:33:12 -0700
X-Trace: forums-1-dub 1313703192 10.22.241.152 (18 Aug 2011 14:33:12 -0700)
X-Original-Trace: 18 Aug 2011 14:33:12 -0700, vip152.sybase.com
Lines: 158
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30476
Article PK: 72653

During the parse-n-compile phase the dataserver does not execute/evaluate anything (eg, it does not evaluate your
conditional test); it just sees a 'create table' command and generates an error if the table already exists.

The normal way of addressing this issue is to place the if/drop code in its own batch, eg:

=========================================
if object_id('<table_name>') is not null
drop <table_name>
go
set nocount on
DECLARE ...
...
ELSE
BEGIN
CREATE TABLE <table_name> ...
...
END
go
=========================================

And yes, you can place all of the if/drop pairs into the same batch if you wish.

On 08/18/2011 16:43, DBADUFF wrote:
> ...This is a ... unique? ... special? ... 'feature'...design
> flaw with #temp tables in ASE....
>
> So as I see it, there is no way around this because during
> the parse-n-compile phase it doesn't see the table because
> its a nested execution level(?). Then it runs the commands
> and magically the temp table is visible. Isn't that one of
> the explicit purposes of the IF object_id() or IF exists()
> commands?
>
> I know I'm being snarky about this and I apologize, it just
> seems such an obvious requirement that should have been
> tested for.
>
> Tim
>> You're receiving the error during the *parse-n-compile*
>> phase; this error is generated for 'create proc' and SQL
>> batches (like your example) when a #temp table already
>> exists.
>>
>> In Simon's example he obtains multiple #temp tables (same
>> name, different nesting levels) during the *execution*
>> phase; this is possible with stored procs.
>>
>> Obviously (?) for Simon's example to work, the #temp table
>> cannot exist at the time the stored proc is *created*
>> (else he gets the same *parse-n-compile* error about the
>> #temp table already existing), but the #temp table can
>> exist when he *executes* the proc. This is a ... unique?
>> ... special? ... 'feature' with #temp tables in ASE.
>>
>>
>> On 08/18/2011 15:19, DBADUFF wrote:
>>> I'm having a similar problem as Simon but the answers
>>> here don't fit with my experience in ASE 15.5. Consider
>>> this piece of code:
>>>
>>> set nocount on
>>> DECLARE @Start_Id_Nbr int
>>> DECLARE @End_Id_Nbr int
>>> DECLARE @SameLine bit
>>> DECLARE @Common_Ln int
>>> DECLARE @level int
>>> DECLARE @rowcount int
>>>
>>> set @Start_Id_Nbr = 412
>>> set @End_Id_Nbr = 373
>>> set @SameLine = 0
>>>
>>> IF @SameLine = 1
>>> BEGIN
>>> print 'same line is 1.'
>>> END
>>> ELSE
>>> BEGIN
>>> IF object_id('#Start_Point_Lines') is not null
>>> DROP TABLE tempdb.dbo.#Start_Point_Lines
>>> IF object_id('#End_Point_Lines') is not null
>>> DROP TABLE tempdb.dbo.#End_Point_Lines
>>>
>>> CREATE TABLE #Start_Point_Lines (
>>> Line_Nbr int,
>>> Parent_Line_Nbr int,
>>> depth tinyint)
>>> set @level = 0
>>> INSERT INTO #Start_Point_Lines
>>> SELECT pl_ln_nbr, pl_ln_nbr, @level
>>> FROM acctg_pt
>>> WHERE PT_ID_NBR = @Start_Id_Nbr
>>> set @level = @level+1
>>> set @rowcount = @@Rowcount
>>> END
>>>
>>> The first time I run this everything is fine. It creates
>>> the table and populates it. I can even do a select
>>> against the temporary table as a separate execution in
>>> the same isql window. The second time I run it I get
>> this error:>
>>> Cannot create temporary table '#Start_Point_Lines'.
>>> Prefix name
>>> '#Start_Point_Lines' is already in use by another
>>> temporary table
>>> '#Start_Point_Lines'.
>>>
>>> If I highlight the individual drop table statements and
>>> run them, and then run the whole script again it works.
>>> It appears that the temp tables are not session specific
>>> nor nesting level specific. Also, I have run this in
>>> RapidSQL and iSQL with the same results.
>>>
>>> Tim
>>>
>>>> FYI:
>>>>
>>>>
>>>
>>
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag145.htm
>>>>
>>>>
>>>> If you temp table name start from '#', it only exist in
>>>> the current session, and cannot share. So it will be
>>>> failed if you create this temp table in your current
>> sp>> (e.g.: sp1) then execute another sp (sp2) to use it,
>> even>> this sp2 execute in your sp1 (because different
>> sp>> execution will have their own session, they are
>> unique!).>> The solution like Rob said, create the temp
>> table WITHOUT>> '#', and create it on tempdb, the table
>> will exist until>> you manually delete it with your code,
>> or delete>> automatically with next ASE server restart.
>>>>
>>>>
>>>> Sorry for my english and HTH :)
>>>>
>>>>
>>>> "Simon Scott"
> ¦b¶l¥ó±i¶K¤º®e¥D¦®
>>>> 4e375517.4e8a.1681692777@sybase.com
> ¤¤¼¶¼g...>
>>>>>> This has got me stuffed.
>>>>>
>>>>> Ahh, it clicked.
>>>>>
>>>>> When compiling the proc the optimiser must be forcing
>>>>> the if statement because the tables it thinks it needs
>>>>> are defined there. That is, it is grabbing the
>> object_id>>> at compile time.
>>>>>
>>>>> Changing the table creation lines into an execute
>>>>> immediate solved the problem.
>>>>>
>>>>> Not sure this is all that great to be honest, but at
>>>>> least I found a work-around.
>>>>


"Mark A. Parsons" <iron_horse Posted on 2011-08-18 22:52:58.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Successfully creating already existing temp tables in proc?
References: <4e4d6ef2@forums-1-dub> <4e4d7967.3e86.1681692777@sybase.com>
In-Reply-To: <4e4d7967.3e86.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e4d97ca$1@forums-1-dub>
Date: 18 Aug 2011 15:52:58 -0700
X-Trace: forums-1-dub 1313707978 10.22.241.152 (18 Aug 2011 15:52:58 -0700)
X-Original-Trace: 18 Aug 2011 15:52:58 -0700, vip152.sybase.com
Lines: 49
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30478
Article PK: 72656


On 08/18/2011 16:43, DBADUFF wrote:
> ...This is a ... unique? ... special? ... 'feature'...design
> flaw with #temp tables in ASE....

What are you calling a design flaw? The fact that your sample code doesn't allow you to drop and recreate a table in
the same batch?

The behavior you're seeing is the same for temp tables and permanent tables; while you'll get a different error
#/message the results are the same, the parse-n-compile phase will fail if the temp/permanent table already exists.

I provided one workaround for your predicament under a separate posting (ie, split the if/drop logic into a separate
batch).

Another workaround for your predicament is to put the if/drop logic *after* the create logic (yes, this works in the
same batch). Granted, this workaround may not be acceptable in some scenarios (eg, you wish the table to exist for a
follow-on batch).

And no, using an exec() construct to create a #temp table will not work. Well, it *will* work, but because the exec()
is executed at a subordinate nesting level the #temp table will be destroyed when the exec() construct returns control
to the current batch/proc (just like the #temp table created inside a proc will disappear when you exit the proc).

----------

When I said "... unique? ... special? ... 'feature'" I was referring to the fact that at execution time it's possible to
generate a new version of the same #temp table at different nesting levels.

Obviously (?) you can't create multiple copies of a permanent table at different nesting levels hence the reason this
capability is unique/special to #temp tables. {"Duh, Mark!" ?)

---------

Now, is this feature (multiple #temp tables with same name) a design flaw? Not really; it does have it's limited uses.

While most folks don't even know this feature exists, my guess is that most of the folks who do know about this feature
rarely use it.

The general case where you would want this capability is where you've got a proc that uses a #temp table for local (to
the proc) processing, but where you're not sure what parent processes may be calling this proc let alone whether or not
the parent process has a #temp table of the same name.

This feature allows the proc to create/populate/process/drop it's own version of the #temp table without worrying about
the existence of a similarly named #temp table existing at a different nesting level.

A good example where this feature would come in handy is with a recursive proc (ie, it calls itself) that needs a #temp
table for local (to the proc) processing purposes. Each recursive call to the proc will get its own version of the
#temp table to play with.