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.

How to run DB-dependent function in another DB?

5 posts in General Discussion Last posting was on 2011-05-12 22:31:54.0Z
George Brink Posted on 2011-05-12 20:50:35.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: How to run DB-dependent function in another DB?
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: <4dcc481b$1@forums-1-dub>
Date: 12 May 2011 13:50:35 -0700
X-Trace: forums-1-dub 1305233435 10.22.241.152 (12 May 2011 13:50:35 -0700)
X-Original-Trace: 12 May 2011 13:50:35 -0700, vip152.sybase.com
Lines: 3
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30161
Article PK: 72338

Is it possible (and if yes how) to run database dependent function (ie
user_name() or user_id()) for a database MyDB without issuing command
use MyDB?


Rob V [ Sybase ] Posted on 2011-05-12 21:11:25.0Z
Message-ID: <4DCC4CFA.4030800@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
To: George Brink <siberianowl@yahoo.com>
Subject: Re: How to run DB-dependent function in another DB?
References: <4dcc481b$1@forums-1-dub>
In-Reply-To: <4dcc481b$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
Date: 12 May 2011 14:11:25 -0700
X-Trace: forums-1-dub 1305234685 10.22.241.152 (12 May 2011 14:11:25 -0700)
X-Original-Trace: 12 May 2011 14:11:25 -0700, vip152.sybase.com
Lines: 46
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30162
Article PK: 72340


On 12-May-2011 22:50, George Brink wrote:
> Is it possible (and if yes how) to run database dependent function (ie
> user_name() or user_id()) for a database MyDB without issuing command
> use MyDB?

Yes, by running it in a stored proc named sp_xyz which is located in
sybsystemprocs:

use sybsystemprocs
go
create proc sp_myproc @c varchar(100)
as
exec(@c)
go

use master
go
exec tempdb..sp_myproc "select db_name(), user_name()"
go
exec mydb..sp_myproc "select db_name(), user_name()"
go


The essence is described in the article at
http://www.sypron.nl/new_ssp.html (while some of the actual examples in
this article are no longer relevant since ASE has been improved on those
points, the overall principle still applies).

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
-----------------------------------------------------------------


George Brink Posted on 2011-05-12 21:37:26.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to run DB-dependent function in another DB?
References: <4dcc481b$1@forums-1-dub> <4DCC4CFA.4030800@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
In-Reply-To: <4DCC4CFA.4030800@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
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: <4dcc5316$1@forums-1-dub>
Date: 12 May 2011 14:37:26 -0700
X-Trace: forums-1-dub 1305236246 10.22.241.152 (12 May 2011 14:37:26 -0700)
X-Original-Trace: 12 May 2011 14:37:26 -0700, vip152.sybase.com
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30163
Article PK: 72342


On 5/12/2011 5:11 PM, Rob V [ Sybase ] wrote:
> On 12-May-2011 22:50, George Brink wrote:
>> Is it possible (and if yes how) to run database dependent function (ie
>> user_name() or user_id()) for a database MyDB without issuing command
>> use MyDB?
>
> Yes, by running it in a stored proc named sp_xyz which is located in
> sybsystemprocs:

But this is a stored procedure, not a function. You cannot use the
result of such workaround inside another query...

I came with a query:
-------------------------
select * from
mydb.dbo.sysusers su
where su.suid=suser_id()
or su.suid=(select altsuid
from mydb.dbo.sysalternates
where suid=suser_id())
or su.suid=-1
-------------------------
That can be used as a subquery instead of user_id() or user_name().


Rob V [ Sybase ] Posted on 2011-05-12 22:26:20.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to run DB-dependent function in another DB?
References: <4dcc481b$1@forums-1-dub> <4DCC4CFA.4030800@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> <4dcc5316$1@forums-1-dub>
In-Reply-To: <4dcc5316$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: <4dcc5e8c@forums-1-dub>
Date: 12 May 2011 15:26:20 -0700
X-Trace: forums-1-dub 1305239180 10.22.241.152 (12 May 2011 15:26:20 -0700)
X-Original-Trace: 12 May 2011 15:26:20 -0700, vip152.sybase.com
Lines: 46
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30164
Article PK: 72341


On 12-May-2011 23:37, George Brink wrote:
> On 5/12/2011 5:11 PM, Rob V [ Sybase ] wrote:
>> On 12-May-2011 22:50, George Brink wrote:
>>> Is it possible (and if yes how) to run database dependent function (ie
>>> user_name() or user_id()) for a database MyDB without issuing command
>>> use MyDB?
>>
>> Yes, by running it in a stored proc named sp_xyz which is located in
>> sybsystemprocs:
> But this is a stored procedure, not a function. You cannot use the
> result of such workaround inside another query...
>
> I came with a query:
> -------------------------
> select * from
> mydb.dbo.sysusers su
> where su.suid=suser_id()
> or su.suid=(select altsuid
> from mydb.dbo.sysalternates
> where suid=suser_id())
> or su.suid=-1
> -------------------------
> That can be used as a subquery instead of user_id() or user_name().

I'm not sure I understand exactly what you want to do, but you can use
exactly the same approach with SQL functions named sp_*, see the
downloadable collection at www.sypron.nl/udf.

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
-----------------------------------------------------------------


"Mark A. Parsons" <iron_horse Posted on 2011-05-12 22:31:54.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 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to run DB-dependent function in another DB?
References: <4dcc481b$1@forums-1-dub> <4DCC4CFA.4030800@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> <4dcc5316$1@forums-1-dub>
In-Reply-To: <4dcc5316$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: <4dcc5fda$1@forums-1-dub>
Date: 12 May 2011 15:31:54 -0700
X-Trace: forums-1-dub 1305239514 10.22.241.152 (12 May 2011 15:31:54 -0700)
X-Original-Trace: 12 May 2011 15:31:54 -0700, vip152.sybase.com
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30165
Article PK: 72344

If you're running 15.0.2+ you might want to look at the 'create function' command. This allows you to create your own
function which in turn can be referenced in a query just like a Sybase builtin function.

Alternatively, you could put a proxy table on the front of some code similar to what Rob's suggested. You could then
join the proxy table with your desired query. Take a gander at the examples at Rob's site
(http://www.sypron.nl/proctab.html) to get some ideas on how to turn proc results into (proxy) tables you can query.

If you have problems implementing either of the above just post back here with your test code and any issues and/or
error messages you're receiving. Also let us know if the target database name could vary or if a single, hard-coded
name (eg, 'mydb') is all that's needed ... as the two options will require different coding.

On 05/12/2011 17:37, George Brink wrote:
> On 5/12/2011 5:11 PM, Rob V [ Sybase ] wrote:
>> On 12-May-2011 22:50, George Brink wrote:
>>> Is it possible (and if yes how) to run database dependent function (ie
>>> user_name() or user_id()) for a database MyDB without issuing command
>>> use MyDB?
>>
>> Yes, by running it in a stored proc named sp_xyz which is located in
>> sybsystemprocs:
> But this is a stored procedure, not a function. You cannot use the result of such workaround inside another query...
>
> I came with a query:
> -------------------------
> select * from
> mydb.dbo.sysusers su
> where su.suid=suser_id()
> or su.suid=(select altsuid
> from mydb.dbo.sysalternates
> where suid=suser_id())
> or su.suid=-1
> -------------------------
> That can be used as a subquery instead of user_id() or user_name().