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.

Sybsystemprocs

5 posts in Windows NT Last posting was on 2001-03-16 05:02:05.0Z
Ramesh Posted on 2001-03-02 08:48:30.0Z
From: Ramesh
Date: Fri, 2 Mar 2001 03:48:30 -0500
Newsgroups: sybase.public.sqlserver.nt
Subject: Sybsystemprocs
Message-ID: <A24C756E40076B18003062D285256A03.003062E485256A03@webforums>
Lines: 22
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.nt:1160
Article PK: 1088616

Hi ,

We have a requirement werein we should be able to call a procedure from any
database but it ( the procedure) should access only those tables which are
specific to that database.

In other words, it should work like a system procedure which even run from
different databases accesses that particular database alone.

eg., sp_adduser adds a user to a database( say DB1) when run from that
database ( that is DB1) .
When run from another database (say DB2) adds a user to that database
- DB2.

I created a dummy procedure and a dummy table in sybsystemprocs .

The procedure gets called in any database. But the table doesn't.

Any ideas in this regard will be highly appreciated.

Thanks
Ramesh


Jim Egan Posted on 2001-03-16 05:02:05.0Z
From: Jim Egan <dbaguru@eganomics.com>
Subject: Re: Sybsystemprocs
Date: Thu, 15 Mar 2001 22:02:05 -0700
Message-ID: <MPG.151b4c34701ed0db98b1b5@forums.sybase.com>
References: <A24C756E40076B18003062D285256A03.003062E485256A03@webforums>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.30
Newsgroups: sybase.public.sqlserver.nt
Lines: 97
NNTP-Posting-Host: c1420400-b.hiland1.co.home.com 65.7.153.228
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1087
Article PK: 1087688

If you're on 12.0 you can do this with the EXEC statement. Here's an example:
DROP PROCEDURE dbo.sp_segmentallocations
GO

CREATE PROCEDURE dbo.sp_segmentallocations
@db_name VARCHAR(30) = NULL
AS
BEGIN
/*
Procedure: sp_segmentallocations
Author : Jim Egan
Date : 01/12/2000
Purpose : Provide a summary of Segment usage for the current database.
: The logic for this is based on system stored procedure
: sp_helpsegment <segmentname>.

Author : Jim Egan
Date : 10/10/2000
Purpose : Changed so the procedure can exist in sybsystemprocs
: and work on any database.

*/
declare @factor float, /* conversion factor to convert to MB */
@db_id INTEGER

if @@trancount = 0
begin
set chained off
end

set transaction isolation level 1

set nocount on

SELECT @db_id = DB_ID(@db_name)

IF @db_name IS NULL
BEGIN
SELECT @db_id = DB_ID(), @db_name = DB_NAME()
END

/*
** Get factor for conversion of pages to megabytes from spt_values
*/
select @factor = convert(float, low) / 1048576.0
from master.dbo.spt_values
where number = 1 and type = "E"

CREATE TABLE #seglist
(segment_name CHAR(30) NOT NULL,
segment INTEGER NULL,
segment_bit INTEGER NULL,
total_size CHAR(20) NULL,
total_pages INTEGER NULL,
free_pages INTEGER NULL,
used_pages INTEGER NULL)


/*
** Set the bit position for each segment.
*/
EXECUTE ('INSERT INTO #seglist (segment_name, segment, segment_bit) ' +
'SELECT name, segment, power(2, segment) ' +
'FROM ' + @db_name + '..syssegments WHERE segment < 31 ' +
'UNION ' +
'SELECT name, segment, (SELECT low FROM master.dbo.spt_values WHERE type = "E" AND
number = 2)' +
'FROM ' + @db_name + '..syssegments WHERE segment >= 31')


/*
** Print total_size, free_pages and used_pages
*/
UPDATE #seglist
SET total_size =
(SELECT CONVERT(varchar(20), ROUND((SUM(u.size) * @factor), 0)) + " MB"
FROM master.dbo.sysusages u, master.dbo.sysdevices d
WHERE u.segmap & #seglist.segment_bit = #seglist.segment_bit
and u.dbid = @db_id
and d.status & 2 = 2
and d.low <= u.vstart
and d.high >= u.vstart + (u.size - 1)),
total_pages =
(SELECT sum(u.size)
FROM master.dbo.sysusages u, master.dbo.sysdevices d
WHERE u.segmap & #seglist.segment_bit = #seglist.segment_bit
and u.dbid = @db_id
and d.status & 2 = 2
and d.low <= u.vstart
and d.high >= u.vstart + (u.size - 1)),
free_pages =
(SELECT sum(curunreservedpgs(@db_id, u.lstart, u.unreservedpgs))
FROM master.dbo.sysusages u, master.dbo.sysdevices d
WHERE u.segmap & #seglist.segment_bit = #seglist.segment_bit
and u.dbid = @db_id
and d.status & 2 = 2
and d.low <= u.vstart
and d.high >= u.vstart + (u.size - 1)),
used_pages =
(SELECT sum(u.size) - sum(curunreservedpgs(@db_id, u.lstart, u.unreservedpgs))
FROM master.dbo.sysusages u, master.dbo.sysdevices d
WHERE u.segmap & #seglist.segment_bit = #seglist.segment_bit
and u.dbid = @db_id
and d.status & 2 = 2
and d.low <= u.vstart
and d.high >= u.vstart + (u.size - 1))

SELECT segment_name,
CASE WHEN total_size = ' MB'
THEN 'Not Used'
ELSE total_size
END AS 'total_size',
total_pages,
free_pages,
used_pages,
percent_used = CONVERT(VARCHAR(6),
ROUND((CONVERT(NUMERIC(18,2), used_pages) /
CONVERT(NUMERIC(18,2), total_pages)) * 100.0, 2)) + '%'
FROM #seglist

return (0)
END /* End of Procedure */
GO

--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services

Get your free subscription to PowerTimes at http://www.powertimes.com


Sridhar Posted on 2001-03-02 18:21:17.0Z
From: sridhar
Date: Fri, 2 Mar 2001 13:21:17 -0500
Newsgroups: sybase.public.sqlserver.nt
Subject: Re: Sybsystemprocs
Message-ID: <8A9AE4302BB3FF4B0064D39F85256A03.0033B1E085256A03@webforums>
References: <A24C756E40076B18003062D285256A03.003062E485256A03@webforums>
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!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.nt:1157
Article PK: 1088647

Try specifying your table with its db in the SP:

create procedure test
as select *
from sybsystemprocs..test_table


sridhar


Ramesh Posted on 2001-03-05 08:37:54.0Z
From: Ramesh
Date: Mon, 5 Mar 2001 03:37:54 -0500
Newsgroups: sybase.public.sqlserver.nt
Subject: Re: Sybsystemprocs
Message-ID: <E02458B296E1940C002F6A7B85256A06.00683F2685256A03@webforums>
References: <A24C756E40076B18003062D285256A03.003062E485256A03@webforums> <8A9AE4302BB3FF4B0064D39F85256A03.0033B1E085256A03@webforums>
Lines: 3
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.nt:1155
Article PK: 1088648

I want it without the extension sybsystemprocs..

Ramesh


Bret Halford Posted on 2001-03-06 21:05:51.0Z
Message-ID: <3AA5512E.74B14AC@sybase.com>
Date: Tue, 06 Mar 2001 14:05:51 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Sybsystemprocs
References: <A24C756E40076B18003062D285256A03.003062E485256A03@webforums> <8A9AE4302BB3FF4B0064D39F85256A03.0033B1E085256A03@webforums> <E02458B296E1940C002F6A7B85256A06.00683F2685256A03@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 5
NNTP-Posting-Host: bret-pc.sybase.com 157.133.80.211
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1148
Article PK: 1088601

Perhaps you could create a view in your database based on the
sybsystemprocs table?

-bret

Ramesh wrote:

> I want it without the extension sybsystemprocs..
>
> Ramesh