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.

RE: spid assignment on dynamic sql

11 posts in Cluster Last posting was on 2008-03-10 15:14:13.0Z
Kevin Leahy Posted on 2008-03-06 16:04:58.0Z
Sender: 27a.47d01579.1804289383@sybase.com
From: Kevin Leahy
Newsgroups: sybase.public.ase.cluster
Subject: RE: spid assignment on dynamic sql
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <47d0162a.284.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 6 Mar 2008 08:04:58 -0800
X-Trace: forums-1-dub 1204819498 10.22.241.41 (6 Mar 2008 08:04:58 -0800)
X-Original-Trace: 6 Mar 2008 08:04:58 -0800, 10.22.241.41
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:21
Article PK: 48307

Basically what we need to know is this:

Here are two proc definitions. The first takes the same of
another proc and runs it.
The EXEC statement at the end just asks proc 1 to run proc
2.

CREATE PROCEDURE dbo.test_proc_1 ( @sp VARCHAR(20) ) AS
BEGIN
SELECT 'In Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
EXEC @sp
SELECT 'Out Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
END
go

CREATE PROCEDURE dbo.test_proc_2 AS BEGIN
SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
END
go

EXEC test_proc_1 'test_proc_2'

If you run this SQL you will see that the same spid is
returned everywhere.

But we need to be sure this will always be the case, even
when the database is "creaking" with large queries and
multiple users. A colleage has noticed some inconsistencies
that he thinks imply that Sybase may drop the spid and allow
it to be reassigned to another process before the dynamic
sql is complete. The question I think is really: does Sybase
guarantee that the spid given to a calling proc will be held
until that proc completes and that dynamic sql called from
the calling proc will inherit the spid?

Thanks

Kevin


Bret Halford Posted on 2008-03-06 16:34:24.0Z
Message-ID: <47D01D10.59545EF7@sybase.com>
From: Bret Halford <bret@sybase.com>
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.cluster
Subject: Re: spid assignment on dynamic sql
References: <47d0162a.284.1681692777@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 6 Mar 2008 08:34:24 -0800
X-Trace: forums-1-dub 1204821264 10.22.241.152 (6 Mar 2008 08:34:24 -0800)
X-Original-Trace: 6 Mar 2008 08:34:24 -0800, vip152.sybase.com
Lines: 61
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:22
Article PK: 48308

The spid is going to stay the same for the entire session. The client
would
have to exit and reconnect for @@spid to change. If you were to see
different @@spid values between a calling and child proc execution,
please
report it as a bug.

A possibly related oddity that might be what is being noticed is that
the
sysprocesses table will sometimes show a different uid/login name as
procedures are executing. The is because procs execute as if they were
being run by the login that created them, and this sometimes shows
up in sysprocesses (typically most procs are created by "sa", so you
would
see a user session "becoming" sa at times).

-bret

Kevin, Leahy wrote:

> Basically what we need to know is this:
>
> Here are two proc definitions. The first takes the same of
> another proc and runs it.
> The EXEC statement at the end just asks proc 1 to run proc
> 2.
>
> CREATE PROCEDURE dbo.test_proc_1 ( @sp VARCHAR(20) ) AS
> BEGIN
> SELECT 'In Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
> EXEC @sp
> SELECT 'Out Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
> END
> go
>
> CREATE PROCEDURE dbo.test_proc_2 AS BEGIN
> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
> END
> go
>
> EXEC test_proc_1 'test_proc_2'
>
> If you run this SQL you will see that the same spid is
> returned everywhere.
>
> But we need to be sure this will always be the case, even
> when the database is "creaking" with large queries and
> multiple users. A colleage has noticed some inconsistencies
> that he thinks imply that Sybase may drop the spid and allow
> it to be reassigned to another process before the dynamic
> sql is complete. The question I think is really: does Sybase
> guarantee that the spid given to a calling proc will be held
> until that proc completes and that dynamic sql called from
> the calling proc will inherit the spid?
>
> Thanks
>
> Kevin


Bret Halford Posted on 2008-03-06 16:35:54.0Z
Message-ID: <47D01D6A.8AAE9867@sybase.com>
From: Bret Halford <bret@sybase.com>
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.cluster
Subject: Re: spid assignment on dynamic sql
References: <47d0162a.284.1681692777@sybase.com> <47D01D10.59545EF7@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 6 Mar 2008 08:35:54 -0800
X-Trace: forums-1-dub 1204821354 10.22.241.152 (6 Mar 2008 08:35:54 -0800)
X-Original-Trace: 6 Mar 2008 08:35:54 -0800, vip152.sybase.com
Lines: 66
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:23
Article PK: 48309

Actually, hold off. I hadn't noticed I'd wandered into the new
"cluster" newsgroup, and I could be very wrong about it's behavior.
My answer would apply to regular non-clustered ASE, though.

Bret Halford wrote:

> The spid is going to stay the same for the entire session. The client
> would
> have to exit and reconnect for @@spid to change. If you were to see
> different @@spid values between a calling and child proc execution,
> please
> report it as a bug.
>
> A possibly related oddity that might be what is being noticed is that
> the
> sysprocesses table will sometimes show a different uid/login name as
> procedures are executing. The is because procs execute as if they were
> being run by the login that created them, and this sometimes shows
> up in sysprocesses (typically most procs are created by "sa", so you
> would
> see a user session "becoming" sa at times).
>
> -bret
>
> Kevin, Leahy wrote:
>
> > Basically what we need to know is this:
> >
> > Here are two proc definitions. The first takes the same of
> > another proc and runs it.
> > The EXEC statement at the end just asks proc 1 to run proc
> > 2.
> >
> > CREATE PROCEDURE dbo.test_proc_1 ( @sp VARCHAR(20) ) AS
> > BEGIN
> > SELECT 'In Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
> > EXEC @sp
> > SELECT 'Out Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
> > END
> > go
> >
> > CREATE PROCEDURE dbo.test_proc_2 AS BEGIN
> > SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
> > SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
> > END
> > go
> >
> > EXEC test_proc_1 'test_proc_2'
> >
> > If you run this SQL you will see that the same spid is
> > returned everywhere.
> >
> > But we need to be sure this will always be the case, even
> > when the database is "creaking" with large queries and
> > multiple users. A colleage has noticed some inconsistencies
> > that he thinks imply that Sybase may drop the spid and allow
> > it to be reassigned to another process before the dynamic
> > sql is complete. The question I think is really: does Sybase
> > guarantee that the spid given to a calling proc will be held
> > until that proc completes and that dynamic sql called from
> > the calling proc will inherit the spid?
> >
> > Thanks
> >
> > Kevin


David Wein Posted on 2008-03-06 17:03:05.0Z
From: David Wein <david.wein@sybase.com>
User-Agent: Thunderbird 1.5.0.12 (X11/20071020)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.cluster
Subject: Re: spid assignment on dynamic sql
References: <47d0162a.284.1681692777@sybase.com> <47D01D10.59545EF7@sybase.com> <47D01D6A.8AAE9867@sybase.com>
In-Reply-To: <47D01D6A.8AAE9867@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: <47d023c9$1@forums-1-dub>
Date: 6 Mar 2008 09:03:05 -0800
X-Trace: forums-1-dub 1204822985 10.22.241.152 (6 Mar 2008 09:03:05 -0800)
X-Original-Trace: 6 Mar 2008 09:03:05 -0800, vip152.sybase.com
Lines: 94
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:24
Article PK: 48310

Right, the spid can be a little more elusive in Cluster Edition.

One of the key capabilities is connection migration, whereby an existing
connection is moved from one instance of the cluster to another. This
happens without the application having to code for it, or even knowing
about it. The current design results in a new spid upon migration.

So if you are in an isql session and you run:

1> select @@spid
2> go
1> select @@spid
2> go

there is no guarantee the two selects will return the same value.

However it is important to note that you will not be migrated during the
execution of a stored procedure. So within a single sproc execution,
which includes the execution of nested sprocs, the spid won't change.

We explored having the spid move with the connection when it is
migrated, but we felt the cost- benefit didn't work in favor of doing
so. I'd be interested to know how much of an issue it is to have the
spid change between commands.

-Dave

Bret Halford wrote:
> Actually, hold off. I hadn't noticed I'd wandered into the new
> "cluster" newsgroup, and I could be very wrong about it's behavior.
> My answer would apply to regular non-clustered ASE, though.
>
> Bret Halford wrote:
>
>> The spid is going to stay the same for the entire session. The client
>> would
>> have to exit and reconnect for @@spid to change. If you were to see
>> different @@spid values between a calling and child proc execution,
>> please
>> report it as a bug.
>>
>> A possibly related oddity that might be what is being noticed is that
>> the
>> sysprocesses table will sometimes show a different uid/login name as
>> procedures are executing. The is because procs execute as if they were
>> being run by the login that created them, and this sometimes shows
>> up in sysprocesses (typically most procs are created by "sa", so you
>> would
>> see a user session "becoming" sa at times).
>>
>> -bret
>>
>> Kevin, Leahy wrote:
>>
>>> Basically what we need to know is this:
>>>
>>> Here are two proc definitions. The first takes the same of
>>> another proc and runs it.
>>> The EXEC statement at the end just asks proc 1 to run proc
>>> 2.
>>>
>>> CREATE PROCEDURE dbo.test_proc_1 ( @sp VARCHAR(20) ) AS
>>> BEGIN
>>> SELECT 'In Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>>> EXEC @sp
>>> SELECT 'Out Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>>> END
>>> go
>>>
>>> CREATE PROCEDURE dbo.test_proc_2 AS BEGIN
>>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>>> END
>>> go
>>>
>>> EXEC test_proc_1 'test_proc_2'
>>>
>>> If you run this SQL you will see that the same spid is
>>> returned everywhere.
>>>
>>> But we need to be sure this will always be the case, even
>>> when the database is "creaking" with large queries and
>>> multiple users. A colleage has noticed some inconsistencies
>>> that he thinks imply that Sybase may drop the spid and allow
>>> it to be reassigned to another process before the dynamic
>>> sql is complete. The question I think is really: does Sybase
>>> guarantee that the spid given to a calling proc will be held
>>> until that proc completes and that dynamic sql called from
>>> the calling proc will inherit the spid?
>>>
>>> Thanks
>>>
>>> Kevin
>


Sherlock, Kevin Posted on 2008-03-06 17:14:07.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.cluster
References: <47d0162a.284.1681692777@sybase.com> <47D01D10.59545EF7@sybase.com> <47D01D6A.8AAE9867@sybase.com> <47d023c9$1@forums-1-dub>
Subject: Re: spid assignment on dynamic sql
Lines: 102
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47d0265f$1@forums-1-dub>
Date: 6 Mar 2008 09:14:07 -0800
X-Trace: forums-1-dub 1204823647 10.22.241.152 (6 Mar 2008 09:14:07 -0800)
X-Original-Trace: 6 Mar 2008 09:14:07 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:28
Article PK: 48314

Which conjures up a question in my mind. Do all of the appcontext contexts
migrate with the connection? If so, does SYS_CONTEXT stay in tact, or is
it re-populated as you describe below?

"David Wein" <david.wein@sybase.com> wrote in message
news:47d023c9$1@forums-1-dub...
> Right, the spid can be a little more elusive in Cluster Edition.
>
> One of the key capabilities is connection migration, whereby an existing
> connection is moved from one instance of the cluster to another. This
> happens without the application having to code for it, or even knowing
> about it. The current design results in a new spid upon migration.
>
> So if you are in an isql session and you run:
>
> 1> select @@spid
> 2> go
> 1> select @@spid
> 2> go
>
> there is no guarantee the two selects will return the same value.
>
> However it is important to note that you will not be migrated during the
> execution of a stored procedure. So within a single sproc execution,
> which includes the execution of nested sprocs, the spid won't change.
>
> We explored having the spid move with the connection when it is migrated,
> but we felt the cost- benefit didn't work in favor of doing so. I'd be
> interested to know how much of an issue it is to have the spid change
> between commands.
>
> -Dave
>
> Bret Halford wrote:
>> Actually, hold off. I hadn't noticed I'd wandered into the new
>> "cluster" newsgroup, and I could be very wrong about it's behavior.
>> My answer would apply to regular non-clustered ASE, though.
>>
>> Bret Halford wrote:
>>
>>> The spid is going to stay the same for the entire session. The client
>>> would
>>> have to exit and reconnect for @@spid to change. If you were to see
>>> different @@spid values between a calling and child proc execution,
>>> please
>>> report it as a bug.
>>>
>>> A possibly related oddity that might be what is being noticed is that
>>> the
>>> sysprocesses table will sometimes show a different uid/login name as
>>> procedures are executing. The is because procs execute as if they were
>>> being run by the login that created them, and this sometimes shows
>>> up in sysprocesses (typically most procs are created by "sa", so you
>>> would
>>> see a user session "becoming" sa at times).
>>>
>>> -bret
>>>
>>> Kevin, Leahy wrote:
>>>
>>>> Basically what we need to know is this:
>>>>
>>>> Here are two proc definitions. The first takes the same of
>>>> another proc and runs it.
>>>> The EXEC statement at the end just asks proc 1 to run proc
>>>> 2.
>>>>
>>>> CREATE PROCEDURE dbo.test_proc_1 ( @sp VARCHAR(20) ) AS
>>>> BEGIN
>>>> SELECT 'In Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>>>> EXEC @sp
>>>> SELECT 'Out Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>>>> END
>>>> go
>>>>
>>>> CREATE PROCEDURE dbo.test_proc_2 AS BEGIN
>>>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>>>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>>>> END
>>>> go
>>>>
>>>> EXEC test_proc_1 'test_proc_2'
>>>>
>>>> If you run this SQL you will see that the same spid is
>>>> returned everywhere.
>>>>
>>>> But we need to be sure this will always be the case, even
>>>> when the database is "creaking" with large queries and
>>>> multiple users. A colleage has noticed some inconsistencies
>>>> that he thinks imply that Sybase may drop the spid and allow
>>>> it to be reassigned to another process before the dynamic
>>>> sql is complete. The question I think is really: does Sybase
>>>> guarantee that the spid given to a calling proc will be held
>>>> until that proc completes and that dynamic sql called from
>>>> the calling proc will inherit the spid?
>>>>
>>>> Thanks
>>>>
>>>> Kevin
>>


David Wein Posted on 2008-03-06 17:39:31.0Z
From: David Wein <david.wein@sybase.com>
User-Agent: Thunderbird 1.5.0.12 (X11/20071020)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.cluster
Subject: Re: spid assignment on dynamic sql
References: <47d0162a.284.1681692777@sybase.com> <47D01D10.59545EF7@sybase.com> <47D01D6A.8AAE9867@sybase.com> <47d023c9$1@forums-1-dub> <47d0265f$1@forums-1-dub>
In-Reply-To: <47d0265f$1@forums-1-dub>
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: <47d02c53$1@forums-1-dub>
Date: 6 Mar 2008 09:39:31 -0800
X-Trace: forums-1-dub 1204825171 10.22.241.152 (6 Mar 2008 09:39:31 -0800)
X-Original-Trace: 6 Mar 2008 09:39:31 -0800, vip152.sybase.com
Lines: 116
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:29
Article PK: 48315

SYS_CONTEXT is an Oracle thing, correct?

Your application context should stay in tact. This includes current
database, set options, etc. The spid is a bit of a challenge because
that number is actually an index into an ASE memory pool. If we want to
move the spid then we need to decouple it from the memory pool index,
which can be done but is a pain.

There are some restrictions about what connections can and can't be
migrated, and certain elements of the context may prevent the connection
from being migrated. The user guide discusses this.

-Dave

Sherlock, Kevin wrote:
> Which conjures up a question in my mind. Do all of the appcontext contexts
> migrate with the connection? If so, does SYS_CONTEXT stay in tact, or is
> it re-populated as you describe below?
>
> "David Wein" <david.wein@sybase.com> wrote in message
> news:47d023c9$1@forums-1-dub...
>> Right, the spid can be a little more elusive in Cluster Edition.
>>
>> One of the key capabilities is connection migration, whereby an existing
>> connection is moved from one instance of the cluster to another. This
>> happens without the application having to code for it, or even knowing
>> about it. The current design results in a new spid upon migration.
>>
>> So if you are in an isql session and you run:
>>
>> 1> select @@spid
>> 2> go
>> 1> select @@spid
>> 2> go
>>
>> there is no guarantee the two selects will return the same value.
>>
>> However it is important to note that you will not be migrated during the
>> execution of a stored procedure. So within a single sproc execution,
>> which includes the execution of nested sprocs, the spid won't change.
>>
>> We explored having the spid move with the connection when it is migrated,
>> but we felt the cost- benefit didn't work in favor of doing so. I'd be
>> interested to know how much of an issue it is to have the spid change
>> between commands.
>>
>> -Dave
>>
>> Bret Halford wrote:
>>> Actually, hold off. I hadn't noticed I'd wandered into the new
>>> "cluster" newsgroup, and I could be very wrong about it's behavior.
>>> My answer would apply to regular non-clustered ASE, though.
>>>
>>> Bret Halford wrote:
>>>
>>>> The spid is going to stay the same for the entire session. The client
>>>> would
>>>> have to exit and reconnect for @@spid to change. If you were to see
>>>> different @@spid values between a calling and child proc execution,
>>>> please
>>>> report it as a bug.
>>>>
>>>> A possibly related oddity that might be what is being noticed is that
>>>> the
>>>> sysprocesses table will sometimes show a different uid/login name as
>>>> procedures are executing. The is because procs execute as if they were
>>>> being run by the login that created them, and this sometimes shows
>>>> up in sysprocesses (typically most procs are created by "sa", so you
>>>> would
>>>> see a user session "becoming" sa at times).
>>>>
>>>> -bret
>>>>
>>>> Kevin, Leahy wrote:
>>>>
>>>>> Basically what we need to know is this:
>>>>>
>>>>> Here are two proc definitions. The first takes the same of
>>>>> another proc and runs it.
>>>>> The EXEC statement at the end just asks proc 1 to run proc
>>>>> 2.
>>>>>
>>>>> CREATE PROCEDURE dbo.test_proc_1 ( @sp VARCHAR(20) ) AS
>>>>> BEGIN
>>>>> SELECT 'In Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>>>>> EXEC @sp
>>>>> SELECT 'Out Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>>>>> END
>>>>> go
>>>>>
>>>>> CREATE PROCEDURE dbo.test_proc_2 AS BEGIN
>>>>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>>>>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>>>>> END
>>>>> go
>>>>>
>>>>> EXEC test_proc_1 'test_proc_2'
>>>>>
>>>>> If you run this SQL you will see that the same spid is
>>>>> returned everywhere.
>>>>>
>>>>> But we need to be sure this will always be the case, even
>>>>> when the database is "creaking" with large queries and
>>>>> multiple users. A colleage has noticed some inconsistencies
>>>>> that he thinks imply that Sybase may drop the spid and allow
>>>>> it to be reassigned to another process before the dynamic
>>>>> sql is complete. The question I think is really: does Sybase
>>>>> guarantee that the spid given to a calling proc will be held
>>>>> until that proc completes and that dynamic sql called from
>>>>> the calling proc will inherit the spid?
>>>>>
>>>>> Thanks
>>>>>
>>>>> Kevin
>
>


Sherlock, Kevin Posted on 2008-03-06 20:05:03.0Z
From: "Sherlock, Kevin" <ksherlock@tconl.com>
Newsgroups: sybase.public.ase.cluster
References: <47d0162a.284.1681692777@sybase.com> <47D01D10.59545EF7@sybase.com> <47D01D6A.8AAE9867@sybase.com> <47d023c9$1@forums-1-dub> <47d0265f$1@forums-1-dub> <47d02c53$1@forums-1-dub>
Subject: Re: spid assignment on dynamic sql
Lines: 139
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47d04e6f$1@forums-1-dub>
Date: 6 Mar 2008 12:05:03 -0800
X-Trace: forums-1-dub 1204833903 10.22.241.152 (6 Mar 2008 12:05:03 -0800)
X-Original-Trace: 6 Mar 2008 12:05:03 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:31
Article PK: 48316

Sorry, I meant the default SYS_CONTEXT structure as in the call:

select get_appcontext('SYS_CONTEXT','<parameter>')

Like

select get_appcontext('SYS_CONTEXT','spid')
select get_appcontext('SYS_CONTEXT','hostname')
select get_appcontext('SYS_CONTEXT','program_name')

...
etc.

And the question wasn't just about SYS_CONTEXT, but any application defined
application context variable sets.

"David Wein" <david.wein@sybase.com> wrote in message
news:47d02c53$1@forums-1-dub...
> SYS_CONTEXT is an Oracle thing, correct?
>
> Your application context should stay in tact. This includes current
> database, set options, etc. The spid is a bit of a challenge because that
> number is actually an index into an ASE memory pool. If we want to move
> the spid then we need to decouple it from the memory pool index, which can
> be done but is a pain.
>
> There are some restrictions about what connections can and can't be
> migrated, and certain elements of the context may prevent the connection
> from being migrated. The user guide discusses this.
>
> -Dave
>
> Sherlock, Kevin wrote:
>> Which conjures up a question in my mind. Do all of the appcontext
>> contexts migrate with the connection? If so, does SYS_CONTEXT stay in
>> tact, or is it re-populated as you describe below?
>>
>> "David Wein" <david.wein@sybase.com> wrote in message
>> news:47d023c9$1@forums-1-dub...
>>> Right, the spid can be a little more elusive in Cluster Edition.
>>>
>>> One of the key capabilities is connection migration, whereby an existing
>>> connection is moved from one instance of the cluster to another. This
>>> happens without the application having to code for it, or even knowing
>>> about it. The current design results in a new spid upon migration.
>>>
>>> So if you are in an isql session and you run:
>>>
>>> 1> select @@spid
>>> 2> go
>>> 1> select @@spid
>>> 2> go
>>>
>>> there is no guarantee the two selects will return the same value.
>>>
>>> However it is important to note that you will not be migrated during the
>>> execution of a stored procedure. So within a single sproc execution,
>>> which includes the execution of nested sprocs, the spid won't change.
>>>
>>> We explored having the spid move with the connection when it is
>>> migrated, but we felt the cost- benefit didn't work in favor of doing
>>> so. I'd be interested to know how much of an issue it is to have the
>>> spid change between commands.
>>>
>>> -Dave
>>>
>>> Bret Halford wrote:
>>>> Actually, hold off. I hadn't noticed I'd wandered into the new
>>>> "cluster" newsgroup, and I could be very wrong about it's behavior.
>>>> My answer would apply to regular non-clustered ASE, though.
>>>>
>>>> Bret Halford wrote:
>>>>
>>>>> The spid is going to stay the same for the entire session. The client
>>>>> would
>>>>> have to exit and reconnect for @@spid to change. If you were to see
>>>>> different @@spid values between a calling and child proc execution,
>>>>> please
>>>>> report it as a bug.
>>>>>
>>>>> A possibly related oddity that might be what is being noticed is that
>>>>> the
>>>>> sysprocesses table will sometimes show a different uid/login name as
>>>>> procedures are executing. The is because procs execute as if they
>>>>> were
>>>>> being run by the login that created them, and this sometimes shows
>>>>> up in sysprocesses (typically most procs are created by "sa", so you
>>>>> would
>>>>> see a user session "becoming" sa at times).
>>>>>
>>>>> -bret
>>>>>
>>>>> Kevin, Leahy wrote:
>>>>>
>>>>>> Basically what we need to know is this:
>>>>>>
>>>>>> Here are two proc definitions. The first takes the same of
>>>>>> another proc and runs it.
>>>>>> The EXEC statement at the end just asks proc 1 to run proc
>>>>>> 2.
>>>>>>
>>>>>> CREATE PROCEDURE dbo.test_proc_1 ( @sp VARCHAR(20) ) AS
>>>>>> BEGIN
>>>>>> SELECT 'In Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>>>>>> EXEC @sp
>>>>>> SELECT 'Out Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>>>>>> END
>>>>>> go
>>>>>>
>>>>>> CREATE PROCEDURE dbo.test_proc_2 AS BEGIN
>>>>>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>>>>>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>>>>>> END
>>>>>> go
>>>>>>
>>>>>> EXEC test_proc_1 'test_proc_2'
>>>>>>
>>>>>> If you run this SQL you will see that the same spid is
>>>>>> returned everywhere.
>>>>>>
>>>>>> But we need to be sure this will always be the case, even
>>>>>> when the database is "creaking" with large queries and
>>>>>> multiple users. A colleage has noticed some inconsistencies
>>>>>> that he thinks imply that Sybase may drop the spid and allow
>>>>>> it to be reassigned to another process before the dynamic
>>>>>> sql is complete. The question I think is really: does Sybase
>>>>>> guarantee that the spid given to a calling proc will be held
>>>>>> until that proc completes and that dynamic sql called from
>>>>>> the calling proc will inherit the spid?
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>> Kevin
>>
>>
>


David Wein Posted on 2008-03-10 14:59:10.0Z
From: David Wein <david.wein@sybase.com>
User-Agent: Thunderbird 1.5.0.12 (X11/20071020)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.cluster
Subject: Re: spid assignment on dynamic sql
References: <47d0162a.284.1681692777@sybase.com> <47D01D10.59545EF7@sybase.com> <47D01D6A.8AAE9867@sybase.com> <47d023c9$1@forums-1-dub> <47d0265f$1@forums-1-dub> <47d02c53$1@forums-1-dub> <47d04e6f$1@forums-1-dub>
In-Reply-To: <47d04e6f$1@forums-1-dub>
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: <47d54cbe$1@forums-1-dub>
Date: 10 Mar 2008 06:59:10 -0800
X-Trace: forums-1-dub 1205161150 10.22.241.152 (10 Mar 2008 06:59:10 -0800)
X-Original-Trace: 10 Mar 2008 06:59:10 -0800, vip152.sybase.com
Lines: 146
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:32
Article PK: 48317

Hi Kevin. As usual, your knowledge of ASE exceeds mine :-)

Yes, the 'SYS_CONTEXT' stuff will all be the same - except for the spid.

Migration of user created contexts will be supported in a future ESD.

-Dave

Sherlock, Kevin wrote:
> Sorry, I meant the default SYS_CONTEXT structure as in the call:
>
> select get_appcontext('SYS_CONTEXT','<parameter>')
>
> Like
>
> select get_appcontext('SYS_CONTEXT','spid')
> select get_appcontext('SYS_CONTEXT','hostname')
> select get_appcontext('SYS_CONTEXT','program_name')
>
> ...
> etc.
>
> And the question wasn't just about SYS_CONTEXT, but any application defined
> application context variable sets.
>
>
> "David Wein" <david.wein@sybase.com> wrote in message
> news:47d02c53$1@forums-1-dub...
>> SYS_CONTEXT is an Oracle thing, correct?
>>
>> Your application context should stay in tact. This includes current
>> database, set options, etc. The spid is a bit of a challenge because that
>> number is actually an index into an ASE memory pool. If we want to move
>> the spid then we need to decouple it from the memory pool index, which can
>> be done but is a pain.
>>
>> There are some restrictions about what connections can and can't be
>> migrated, and certain elements of the context may prevent the connection
>> from being migrated. The user guide discusses this.
>>
>> -Dave
>>
>> Sherlock, Kevin wrote:
>>> Which conjures up a question in my mind. Do all of the appcontext
>>> contexts migrate with the connection? If so, does SYS_CONTEXT stay in
>>> tact, or is it re-populated as you describe below?
>>>
>>> "David Wein" <david.wein@sybase.com> wrote in message
>>> news:47d023c9$1@forums-1-dub...
>>>> Right, the spid can be a little more elusive in Cluster Edition.
>>>>
>>>> One of the key capabilities is connection migration, whereby an existing
>>>> connection is moved from one instance of the cluster to another. This
>>>> happens without the application having to code for it, or even knowing
>>>> about it. The current design results in a new spid upon migration.
>>>>
>>>> So if you are in an isql session and you run:
>>>>
>>>> 1> select @@spid
>>>> 2> go
>>>> 1> select @@spid
>>>> 2> go
>>>>
>>>> there is no guarantee the two selects will return the same value.
>>>>
>>>> However it is important to note that you will not be migrated during the
>>>> execution of a stored procedure. So within a single sproc execution,
>>>> which includes the execution of nested sprocs, the spid won't change.
>>>>
>>>> We explored having the spid move with the connection when it is
>>>> migrated, but we felt the cost- benefit didn't work in favor of doing
>>>> so. I'd be interested to know how much of an issue it is to have the
>>>> spid change between commands.
>>>>
>>>> -Dave
>>>>
>>>> Bret Halford wrote:
>>>>> Actually, hold off. I hadn't noticed I'd wandered into the new
>>>>> "cluster" newsgroup, and I could be very wrong about it's behavior.
>>>>> My answer would apply to regular non-clustered ASE, though.
>>>>>
>>>>> Bret Halford wrote:
>>>>>
>>>>>> The spid is going to stay the same for the entire session. The client
>>>>>> would
>>>>>> have to exit and reconnect for @@spid to change. If you were to see
>>>>>> different @@spid values between a calling and child proc execution,
>>>>>> please
>>>>>> report it as a bug.
>>>>>>
>>>>>> A possibly related oddity that might be what is being noticed is that
>>>>>> the
>>>>>> sysprocesses table will sometimes show a different uid/login name as
>>>>>> procedures are executing. The is because procs execute as if they
>>>>>> were
>>>>>> being run by the login that created them, and this sometimes shows
>>>>>> up in sysprocesses (typically most procs are created by "sa", so you
>>>>>> would
>>>>>> see a user session "becoming" sa at times).
>>>>>>
>>>>>> -bret
>>>>>>
>>>>>> Kevin, Leahy wrote:
>>>>>>
>>>>>>> Basically what we need to know is this:
>>>>>>>
>>>>>>> Here are two proc definitions. The first takes the same of
>>>>>>> another proc and runs it.
>>>>>>> The EXEC statement at the end just asks proc 1 to run proc
>>>>>>> 2.
>>>>>>>
>>>>>>> CREATE PROCEDURE dbo.test_proc_1 ( @sp VARCHAR(20) ) AS
>>>>>>> BEGIN
>>>>>>> SELECT 'In Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>>>>>>> EXEC @sp
>>>>>>> SELECT 'Out Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>>>>>>> END
>>>>>>> go
>>>>>>>
>>>>>>> CREATE PROCEDURE dbo.test_proc_2 AS BEGIN
>>>>>>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>>>>>>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>>>>>>> END
>>>>>>> go
>>>>>>>
>>>>>>> EXEC test_proc_1 'test_proc_2'
>>>>>>>
>>>>>>> If you run this SQL you will see that the same spid is
>>>>>>> returned everywhere.
>>>>>>>
>>>>>>> But we need to be sure this will always be the case, even
>>>>>>> when the database is "creaking" with large queries and
>>>>>>> multiple users. A colleage has noticed some inconsistencies
>>>>>>> that he thinks imply that Sybase may drop the spid and allow
>>>>>>> it to be reassigned to another process before the dynamic
>>>>>>> sql is complete. The question I think is really: does Sybase
>>>>>>> guarantee that the spid given to a calling proc will be held
>>>>>>> until that proc completes and that dynamic sql called from
>>>>>>> the calling proc will inherit the spid?
>>>>>>>
>>>>>>> Thanks
>>>>>>>
>>>>>>> Kevin
>>>
>
>


Sherlock, Kevin Posted on 2008-03-10 15:14:13.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.cluster
References: <47d0162a.284.1681692777@sybase.com> <47D01D10.59545EF7@sybase.com> <47D01D6A.8AAE9867@sybase.com> <47d023c9$1@forums-1-dub> <47d0265f$1@forums-1-dub> <47d02c53$1@forums-1-dub> <47d04e6f$1@forums-1-dub> <47d54cbe$1@forums-1-dub>
Subject: Re: spid assignment on dynamic sql
Lines: 166
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47d55045$1@forums-1-dub>
Date: 10 Mar 2008 07:14:13 -0800
X-Trace: forums-1-dub 1205162053 10.22.241.152 (10 Mar 2008 07:14:13 -0800)
X-Original-Trace: 10 Mar 2008 07:14:13 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:33
Article PK: 48319


"David Wein" <david.wein@sybase.com> wrote in message
news:47d54cbe$1@forums-1-dub...
> Hi Kevin. As usual, your knowledge of ASE exceeds mine :-)

That would be the day Dave... that would be the day... :)

> Yes, the 'SYS_CONTEXT' stuff will all be the same - except for the spid.
>
> Migration of user created contexts will be supported in a future ESD.
>

Ok thanks, that's what I was curious about.

But, the source of the confusion is mine. My hands typed "SYS_CONTEXT" when
they should have typed "SYS_SESSION". Sorry for the confusion. And, yes, I
am a FORMER Oracl* DBA so perhaps some of my typing muscle memory is still
with me after all these years. :)

Thanks for the answers Dave. As usual your knowledge of what I'm _trying_
to ask about ASE is greater than mine :).

>
> Sherlock, Kevin wrote:
>> Sorry, I meant the default SYS_CONTEXT structure as in the call:
>>
>> select get_appcontext('SYS_CONTEXT','<parameter>')
>>
>> Like
>>
>> select get_appcontext('SYS_CONTEXT','spid')
>> select get_appcontext('SYS_CONTEXT','hostname')
>> select get_appcontext('SYS_CONTEXT','program_name')
>>
>> ...
>> etc.
>>
>> And the question wasn't just about SYS_CONTEXT, but any application
>> defined application context variable sets.
>>
>>
>> "David Wein" <david.wein@sybase.com> wrote in message
>> news:47d02c53$1@forums-1-dub...
>>> SYS_CONTEXT is an Oracle thing, correct?
>>>
>>> Your application context should stay in tact. This includes current
>>> database, set options, etc. The spid is a bit of a challenge because
>>> that number is actually an index into an ASE memory pool. If we want to
>>> move the spid then we need to decouple it from the memory pool index,
>>> which can be done but is a pain.
>>>
>>> There are some restrictions about what connections can and can't be
>>> migrated, and certain elements of the context may prevent the connection
>>> from being migrated. The user guide discusses this.
>>>
>>> -Dave
>>>
>>> Sherlock, Kevin wrote:
>>>> Which conjures up a question in my mind. Do all of the appcontext
>>>> contexts migrate with the connection? If so, does SYS_CONTEXT stay in
>>>> tact, or is it re-populated as you describe below?
>>>>
>>>> "David Wein" <david.wein@sybase.com> wrote in message
>>>> news:47d023c9$1@forums-1-dub...
>>>>> Right, the spid can be a little more elusive in Cluster Edition.
>>>>>
>>>>> One of the key capabilities is connection migration, whereby an
>>>>> existing connection is moved from one instance of the cluster to
>>>>> another. This happens without the application having to code for it,
>>>>> or even knowing about it. The current design results in a new spid
>>>>> upon migration.
>>>>>
>>>>> So if you are in an isql session and you run:
>>>>>
>>>>> 1> select @@spid
>>>>> 2> go
>>>>> 1> select @@spid
>>>>> 2> go
>>>>>
>>>>> there is no guarantee the two selects will return the same value.
>>>>>
>>>>> However it is important to note that you will not be migrated during
>>>>> the execution of a stored procedure. So within a single sproc
>>>>> execution, which includes the execution of nested sprocs, the spid
>>>>> won't change.
>>>>>
>>>>> We explored having the spid move with the connection when it is
>>>>> migrated, but we felt the cost- benefit didn't work in favor of doing
>>>>> so. I'd be interested to know how much of an issue it is to have the
>>>>> spid change between commands.
>>>>>
>>>>> -Dave
>>>>>
>>>>> Bret Halford wrote:
>>>>>> Actually, hold off. I hadn't noticed I'd wandered into the new
>>>>>> "cluster" newsgroup, and I could be very wrong about it's behavior.
>>>>>> My answer would apply to regular non-clustered ASE, though.
>>>>>>
>>>>>> Bret Halford wrote:
>>>>>>
>>>>>>> The spid is going to stay the same for the entire session. The
>>>>>>> client
>>>>>>> would
>>>>>>> have to exit and reconnect for @@spid to change. If you were to
>>>>>>> see
>>>>>>> different @@spid values between a calling and child proc execution,
>>>>>>> please
>>>>>>> report it as a bug.
>>>>>>>
>>>>>>> A possibly related oddity that might be what is being noticed is
>>>>>>> that
>>>>>>> the
>>>>>>> sysprocesses table will sometimes show a different uid/login name as
>>>>>>> procedures are executing. The is because procs execute as if they
>>>>>>> were
>>>>>>> being run by the login that created them, and this sometimes shows
>>>>>>> up in sysprocesses (typically most procs are created by "sa", so you
>>>>>>> would
>>>>>>> see a user session "becoming" sa at times).
>>>>>>>
>>>>>>> -bret
>>>>>>>
>>>>>>> Kevin, Leahy wrote:
>>>>>>>
>>>>>>>> Basically what we need to know is this:
>>>>>>>>
>>>>>>>> Here are two proc definitions. The first takes the same of
>>>>>>>> another proc and runs it.
>>>>>>>> The EXEC statement at the end just asks proc 1 to run proc
>>>>>>>> 2.
>>>>>>>>
>>>>>>>> CREATE PROCEDURE dbo.test_proc_1 ( @sp VARCHAR(20) ) AS
>>>>>>>> BEGIN
>>>>>>>> SELECT 'In Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>>>>>>>> EXEC @sp
>>>>>>>> SELECT 'Out Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>>>>>>>> END
>>>>>>>> go
>>>>>>>>
>>>>>>>> CREATE PROCEDURE dbo.test_proc_2 AS BEGIN
>>>>>>>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>>>>>>>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>>>>>>>> END
>>>>>>>> go
>>>>>>>>
>>>>>>>> EXEC test_proc_1 'test_proc_2'
>>>>>>>>
>>>>>>>> If you run this SQL you will see that the same spid is
>>>>>>>> returned everywhere.
>>>>>>>>
>>>>>>>> But we need to be sure this will always be the case, even
>>>>>>>> when the database is "creaking" with large queries and
>>>>>>>> multiple users. A colleage has noticed some inconsistencies
>>>>>>>> that he thinks imply that Sybase may drop the spid and allow
>>>>>>>> it to be reassigned to another process before the dynamic
>>>>>>>> sql is complete. The question I think is really: does Sybase
>>>>>>>> guarantee that the spid given to a calling proc will be held
>>>>>>>> until that proc completes and that dynamic sql called from
>>>>>>>> the calling proc will inherit the spid?
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>>
>>>>>>>> Kevin
>>>>
>>


Sherlock, Kevin Posted on 2008-03-06 17:08:39.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.cluster
References: <47d0162a.284.1681692777@sybase.com>
Subject: Re: spid assignment on dynamic sql
Lines: 60
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47d02517$1@forums-1-dub>
Date: 6 Mar 2008 09:08:39 -0800
X-Trace: forums-1-dub 1204823319 10.22.241.152 (6 Mar 2008 09:08:39 -0800)
X-Original-Trace: 6 Mar 2008 09:08:39 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:25
Article PK: 48311

Well, you aren't using Dynamic SQL here per se. You may be confusing the
"exec @sp" construct with the "exec ()" construct.
However, in both cases I would expect the @@spid global variable to be the
same in both calls.

I can't think of a situation where a spid would be "reassigned" in the
context of a single connection instance. The only time when I could see
where spid is tough to rely on is when parallel processing is taking place
and you have several worker processes working on a query with a single
"family_id" assigned to them.

Perhaps a better (more reliable?) way to obtain the current spid would be to
use the built-in get_appcontext() function. ie:

declare @myspid varchar(10)
select @myspid = get_appcontext('SYS_SESSION', 'spid')

<Kevin Leahy> wrote in message news:47d0162a.284.1681692777@sybase.com...
> Basically what we need to know is this:
>
> Here are two proc definitions. The first takes the same of
> another proc and runs it.
> The EXEC statement at the end just asks proc 1 to run proc
> 2.
>
> CREATE PROCEDURE dbo.test_proc_1 ( @sp VARCHAR(20) ) AS
> BEGIN
> SELECT 'In Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
> EXEC @sp
> SELECT 'Out Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
> END
> go
>
> CREATE PROCEDURE dbo.test_proc_2 AS BEGIN
> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
> END
> go
>
> EXEC test_proc_1 'test_proc_2'
>
> If you run this SQL you will see that the same spid is
> returned everywhere.
>
> But we need to be sure this will always be the case, even
> when the database is "creaking" with large queries and
> multiple users. A colleage has noticed some inconsistencies
> that he thinks imply that Sybase may drop the spid and allow
> it to be reassigned to another process before the dynamic
> sql is complete. The question I think is really: does Sybase
> guarantee that the spid given to a calling proc will be held
> until that proc completes and that dynamic sql called from
> the calling proc will inherit the spid?
>
> Thanks
>
> Kevin


Sherlock, Kevin Posted on 2008-03-06 17:11:08.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.cluster
References: <47d0162a.284.1681692777@sybase.com> <47d02517$1@forums-1-dub>
Subject: Re: spid assignment on dynamic sql
Lines: 71
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47d025ac$1@forums-1-dub>
Date: 6 Mar 2008 09:11:08 -0800
X-Trace: forums-1-dub 1204823468 10.22.241.152 (6 Mar 2008 09:11:08 -0800)
X-Original-Trace: 6 Mar 2008 09:11:08 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:26
Article PK: 48312

Oops. I forgot that i was in the Cluster newsgroup as well. All my
previous verbage applies to non-CE of course. Looks like David's response
is what you were looking for.

Sorry for the confusion.

"Sherlock, Kevin" <kevin.sherlock@teamsybase.com> wrote in message
news:47d02517$1@forums-1-dub...
> Well, you aren't using Dynamic SQL here per se. You may be confusing the
> "exec @sp" construct with the "exec ()" construct.
> However, in both cases I would expect the @@spid global variable to be the
> same in both calls.
>
> I can't think of a situation where a spid would be "reassigned" in the
> context of a single connection instance. The only time when I could see
> where spid is tough to rely on is when parallel processing is taking place
> and you have several worker processes working on a query with a single
> "family_id" assigned to them.
>
> Perhaps a better (more reliable?) way to obtain the current spid would be
> to use the built-in get_appcontext() function. ie:
>
> declare @myspid varchar(10)
> select @myspid = get_appcontext('SYS_SESSION', 'spid')
>
>
> <Kevin Leahy> wrote in message news:47d0162a.284.1681692777@sybase.com...
>> Basically what we need to know is this:
>>
>> Here are two proc definitions. The first takes the same of
>> another proc and runs it.
>> The EXEC statement at the end just asks proc 1 to run proc
>> 2.
>>
>> CREATE PROCEDURE dbo.test_proc_1 ( @sp VARCHAR(20) ) AS
>> BEGIN
>> SELECT 'In Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>> EXEC @sp
>> SELECT 'Out Test Proc 1: ' + CONVERT(VARCHAR(10),@@spid)
>> END
>> go
>>
>> CREATE PROCEDURE dbo.test_proc_2 AS BEGIN
>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>> SELECT 'In Test Proc 2: ' + CONVERT(VARCHAR(10),@@spid)
>> END
>> go
>>
>> EXEC test_proc_1 'test_proc_2'
>>
>> If you run this SQL you will see that the same spid is
>> returned everywhere.
>>
>> But we need to be sure this will always be the case, even
>> when the database is "creaking" with large queries and
>> multiple users. A colleage has noticed some inconsistencies
>> that he thinks imply that Sybase may drop the spid and allow
>> it to be reassigned to another process before the dynamic
>> sql is complete. The question I think is really: does Sybase
>> guarantee that the spid given to a calling proc will be held
>> until that proc completes and that dynamic sql called from
>> the calling proc will inherit the spid?
>>
>> Thanks
>>
>> Kevin
>
>