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.

Temporal Tables # and ##

6 posts in General Discussion Last posting was on 2010-08-26 17:23:58.0Z
RGS Posted on 2010-08-25 20:20:13.0Z
Sender: 7369.4c757a41.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Temporal Tables # and ##
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c757afd.7393.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Aug 2010 13:20:13 -0700
X-Trace: forums-1-dub 1282767613 10.22.241.41 (25 Aug 2010 13:20:13 -0700)
X-Original-Trace: 25 Aug 2010 13:20:13 -0700, 10.22.241.41
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29498
Article PK: 78726

What is the difference in ASE between temporal tables
created in the way:

create table #abc ........

And

create table ##abc .......

Thanks!


ET Posted on 2010-08-25 20:54:20.0Z
From: ET <dbccjob@gmail.Com>
Reply-To: ET
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.9.2.8) Gecko/20100802 Thunderbird/3.1.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Temporal Tables # and ##
References: <4c757afd.7393.1681692777@sybase.com>
In-Reply-To: <4c757afd.7393.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: <4c7582fc$1@forums-1-dub>
Date: 25 Aug 2010 13:54:20 -0700
X-Trace: forums-1-dub 1282769660 10.22.241.152 (25 Aug 2010 13:54:20 -0700)
X-Original-Trace: 25 Aug 2010 13:54:20 -0700, vip152.sybase.com
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29500
Article PK: 78734

Hi

I am not aware of ##table syntax in ASE like with MS SQL server. This ##
notation is equivalent to shared temporary tables in ASE.

You have two types of temp tables :

create tempdb..table : creates a shared temporary table, that can be
accessed by any session running in ASE. It stays available even after
the user that created the table logs out. Restart ASE will get rid of
the table as tempdb is rebuilt.

create #table : creates a temp table that is only visible inside the
session or the procedure were the temp table is created. The table is
automatically dropped at end of the session/procedure

Emmanuel

Le 25/08/2010 22:20, RGS a écrit :

> What is the difference in ASE between temporal tables
> created in the way:
>
> create table #abc ........
>
> And
>
> create table ##abc .......
>
> Thanks!


"Mark A. Parsons" <iron_horse Posted on 2010-08-25 23:55:43.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Temporal Tables # and ##
References: <4c7582fc$1@forums-1-dub> <4c75a2fa.77f3.1681692777@sybase.com>
In-Reply-To: <4c75a2fa.77f3.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: <4c75ad7f$1@forums-1-dub>
Date: 25 Aug 2010 16:55:43 -0700
X-Trace: forums-1-dub 1282780543 10.22.241.152 (25 Aug 2010 16:55:43 -0700)
X-Original-Trace: 25 Aug 2010 16:55:43 -0700, vip152.sybase.com
Lines: 93
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29504
Article PK: 78735

Temporary tables (ie, name begins with '#') are only accessible by the login session (aka spid) that created it. They
cannot be accessed by other login sessions.

While you can create a table that begins with '##', it is treated like any other temporary table that begins with a
single '#', ie, the second '#' has no significant meaning and is treated like any other valid symbol for creating an
object name.

================== spid #1
-- create/populate a couple temp tables:

1> create table #t1 (a int)
2> go
1> insert #t1 select id from sysobjects
2> go

(31 rows affected)

1> create table ##t1 (a int)
2> go
1> insert ##t1 select id from sysobjects
2> go

(32 rows affected)

================== spid #2
-- trying to access spid #1's temp tables:

1> select * from #t1
2> go

Msg 208, Level 16, State 1:
Server 'CC1', Line 1:
#t1 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of
output).

1> select * from ##t1
2> go

Msg 208, Level 16, State 1:
Server 'CC1', Line 1:
##t1 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of
output).

=================

RGS wrote:
> I am so sorry but I didn't uderstand your answer
>
> What do you mean with:
>
> "This ## notation is equivalent to shared temporary tables
> in ASE"
>
> Is it possible to share temorary tables between different
> sessions in ASE?
>
> Thanks and sorry again..
>
>
>
>> Hi
>>
>> I am not aware of ##table syntax in ASE like with MS SQL
>> server. This ## notation is equivalent to shared
>> temporary tables in ASE.
>>
>> You have two types of temp tables :
>>
>> create tempdb..table : creates a shared temporary table,
>> that can be accessed by any session running in ASE. It
>> stays available even after the user that created the
>> table logs out. Restart ASE will get rid of the table as
>> tempdb is rebuilt.
>>
>> create #table : creates a temp table that is only visible
>> inside the session or the procedure were the temp table
>> is created. The table is automatically dropped at end of
>> the session/procedure
>>
>> Emmanuel
>>
>> Le 25/08/2010 22:20, RGS a écrit :
>>> What is the difference in ASE between temporal tables
>>> created in the way:
>>>
>>> create table #abc ........
>>>
>>> And
>>>
>>> create table ##abc .......
>>>
>>> Thanks!


RGS Posted on 2010-08-26 17:23:58.0Z
Sender: 7369.4c757a41.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: Temporal Tables # and ##
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c76a32e.1598.1681692777@sybase.com>
References: <4c75ad7f$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: 26 Aug 2010 10:23:58 -0700
X-Trace: forums-1-dub 1282843438 10.22.241.41 (26 Aug 2010 10:23:58 -0700)
X-Original-Trace: 26 Aug 2010 10:23:58 -0700, 10.22.241.41
Lines: 103
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29511
Article PK: 78742

Thank you very much

> Temporary tables (ie, name begins with '#') are only
> accessible by the login session (aka spid) that created
> it. They cannot be accessed by other login sessions.
>
> While you can create a table that begins with '##', it is
> treated like any other temporary table that begins with a
> single '#', ie, the second '#' has no significant meaning
> and is treated like any other valid symbol for creating an
> object name.
>
> ==================
spid #1
> -- create/populate a couple temp tables:
>
> 1> create table #t1 (a int)
> 2> go
> 1> insert #t1 select id from sysobjects
> 2> go
>
> (31 rows affected)
>
> 1> create table ##t1 (a int)
> 2> go
> 1> insert ##t1 select id from sysobjects
> 2> go
>
> (32 rows affected)
>
> ==================
spid #2
> -- trying to access spid #1's temp tables:
>
> 1> select * from #t1
> 2> go
>
> Msg 208, Level 16, State 1:
> Server 'CC1', Line 1:
> #t1 not found. Specify owner.objectname or use sp_help to
> check whether the object exists (sp_help may produce lots
> of output).
>
> 1> select * from ##t1
> 2> go
>
> Msg 208, Level 16, State 1:
> Server 'CC1', Line 1:
> ##t1 not found. Specify owner.objectname or use sp_help to
> check whether the object exists (sp_help may produce lots
> of output).
>
> =================
>
>
> RGS wrote:
> > I am so sorry but I didn't uderstand your answer
> >
> > What do you mean with:
> >
> > "This ## notation is equivalent to shared temporary
> > tables in ASE"
> >
> > Is it possible to share temorary tables between
> > different sessions in ASE?
> >
> > Thanks and sorry again..
> >
> >
> >
> >> Hi
> >>
> >> I am not aware of ##table syntax in ASE like with MS
> SQL >> server. This ## notation is equivalent to shared
> >> temporary tables in ASE.
> >>
> >> You have two types of temp tables :
> >>
> >> create tempdb..table : creates a shared temporary table
> , >> that can be accessed by any session running in ASE.
> It >> stays available even after the user that created
> the >> table logs out. Restart ASE will get rid of the
> table as >> tempdb is rebuilt.
> >>
> >> create #table : creates a temp table that is only
> visible >> inside the session or the procedure were the
> temp table >> is created. The table is automatically
> dropped at end of >> the session/procedure
> >>
> >> Emmanuel
> >>
> >> Le 25/08/2010 22:20, RGS a écrit :
> >>> What is the difference in ASE between temporal tables
> >>> created in the way:
> >>>
> >>> create table #abc ........
> >>>
> >>> And
> >>>
> >>> create table ##abc .......
> >>>
> >>> Thanks!