SEO

I have the following scenario:
There is an existing st. proc. "p1" in database "db1" created by user
"dbo".
Now I am logging in as a different user and trying to replace the same
st. proc. "p1" in that database "db1". I'm executing the following:

-------
use db1
go

if exists( select * from sysobjects where name = 'p1' and type = 'P' )
begin
print "DROPPING STORED PROCEDURE : p1"
drop proc p1
end
go

print "CREATING STORED PROCEDURE : p1"
go

CREATE PROCEDURE p1
@parm1 char(12),
@parm2 char(12),
AS
BEGIN

.....

....
END

go
GRANT EXECUTE ON p1 TO <users>
go
--------------------------------------------

In the result from isql I'm getting:
------------------------------------
DROPPING STORED PROCEDURE : p1
Msg 10304, Level 14, State 1:
Server 'SERVER1', Line 5:
Only the owner of object 'p1' or a user with System
Administrator (SA) role can run this command.
CREATING STORED PROCEDURE : p1
------------------------------------

Finally I am finding that two versions of p1 is existing - the
existing one created by dbo and the new one created by me. In
sysobjects the differences for these entries are in:
id, uid, crdate, expdate , loginame

Is it a standard behaviour? ( I'm using Sybase 12).
If it is - that means it is something like Oracle's "same object name
under different schema". Was this behaviour there in earlier versions
of Sybase and does this hold good for any other type of objects (e.g.
tables/index/....) ?

Pls. enlighten on this issue.

Thanks in advance.

Sudip

Sudip wrote:

> Finally I am finding that two versions of p1 is existing - the
> existing one created by dbo and the new one created by me. In
> sysobjects the differences for these entries are in:
> id, uid, crdate, expdate , loginame
>
> Is it a standard behaviour? ( I'm using Sybase 12).

Yes, users can create their own objects with the same names as
those owned by others. You would need to qualify them by the
owner. You might also want to check out procedure groups.

> If it is - that means it is something like Oracle's "same object name
> under different schema".

Not quite. ASE supports schemas too but its not required for this.

> Was this behaviour there in earlier versions
> of Sybase and does this hold good for any other type of objects (e.g.
> tables/index/....) ?

Yes and yes (to some extent).

-am © 2003

Thanks Anthony. Could you pls. tell me what did you mean by "You might
also want to check out procedure groups".

Sudip

Anthony Mandic <ev@hotmail.com> wrote in message news:<3EF85D9D.2D254DCA@hotmail.com>...
> Sudip wrote:
>
> > Finally I am finding that two versions of p1 is existing - the
> > existing one created by dbo and the new one created by me. In
> > sysobjects the differences for these entries are in:
> > id, uid, crdate, expdate , loginame
> >
> > Is it a standard behaviour? ( I'm using Sybase 12).
>
> Yes, users can create their own objects with the same names as
> those owned by others. You would need to qualify them by the
> owner. You might also want to check out procedure groups.
>
> > If it is - that means it is something like Oracle's "same object name
> > under different schema".
>
> Not quite. ASE supports schemas too but its not required for this.
>
> > Was this behaviour there in earlier versions
> > of Sybase and does this hold good for any other type of objects (e.g.
> > tables/index/....) ?
>
> Yes and yes (to some extent).
>
> -am © 2003

Sudip wrote:
>
> Thanks Anthony. Could you pls. tell me what did you mean by "You might
> also want to check out procedure groups".

Sproc groups are another, related interesting area. You can create
several sprocs in a group which all have the same name but are
distinguished by a number for the individual sprocs in the group.
The manuals have the details.

-am © 2003

Also, some information about proc grouping is available at
www.sypron.nl/quiz -- go to the question for September 2002,

HTH,

Rob V.

"Anthony Mandic" <ey@hotmail.com> wrote in message
news:3EF92C4B.269F3061@hotmail.com...
> Sudip wrote:
> >
> > Thanks Anthony. Could you pls. tell me what did you mean by "You
might
> > also want to check out procedure groups".
>
> Sproc groups are another, related interesting area. You can create
> several sprocs in a group which all have the same name but are
> distinguished by a number for the individual sprocs in the group.
> The manuals have the details.
>
> -am © 2003