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.

Extend sybsystemprocs usage

14 posts in Product Futures Discussion Last posting was on 2003-07-16 18:33:28.0Z
putnamr Posted on 2003-07-10 10:05:42.0Z
From: putnamr@river.it.gvsu.edu
Date: Thu, 10 Jul 2003 06:05:42 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Extend sybsystemprocs usage
Message-ID: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums>
Lines: 9
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1284
Article PK: 95521

Hello,

I would like Sybase to extend the sybsytemprocs ability to execute stored
procedures in any database to views. That is, I would like views that are
created in sybsystemprocs to be able to be executed in any database as
though they were local to that database.

Thanks,
Ryan Putnam


Rob Verschoor Posted on 2003-07-10 15:06:13.0Z
Reply-To: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
References: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums>
Subject: Re: Extend sybsystemprocs usage
Date: Thu, 10 Jul 2003 17:06:13 +0200
Lines: 42
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <#AmGAavRDHA.338@forums-2-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: a66085.upc-a.chello.nl 62.163.66.85
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1285
Article PK: 95520


<putnamr@river.it.gvsu.edu> wrote in message
news:6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums...
> Hello,
>
> I would like Sybase to extend the sybsytemprocs ability to execute
stored
> procedures in any database to views. That is, I would like views
that are
> created in sybsystemprocs to be able to be executed in any database
as
> though they were local to that database.
>
> Thanks,
> Ryan Putnam

I don't think this will be possible. The reason why the sp_* procs
work is simply that they refer to system tables whose id and schema is
always identical in all databases. For user tables, this is unlikely
to be true, so compiled object referring to those user tables will not
be able to work the same way as the sp_* procs.

Note that you can actually simulate most of what you describe with
with exec-immediate.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

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

New book(coming soon): 'Tips, Tricks & Recipes for Sybase ASE'
For details, see http://www.sypron.nl/ttr

mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------


putnamr Posted on 2003-07-10 17:09:00.0Z
From: putnamr@river.it.gvsu.edu
Date: Thu, 10 Jul 2003 13:09:00 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Extend sybsystemprocs usage
Message-ID: <8BBF8C6384F2F385005E355A85256D5F.005AF50D85256D5F@webforums>
References: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums> <#AmGAavRDHA.338@forums-2-dub>
Lines: 9
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1286
Article PK: 95526

Hello,

I don't want this feature for user objects. I want this feature for system
tables. That is views on system tables. Views on system tables are much
more efficient and can be accessed more ways and more questions posed than
with stored procedures. That is my believe anyway.

Thanks,
Ryan Putnam


"Ilya Zvyagin" <ziv{ Posted on 2003-07-11 10:35:42.0Z
Reply-To: "Ilya Zvyagin" <ziv{@}fct{.}ru>
From: "Ilya Zvyagin" <ziv{@}fct{.}ru>
References: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums>
Subject: Re: Extend sybsystemprocs usage
Date: Fri, 11 Jul 2003 14:35:42 +0400
Lines: 18
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
X-Comment-To: putnamr@river.it.gvsu.edu
Organization: FCT Ltd
Message-ID: <1057919684.945051@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1289
Article PK: 95527

Hello, putnamr@river.it.gvsu.edu!
You wrote on Thu, 10 Jul 2003 06:05:42 -0400:

p> I would like Sybase to extend the sybsytemprocs ability to execute
p> stored procedures in any database to views. That is, I would like
p> views that are created in sybsystemprocs to be able to be executed
p> in any database as though they were local to that database.

This is NOT a good idea, as only system procedures are in sybsystemprocs
and master databases. And views can not be system, they belong to concrete
databases.

------------------------------------
Ilya Zvyagin
E-mail: ziv[AT]fct[dot]ru
ICQ# 29427861


putnamr Posted on 2003-07-11 11:34:08.0Z
From: putnamr@river.it.gvsu.edu
Date: Fri, 11 Jul 2003 07:34:08 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Extend sybsystemprocs usage
Message-ID: <8D70A9B64674D9CE003F8CF985256D60.003F41F585256D60@webforums>
References: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums> <1057919684.945051@gatekeeper.fct.ru>
Lines: 5
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1290
Article PK: 95528

Views on system tables!!! Not views on user objects. It is a good idea. I
have to create the views in every single database because of this. Views
are BETTER than stored procedures. You can ask questions better and can
join on them. That's all I say.


Sherlock, Kevin Posted on 2003-07-11 15:01:58.0Z
Message-ID: <3F0ED164.F30CA631@qwest.com.nospam>
Date: Fri, 11 Jul 2003 10:01:58 -0500
From: "Sherlock, Kevin" <ksherlo@qwest.com.nospam>
Reply-To: ksherlo@qwest.com.nospam
Organization: QWEST DEX
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Subject: Re: Extend sybsystemprocs usage
References: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums> <1057919684.945051@gatekeeper.fct.ru> <8D70A9B64674D9CE003F8CF985256D60.003F41F585256D60@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: np45.qwest.com 155.70.39.45
Lines: 11
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1291
Article PK: 95529

Create your views in the model database, so that they are propogated to
every user database at creation time.

putnamr@river.it.gvsu.edu wrote:
>
> Views on system tables!!! Not views on user objects. It is a good idea. I
> have to create the views in every single database because of this. Views
> are BETTER than stored procedures. You can ask questions better and can
> join on them. That's all I say.


putnamr Posted on 2003-07-11 16:08:09.0Z
From: putnamr@river.it.gvsu.edu
Date: Fri, 11 Jul 2003 12:08:09 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Extend sybsystemprocs usage
Message-ID: <5B00E5D49D6D3E0F0058A2F085256D60.0057C0F585256D60@webforums>
References: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums> <1057919684.945051@gatekeeper.fct.ru> <8D70A9B64674D9CE003F8CF985256D60.003F41F585256D60@webforums> <3F0ED164.F30CA631@qwest.com.nospam>
Lines: 8
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1292
Article PK: 95534

Hello,

I have already done that. Maintenance on views is what sucks and I hate
stored procedures.

Thanks,
Ryan Putnam


Olivier Posted on 2003-07-16 09:35:34.0Z
From: "Olivier" <ociteau-NOCANSPAM@yahoo.fr>
Organization: 81.80.240.237
References: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums> <1057919684.945051@gatekeeper.fct.ru> <8D70A9B64674D9CE003F8CF985256D60.003F41F585256D60@webforums> <3F0ED164.F30CA631@qwest.com.nospam> <5B00E5D49D6D3E0F0058A2F085256D60.0057C0F585256D60@webforums>
X-Newsreader: AspNNTP 1.50 (Tri Hoang)
Subject: Re: Extend sybsystemprocs usage
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <uqfhc23SDHA.298@forums-2-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Wed, 16 Jul 2003 02:35:34 -0700
NNTP-Posting-Host: transinc-win02.netoptex.com 206.169.167.22
Lines: 11
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1296
Article PK: 95536

I am like you.
may you send me some of your views to : ociteauATyahooDOTfr ?
i am looking at replacement for sp_who and sp_lock.

On Fri, 11 Jul 2003 12:08:09 -0400,
in sybase.public.ase.product_futures_discussion

<putnamr@river.it.gvsu.edu> wrote:
>Hello,
>
>I have already done that. Maintenance on views is what sucks and I hate
>stored procedures.
>
>Thanks,
>Ryan Putnam
>


Peter Weighill Posted on 2003-07-16 12:59:07.0Z
From: "Peter Weighill" <noemail@noemail>
References: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums> <1057919684.945051@gatekeeper.fct.ru> <8D70A9B64674D9CE003F8CF985256D60.003F41F585256D60@webforums> <3F0ED164.F30CA631@qwest.com.nospam> <5B00E5D49D6D3E0F0058A2F085256D60.0057C0F585256D60@webforums> <uqfhc23SDHA.298@forums-2-dub>
Subject: Re: Extend sybsystemprocs usage
Date: Wed, 16 Jul 2003 13:59:07 +0100
Lines: 39
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <uXExUs5SDHA.298@forums-2-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: mail5.emap.com 217.33.123.34
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1297
Article PK: 95535

Try the following query as an alternative to sp_lock:

select
lockype=substring(v.name,1,11),
loginame=substring(suser_name(p.suid)+"
(pid="+rtrim(convert(char(4),l.spid))+")",1,30),

dbtable=substring(db_name(l.dbid)+".."+convert(char(30),object_name(l.id,l.d
bid)),1,32),
blk=convert(char(5),p.blocked),
page=l.page,
cmd=substring(p.cmd,1,15)
from master..syslocks l,
master..sysprocesses p,
master..spt_values v
where p.spid=l.spid
and l.type = v.number
and v.type = "L"

"Olivier" <ociteau-NOCANSPAM@yahoo.fr> wrote in message
news:uqfhc23SDHA.298@forums-2-dub...
> I am like you.
> may you send me some of your views to : ociteauATyahooDOTfr ?
> i am looking at replacement for sp_who and sp_lock.
>
> On Fri, 11 Jul 2003 12:08:09 -0400,
> in sybase.public.ase.product_futures_discussion
> <putnamr@river.it.gvsu.edu> wrote:
> >Hello,
> >
> >I have already done that. Maintenance on views is what sucks and I hate
> >stored procedures.
> >
> >Thanks,
> >Ryan Putnam
> >


Olivier Posted on 2003-07-16 14:55:02.0Z
From: "Olivier" <ociteau-NOCANSPAM@yahoo.fr>
Organization: 81.80.240.237
References: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums> <1057919684.945051@gatekeeper.fct.ru> <8D70A9B64674D9CE003F8CF985256D60.003F41F585256D60@webforums> <3F0ED164.F30CA631@qwest.com.nospam> <5B00E5D49D6D3E0F0058A2F085256D60.0057C0F585256D60@webforums> <uqfhc23SDHA.298@forums-2-dub> <uXExUs5SDHA.298@forums-2-dub>
X-Newsreader: AspNNTP 1.50 (Tri Hoang)
Subject: Re: Extend sybsystemprocs usage
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <#7sZ9o6SDHA.145@forums-2-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Wed, 16 Jul 2003 07:55:02 -0700
NNTP-Posting-Host: transinc-win02.netoptex.com 206.169.167.22
Lines: 18
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1298
Article PK: 95537

Great !
Thanks a lot.

On Wed, 16 Jul 2003 13:59:07 +0100,
in sybase.public.ase.product_futures_discussion

Peter Weighill <noemail@noemail> wrote:
>Try the following query as an alternative to sp_lock:
>
>select
> lockype=substring(v.name,1,11),
> loginame=substring(suser_name(p.suid)+"
>(pid="+rtrim(convert(char(4),l.spid))+")",1,30),
>
>dbtable=substring(db_name(l.dbid)+".."+convert(char(30),object_name(l.id,l.d
>bid)),1,32),
> blk=convert(char(5),p.blocked),
> page=l.page,
> cmd=substring(p.cmd,1,15)
>from master..syslocks l,
> master..sysprocesses p,
> master..spt_values v
>where p.spid=l.spid
> and l.type = v.number
> and v.type = "L"
>
>


Peter Weighill Posted on 2003-07-13 12:36:39.0Z
From: "Peter Weighill" <noemail@noemail>
References: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums>
Subject: Re: Extend sybsystemprocs usage
Date: Sun, 13 Jul 2003 13:36:39 +0100
Lines: 10
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <OR3D5xTSDHA.298@forums-2-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: host213-122-91-161.in-addr.btopenworld.com 213.122.91.161
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1293
Article PK: 95533

It could be used to add the ANSI SQL INFORMATION_SCHEMA views.

<putnamr@river.it.gvsu.edu> wrote in message
news:6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums...
> I would like Sybase to extend the sybsytemprocs ability to execute stored
> procedures in any database to views. That is, I would like views that are
> created in sybsystemprocs to be able to be executed in any database as
> though they were local to that database.


putnamr Posted on 2003-07-14 16:27:14.0Z
From: putnamr@river.it.gvsu.edu
Date: Mon, 14 Jul 2003 12:27:14 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Extend sybsystemprocs usage
Message-ID: <C93B1DDC8228E476005A628485256D63.004CAC3F85256D62@webforums>
References: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums> <OR3D5xTSDHA.298@forums-2-dub>
Lines: 6
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1295
Article PK: 95538

Hello,

Praise intelligence!!! Someone gets it. Thank you!!!

Thanks,
Ryan Putnam


Eugene Korolkov Posted on 2003-07-16 17:26:33.0Z
Message-ID: <3F158AC9.F345B893@davidsohn.com>
Date: Wed, 16 Jul 2003 13:26:33 -0400
From: Eugene Korolkov <ekorolkov@davidsohn.com>
X-Mailer: Mozilla 4.77 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
To: putnamr@river.it.gvsu.edu
Subject: Re: Extend sybsystemprocs usage
References: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums> <OR3D5xTSDHA.298@forums-2-dub> <C93B1DDC8228E476005A628485256D63.004CAC3F85256D62@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 12.3.91.140
Lines: 14
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1300
Article PK: 95540

I think that in general system sps is a wrong idea at all,
select stmts should be done using views on system tables and
modification using "extended" SQL stmts like ALTER USER... etc.
(Of course it was much easier for Sybase creators not go that way but instead
create proprietary set of sps and add additional burden to DBA to remember
all that stuff)

Regards,
Eugene

putnamr@river.it.gvsu.edu wrote:

> Hello,
>
> Praise intelligence!!! Someone gets it. Thank you!!!
>
> Thanks,
> Ryan Putnam


Carl Kayser Posted on 2003-07-16 18:33:28.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
References: <6E80F4BAFD638F0C0037744585256D5F.0037745885256D5F@webforums> <OR3D5xTSDHA.298@forums-2-dub> <C93B1DDC8228E476005A628485256D63.004CAC3F85256D62@webforums> <3F158AC9.F345B893@davidsohn.com>
Subject: Re: Extend sybsystemprocs usage
Date: Wed, 16 Jul 2003 14:33:28 -0400
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <O#C62n8SDHA.298@forums-2-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 146.142.35.25
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1301
Article PK: 95541

I can see that this might become a rather contentious issue.

I also would prefer SQL primitives to "sp_" procedures. (As I remember the
sp_addrole procedure has been replaced by create role ... Obviously this is
release-dependent.)

I don't see any difference with respect to "memory burden".

Althogh Ryan does not like "sp" procedures it seems to me that what he wants
is sp-like views: define once and execute locally. Where would the view
binaries be stored? Answer: in sybsystemprocs or the equivalent. If this
is done I wish that there is no naming-convention restriction. If the
requested view is not in the current database then search the
"sybsystemprocs" database.

At one time a Sybase view could not refer to tables/views in another
database. Now it can. I'm not sure as to whether this is because of an
ANSI change, a re-interpretation thereof, or if it is an extension.

That said, I've become used to them and, at least for me, they are much more
powerful than views could become. But I'm not against the suggestion. I do
believe in using different tools to solve different problems.

"Eugene Korolkov" <ekorolkov@davidsohn.com> wrote in message
news:3F158AC9.F345B893@davidsohn.com...
> I think that in general system sps is a wrong idea at all,
> select stmts should be done using views on system tables and
> modification using "extended" SQL stmts like ALTER USER... etc.
> (Of course it was much easier for Sybase creators not go that way but
instead
> create proprietary set of sps and add additional burden to DBA to
remember
> all that stuff)
>
> Regards,
> Eugene
>
> putnamr@river.it.gvsu.edu wrote:
>
> > Hello,
> >
> > Praise intelligence!!! Someone gets it. Thank you!!!
> >
> > Thanks,
> > Ryan Putnam
>