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.

Stored procedures and the "current database"

4 posts in General Discussion Last posting was on 2009-12-10 15:56:28.0Z
John Flynn Posted on 2009-12-09 17:24:07.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
Subject: Stored procedures and the "current database"
Lines: 14
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b1fdd37@forums-1-dub>
Date: 9 Dec 2009 09:24:07 -0800
X-Trace: forums-1-dub 1260379447 10.22.241.152 (9 Dec 2009 09:24:07 -0800)
X-Original-Trace: 9 Dec 2009 09:24:07 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28740
Article PK: 77983

Hi.

On ASE 15.0.2. I have a stored procedure (not in sybsystemprocs) and I want
it to find out what is the "current database". So far I have not found a way
to do that. Calling db_id() and db_name() seem to return the database where
the sp resides and not necessarily the current database (contrary to the ASE
docs). Apparently, only if the sp is resident in sybsystemprocs do those
functions return the true current database. Is there some other way to do
what I want?

Thanks.
- John.


Carl Kayser Posted on 2009-12-09 17:51:12.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4b1fdd37@forums-1-dub>
Subject: Re: Stored procedures and the "current database"
Lines: 31
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3598
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b1fe390@forums-1-dub>
Date: 9 Dec 2009 09:51:12 -0800
X-Trace: forums-1-dub 1260381072 10.22.241.152 (9 Dec 2009 09:51:12 -0800)
X-Original-Trace: 9 Dec 2009 09:51:12 -0800, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28742
Article PK: 77984


"John Flynn" <jflynn@miqs.com> wrote in message
news:4b1fdd37@forums-1-dub...
> Hi.
>
> On ASE 15.0.2. I have a stored procedure (not in sybsystemprocs) and I
> want it to find out what is the "current database". So far I have not
> found a way to do that. Calling db_id() and db_name() seem to return the
> database where the sp resides and not necessarily the current database
> (contrary to the ASE docs). Apparently, only if the sp is resident in
> sybsystemprocs do those functions return the true current database. Is
> there some other way to do what I want?
>
> Thanks.
> - John.
>
>

Lot's of luck on this. There are similar problems with auditing: ISUG
Enhancement Request 2922 - sp_dropuser and similar commands always record
sybsystemprocs instead of the actual database being used.

One might try to query master..sysprocesses via @@spid but this also may not
be reliable: ISUG Enhancement Request 2039 is to add on the dbid where the
SP is located (versus the database that the user is "in"). Assuing that
your identical SPs in multiple databases (versus one SP in sybsystemprocs)
are always executed locally then this might be viable.

Another possibility might be to use an MDA table - but only SAs can do this.


"Mark A. Parsons" <iron_horse Posted on 2009-12-09 18:21:05.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: Stored procedures and the "current database"
References: <4b1fdd37@forums-1-dub>
In-Reply-To: <4b1fdd37@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091201-0, 12/01/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b1fea91$1@forums-1-dub>
Date: 9 Dec 2009 10:21:05 -0800
X-Trace: forums-1-dub 1260382865 10.22.241.152 (9 Dec 2009 10:21:05 -0800)
X-Original-Trace: 9 Dec 2009 10:21:05 -0800, vip152.sybase.com
Lines: 94
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28743
Article PK: 77985

For clarification, and to make sure we're on the same sheet of music, I'm going to go over what I *think* you're
experiencing ...

----------------------

I'm assuming that when you access the proc you have to include the name of the database where it resides.

If this is the case then what you're explaining makes sense, ie, your database contex is changed to the database where
the proc exists.

For example:

============================
use current_db
go

-- database context is changed to other_db

exec other_db..where_am_i
go

my_current_db
------------------
other_db

(1 row affected)
============================

When a proc resides in the sybsystemprocs (or master) database *AND* its name begins with 'sp_', then the dataserver (in
essence) disables the changing of your database context.

For example:

============================
use current_db
go

-- assumes sp_where_am_i is defined in the
-- sybsystemprocs or master database

-- database context remains as current_db

exec sp_where_am_i
go

my_current_db
------------------
current_db

(1 row affected)
============================

If the above is not a description of your situation, please post some more details (an example?) of your situation.

----------------------

It would be tempting to suggest accessing the sysprocesses, monProcess or monProcessProcedures tables, but this is only
going to tell you the database context for where the currently-executing stored proc is executed from ... not the
database context of where your spid/session currently resides.

In other words, if your proc invocation forces a change in database context ... then this changed context is what shows
up in the sysprocesses/monProcess/monProcessProcedures tables.

You could try pulling the most recent records for your spid from master..monSysStatement ... but this could be a
performance hassle if you've got a large volume of monSysStatement records to process. This also wouldn't be
*guaranteed* to work if the record you're looking for happens to be pushed off the end of the monSysStatement queue
before you can get to it.

----------------------

If your processing forces a change in database context ... I can't think of any way to get the dataserver to figure out
what your originating database context is.

Again, I'd stick with the K.I.S.S. principle ... preface the proc name with 'sp_' and place it in the sybsystemprocs
database. Short. Sweet. To the point. Does the job.

John Flynn wrote:
> Hi.
>
> On ASE 15.0.2. I have a stored procedure (not in sybsystemprocs) and I want
> it to find out what is the "current database". So far I have not found a way
> to do that. Calling db_id() and db_name() seem to return the database where
> the sp resides and not necessarily the current database (contrary to the ASE
> docs). Apparently, only if the sp is resident in sybsystemprocs do those
> functions return the true current database. Is there some other way to do
> what I want?
>
> Thanks.
> - John.
>
>


John Flynn Posted on 2009-12-10 15:56:28.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
References: <4b1fdd37@forums-1-dub> <4b1fea91$1@forums-1-dub>
Subject: Re: Stored procedures and the "current database"
Lines: 21
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b211a2c$1@forums-1-dub>
Date: 10 Dec 2009 07:56:28 -0800
X-Trace: forums-1-dub 1260460588 10.22.241.152 (10 Dec 2009 07:56:28 -0800)
X-Original-Trace: 10 Dec 2009 07:56:28 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28752
Article PK: 77994


Mark A. Parsons wrote:
> I'm assuming that when you access the proc you have to include the
> name of the database where it resides.

Yes.

== snip ==
> If the above is not a description of your situation, please post some
> more details (an example?) of your situation.

You described my situation exactly.

> Again, I'd stick with the K.I.S.S. principle ... preface the proc
> name with 'sp_' and place it in the sybsystemprocs database. Short.
> Sweet. To the point. Does the job.

Yes. Thanks for confirming there's no other "clean" way to do it.

- John.