/** This part copied from Sybase test **/
/* Script generated by dbschema.pl(2.4.2) on Mon Aug 18 14:15:25 2003. */
/* Script extracted on a solaris system. */
use master
go
/* Groups... */
/* No groups found. */
/* Users... */
exec sp_adduser 'guest', 'guest'
exec sp_adduser 'probe', 'probe'
go
/* Aliases... */
/* No aliases found. */
/* Add user-defined data types: */
/* No user defined types found. */
/* Rules... */
/* No rules found. */
/* Defaults... */
/* No defaults found. */
/* Bind rules & defaults to user data types... */
/* No defaults to bind. */
/* No rules to bind. */
/* Start of description of table dbo.jdbc_function_escapes */
setuser 'dbo'
go
CREATE TABLE dbo.jdbc_function_escapes (
escape_name varchar(40) NOT NULL,
map_string varchar(40) NOT NULL
)
go
/* Add permissions for table... */
IF OBJECT_ID('dbo.jdbc_function_escapes') IS NOT NULL
BEGIN
GRANT SELECT ON dbo.jdbc_function_escapes TO public
END
go
/* Bind rules & defaults to columns... */
/* End of description of table dbo.jdbc_function_escapes */
/* Start of description of table dbo.spt_jdbc_conversion */
setuser 'dbo'
go
CREATE TABLE dbo.spt_jdbc_conversion (
datatype int NOT NULL,
conversion char(20) NOT NULL
)
go
/* Add permissions for table... */
IF OBJECT_ID('dbo.spt_jdbc_conversion') IS NOT NULL
BEGIN
GRANT SELECT ON dbo.spt_jdbc_conversion TO public
END
go
/* Bind rules & defaults to columns... */
/* End of description of table dbo.spt_jdbc_conversion */
/* Start of description of table dbo.spt_jdbc_table_types */
setuser 'dbo'
go
CREATE TABLE dbo.spt_jdbc_table_types (
TABLE_TYPE char(15) NOT NULL
)
go
/* Add permissions for table... */
IF OBJECT_ID('dbo.spt_jdbc_table_types') IS NOT NULL
BEGIN
GRANT SELECT ON dbo.spt_jdbc_table_types TO public
END
go
/* Bind rules & defaults to columns... */
/* End of description of table dbo.spt_jdbc_table_types */
/* Start of description of table dbo.spt_jtext */
setuser 'dbo'
go
CREATE TABLE dbo.spt_jtext (
mdinfo varchar(30) NOT NULL,
value text NOT NULL,
UNIQUE (mdinfo)
)
go
/* Add permissions for table... */
IF OBJECT_ID('dbo.spt_jtext') IS NOT NULL
BEGIN
GRANT SELECT ON dbo.spt_jtext TO public
END
go
/* Bind rules & defaults to columns... */
/* End of description of table dbo.spt_jtext */
/* Start of description of table dbo.spt_limit_types */
setuser 'dbo'
go
CREATE TABLE dbo.spt_limit_types (
name char(30) NOT NULL,
id smallint NOT NULL,
enforced tinyint NOT NULL,
object_type smallint NOT NULL,
scope smallint NOT NULL,
units char(60) NOT NULL
)
go
/* Add permissions for table... */
IF OBJECT_ID('dbo.spt_limit_types') IS NOT NULL
BEGIN
GRANT SELECT ON dbo.spt_limit_types TO public
END
go
/* Bind rules & defaults to columns... */
/* End of description of table dbo.spt_limit_types */
/* Start of description of table dbo.spt_mda */
setuser 'dbo'
go
CREATE TABLE dbo.spt_mda (
mdinfo varchar(30) NOT NULL,
querytype tinyint NOT NULL,
query varchar(255) NULL,
mdaver_start tinyint NOT NULL,
mdaver_end tinyint NOT NULL,
srvver_start int NOT NULL,
srvver_end int NOT NULL
)
go
IF OBJECT_ID('dbo.spt_mda') IS NOT NULL
BEGIN
CREATE UNIQUE NONCLUSTERED INDEX spt_mda_ind
ON spt_mda (mdinfo, mdaver_end, srvver_end)
END
go
/* Add permissions for table... */
IF OBJECT_ID('dbo.spt_mda') IS NOT NULL
BEGIN
GRANT SELECT ON dbo.spt_mda TO public
END
go
/* Bind rules & defaults to columns... */
/* End of description of table dbo.spt_mda */
/* Start of description of table dbo.spt_monitor */
setuser 'dbo'
go
CREATE TABLE dbo.spt_monitor (
lastrun datetime NOT NULL,
cpu_busy int NOT NULL,
io_busy int NOT NULL,
idle int NOT NULL,
pack_received int NOT NULL,
pack_sent int NOT NULL,
connections int NOT NULL,
pack_errors int NOT NULL,
total_read int NOT NULL,
total_write int NOT NULL,
total_errors int NOT NULL
)
ON system
go
/* Add permissions for table... */
IF OBJECT_ID('dbo.spt_monitor') IS NOT NULL
BEGIN
GRANT SELECT ON dbo.spt_monitor TO public
END
go
/* Bind rules & defaults to columns... */
/* End of description of table dbo.spt_monitor */
/* Start of description of table dbo.syblicenseslog */
setuser 'dbo'
go
CREATE TABLE dbo.syblicenseslog (
status smallint NOT NULL,
logdate datetime NOT NULL,
maxlicenses int NOT NULL
)
go
/* Add permissions for table... */
/* Bind rules & defaults to columns... */
/* End of description of table dbo.syblicenseslog */
/* Now create the key definitions ...*/
setuser 'dbo'
go
/* Views... */
/* No views found. */
/* Procedures... */
/* Procedure sp_configure, owner dbo */
setuser 'dbo'
go
/* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
/* 4.8 1.1 06/14/90 sproc/src/configure */
/*
** Messages for "sp_configure" 17410
** Must use "langid" when referencing spt_values
**
** 17260, "Can't run %1! from within a transaction."
** 17410, "Configuration option doesn't exist."
** 17411, "Configuration option is not unique."
** 17413, "The value of the 'number of devices' must be greater than the highest VDEVNO, '%1!', defined in sysdevices."
** 17414, "You can't set the default language to a language ID that is not defined in Syslanguages."
** 17415, "Configuration option value is not legal."
** 17418, "'%1!' is an invalid file command. The valid commands are 'verify', 'read', 'write', and 'restore'."
** 17419, "Configuration option changed. The SQL Server need not be rebooted since the option is dynamic.
** 18123, "Configuration option changed. The SQL Server must be rebooted before the change in effect since the option is static."
** 18124, "No matching configuration options. Here is a listing of groups:"
** 18125, "Must provide the parameter 'filename'."
** 18133, "The character set, '%1!', is invalid since it is not defined in Syscharsets."
** 18134, "The sortorder, '%1!', is invalid since it is not defined in Syscharsets."
** 18549, "Invalid third argument supplied: '%1!'. Valid choices are
** 'with truncate' or 'default'."
*/
create procedure sp_configure
@configname varchar(80) = NULL, /* configure option name */
@configvalue int = NULL, /* configure value */
@configvalue2 varchar(255) = NULL, /* config file command/charset info */
@configvalue3 varchar(255) = NULL /* physical name of file */
as
declare @confignum int /* number of option to be configured */
declare @configcount int /* number of options like @configname */
declare @whichone int /* using english or default lang ? */
declare @cmd smallint /* configuration file command */
declare @status int /* return status for misc calls */
declare @children int /* number of children in a group */
declare @parent int /* config number of parent group */
declare @msg varchar(255) /* temp buffer for messages */
declare @sysconfig smallint /* contents of sysconfigures.config */
declare @sysname varchar(255) /* contents of sysconfigures.comment */
declare @sysparent smallint /* contents of sysconfigures.parent */
declare @sysstatus smallint /* contents of sysconfigures.status */
declare @value int /* default charset/sort order id */
declare @user_displaylevel int /* user display level */
declare @maxvdevno int /* highest number of vdevno */
declare @sortorder_id int /* current sortorder id */
declare @charset_id int /* current charset id */
declare @use_wildcard tinyint /* use wildcard to search option name or not */
declare @match_count int /* number of option found by name match */
declare @cache_part_temp int /* cache partition number */
declare @partition_number int /* cache partition number */
declare @cmpstate int /* Local NODE state in companionship */
declare @nocase tinyint /* case-sensitive sort order flag */
select @whichone = 0
select @status = 0
select @cmd = 1
select @value = NULL
select @user_displaylevel = NULL
select @sortorder_id =
value from master.dbo.syscurconfigs where config = 123
select @charset_id =
value from master.dbo.syscurconfigs where config = 131
select @use_wildcard = 1
/*
** Check if the default sort order is case-insensitive.
*/
if ("A" = "a")
select @nocase = 1
else
select @nocase = 0
/*
** Disallow running sp_configure within a transaction since it might make
** recovery impossible.
*/
if @@trancount > 0
begin
/*
** 17260, "Can't run %1! from within a transaction."
*/
raiserror 17260, "sp_configure"
return (1)
end
else
begin
set chained off
end
set transaction isolation level 1
set nocount on
/*
** If the "default sortorder" is case insensitive dictionary sort order,
** the procedure will just print out all the options and their values
** without grouping if no option name is given.
*/
if (@nocase = 1 and @configname is NULL)
begin
select "Parameter Name" = convert(char(30), name),
"Default" = convert(char(11), space(11-char_length(
convert(varchar(11), defvalue)))+
convert(varchar(11), defvalue)),
"Memory Used" = convert(char(11), space(11-char_length(
convert(varchar(11), b.comment)))+
convert(varchar(11), b.comment)),
"Config Value" =convert(char(11), space(11-char_length(
isnull(a.value2, convert(char(32), a.value)))) +
isnull(a.value2, convert(char(32), a.value))),
"Run Value" = convert(char(11), space(11-char_length(
isnull(b.value2, convert(char(32), b.value)))) +
isnull(b.value2, convert(char(32), b.value)))
from master.dbo.sysconfigures a,
master.dbo.syscurconfigs b
where
a.config *= b.config
and parent != 19
and a.config != 19
order by name
return (0)
end
/* Validate the configname if it not NULL */
if @configname is not NULL
begin
select @configcount = count(*)
from master.dbo.sysconfigures
where name like "%" + @configname + "%"
and parent != 19
/*
** If configure option is not unique and case-insensitive
** dictionary sort order is used, check if unique option found
** by exact name match, if so, then disable wildcard match
** for searching option name.
*/
if (@configcount > 1 and @nocase = 1)
begin
/* check if unique option found by exact name match */
select @match_count = count(*)
from master.dbo.sysconfigures
where name = @configname
and parent != 19
if @match_count =1
begin
select @use_wildcard = 0 /* don't use wildcard */
select @configcount = @match_count
end
end
/*
** If more than one option like @configname,
** show the duplicates and return.
*/
if @configcount > 1
begin
/*
** 17411, "Configuration option is not unique."
*/
raiserror 17411
print ""
select "Parameter Name" = convert(char(30), name),
"Default" = convert(char(11), space(11-char_length(
convert(varchar(11), defvalue)))+
convert(varchar(11), defvalue)),
"Memory Used" = convert(char(11), space(11-char_length(
convert(varchar(11), b.comment)))+
convert(varchar(11), b.comment)),
"Config Value" =convert(char(11), space(11-char_length(
isnull(a.value2, convert(char(32), a.value)))) +
isnull(a.value2, convert(char(32), a.value))),
"Run Value" = convert(char(11), space(11-char_length(
isnull(b.value2, convert(char(32), b.value)))) +
isnull(b.value2, convert(char(32), b.value)))
from master.dbo.sysconfigures a,
master.dbo.syscurconfigs b
where
a.config *= b.config
and name like "%" + @configname + "%"
and parent != 19
and a.config != 19
order by name
return (1)
end
/*
** if it is a valid option and the @configvalue is not NULL,
** set the option
*/
if (@configcount != 0) and (@configvalue is not NULL)
begin
/* set @confignum */
select @confignum = config,
@sysstatus = status
from master.dbo.sysconfigures
where name like "%" + @configname + "%"
and parent != 19
and config != 19
/*
** If @configvalue2 is "default",
** setting the value to default
*/
if (@configvalue2 = "default")
select @value = 1
else
select @value = 0
/*
** If the option name is "configuration file"
*/
if @confignum = 114
begin
/*
** if the file command is one of the valid
** commands.
*/
if ((@configvalue2 = "read") or
(@configvalue2 = "write") or
(@configvalue2 = "restore") or
(@configvalue2 = "verify"))
begin
/*
** if filename is NULL
*/
if (@configvalue3 is NULL)
begin
/* 18125, "Must provide the parameter 'filename'." */
raiserror 18125
return(1)
end
/*
** Must have sa_role to run these
** commands
*/
if (proc_role("sa_role") < 1)
begin
return(1)
end
else
begin
if (@configvalue2 = "verify")
select @cmd = 2
else
if (@configvalue2 = "read")
select @cmd = 3
else
if (@configvalue2 = "write")
select @cmd = 4
else
if (@configvalue2 = "restore")
select @cmd = 5
end
end
else
begin
/*
** print the message to show the valid
** file command
*/
raiserror 17418, @configvalue2
return(1)
end
select @status = config_admin(@cmd,0,0,0,NULL,
@configvalue3)
if (@status = 1)
begin
return(0)
end
else
begin
return (1)
end
end
if @confignum = 123
begin
/* get current default charset id */
select @value = value from
master.dbo.sysconfigures
where config = 131
if @configvalue2 is not NULL
begin
/* validate the charset id */
if not exists (select *
from master..syscharsets
where name = @configvalue2
and type between 1000 and 1999)
begin
/* 18133, "The character set, '%1!', is invalid since it
** is not defined in Syscharsets."
*/
raiserror 18133, @configvalue2
return (1)
end
/* get default charset id from name */
select @value = id
from master..syscharsets
where name = @configvalue2
and type between 1000 and 1999
end
end
if @confignum = 131
begin
/* get current default sortord id */
select @value = value from
master.dbo.sysconfigures
where config = 123
if @configvalue2 is not NULL
begin
/* validate the sortord id */
if not exists (select *
from master..syscharsets
where name = @configvalue2
and type between 2000 and 2999)
begin
/* 18134, "The sortorder, '%1!', is invalid since it
** is not defined in Syscharsets."
*/
raiserror 18134, @configvalue2
return (1)
end
/* get default sortorder id from name */
select @value = id
from master..syscharsets
where name = @configvalue2
and type between 2000 and 2999
end
end
/*
** If an attempt to enable a disk mirroring is made, and
** if this happens to be a server with HA services turned
** on, we disallow. Currently we do not support ASE HA
** services along with sybase mirroring.
*/
if (@confignum = 140 and @configvalue = 0)
begin
select @cmpstate = @@cmpstate
if @cmpstate >= 0
begin
/* 18816 Mirroring not allowed in ASE HA */
raiserror 18816
return(1)
end
end
/*
** If an attempt to disable disk mirroring is being made,
** ensure that there are no devices that are currently
** being mirrored.
*/
if (@confignum = 140 and @configvalue = 1)
begin
if (select count(*) from master.dbo.sysdevices
where status & 512 = 512) > 0
begin
/* 18750, Unable to disable disk mirroring
** because some devices are currently
** mirrored. Use 'disk unmirror' to
** unmirror these devices and then
** re-run this sp_configure command.
*/
raiserror 18570
return (1)
end
end
/*
** If this is the number of devices configuration
** parameter, we want to make sure that it's not being
** set to lower than the
** number of devices in sysdevices.
*/
if @confignum = 116
begin
/*
** Get the default value if trying to set the
** value to the default value
*/
if (@value = 1)
begin
select @configvalue = convert(int, defvalue)
from master.dbo.syscurconfigs
where config = 116
end
/*
** Get the max vdevno.
*/
select @maxvdevno = max(
convert(tinyint,
substring(convert(binary(4), d.low),
v.low, 1)))
from master.dbo.sysdevices d,
master.dbo.spt_values v
if (@configvalue <= @maxvdevno)
begin
/* 17413, "The value of the 'number of devices' must be
** greater than the highest VDEVNO, '%1!', defined
** in sysdevices."
*/
raiserror 17413, @maxvdevno
return (1)
end
end
/*
** If this is the number of default language, we want
** to make sure that the new value is a valid language
** id in Syslanguages.
*/
if @confignum = 124
begin
if not exists (select *
from master.dbo.syslanguages
where langid = @configvalue)
begin
/* 0 is default language, us_english */
if @configvalue != 0
begin
/* 17414, "You can't set the default language to a
** language ID that is not defined in Syslanguages."
*/
raiserror 17414
return (1)
end
end
end
/*
** If this is the number of current audit table we want
** to make sure that if "with truncate" opiton is not
** provided new table is empty other wise fail.
*/
if @confignum = 260
begin
if @configvalue2 is not NULL
begin
if (@configvalue2 not in ("with truncate",
"default"))
begin
/*
** 18549, "Invalid third argument
** supplied: '%1!'. Valid
** choices are 'with truncate'
** or 'default'."
*/
raiserror 18549, @configvalue2
return(1)
end
end
else
begin
select @value = 2
end
end
if @confignum = 337
begin
if @configvalue is not NULL
begin
if (@configvalue2 = "default")
begin
select @partition_number = 1
end
else
begin
select @partition_number =
@configvalue
end
if (@partition_number <= 0) OR
(@partition_number > 64)
begin
raiserror 18611
return(1)
end
select @cache_part_temp = 2
while @cache_part_temp < @partition_number
select @cache_part_temp =
@cache_part_temp * 2
if @partition_number != 1 AND
@cache_part_temp != @partition_number
begin
raiserror 18611
return(1)
end
end
end
/* call config_admin() to set the new value */
select @status = config_admin(@cmd, @confignum,
@configvalue, @value, NULL, @configvalue2)
/* if successful */
if (@status = 1)
begin
/* Display the new value */
select "Parameter Name" = convert(char(30), name),
"Default" = convert(char(11), space(11-char_length(
convert(varchar(11), defvalue))) +
convert(varchar(11), defvalue)),
"Memory Used" = convert(char(11),space(11-char_length(
convert(varchar(11), c.comment))) +
convert(varchar(11), c.comment)),
"Config Value" = convert(char(11),
space(11-char_length(
isnull(b.value2, convert(char(32), b.value)))) +
isnull(b.value2, convert(char(32), b.value))),
"Run Value" = convert(char(11), space(11-char_length(
isnull(c.value2, convert(char(32), c.value)))) +
isnull(c.value2, convert(char(32), c.value)))
from master.dbo.sysconfigures b,
master.dbo.syscurconfigs c
where
b.config = @confignum and
b.config *= c.config
/*
** print reboot message if this option is not
** dynamic.
*/
select @sysstatus = @sysstatus & 8
if @sysstatus = 8
begin
exec sp_getmessage 17419, @msg output
print @msg
end
else
begin
exec sp_getmessage 18123, @msg output
print @msg
end
return(0)
end
else
return(1)
end
end
/*
** @configcount=0 implies @configname is not valid
** @configname=NULL implies displaying all the parameters except for
** the parameters with the config number equal to 19 or the parent equal
** to 19 since those parameters are displayed by sp_cacheconfig.
*/
if (@configcount =0)
begin
/* 18124, "No matching configuration options.
** Here is a listing of groups:"
*/
raiserror 18124
select convert(char(50), name)
from master.dbo.sysconfigures
where config < 100
and parent != 19
and config != 19
order by name
return(1)
end
else if (@configname is NULL)
select @configname = "Config"
/*
** retrieve the display level from sysattributes
*/
select @user_displaylevel = int_value from master.dbo.sysattributes where
class = 4 AND
attribute = 0 AND
object_type = 'L' AND
object = suser_id()
/*
** set the default display level to 10 if it is not defined in sysattributes
*/
if (@user_displaylevel = NULL)
select @user_displaylevel = 10
/*
** If @use_wildcard = 0 and the default sortorder is case-insensitive
** dictionary sort order, use exact match: name = @configname to get row,
** otherwise use wildcard match: name like "%" + @configname + "%".
*/
if (@use_wildcard = 0 and @nocase = 1)
begin
select @confignum = config,
@parent = config,
@sysname = name,
@sysstatus = status
from master.dbo.sysconfigures
where name = @configname
and config != 19
end
else
begin
select @confignum = config,
@parent = config,
@sysname = name,
@sysstatus = status
from master.dbo.sysconfigures
where name like "%" + @configname + "%"
and config != 19
end
select @children = count(*)
from master.dbo.sysconfigures
where parent = @confignum
if @children = 0
begin
/* @@nestlevel is problem area if a sproc calls sp_configure */
/* could pass in another param when recursing */
if @@nestlevel > 1
begin
/* reached a leaf, notify parent */
return(1)
end
else
begin
/* display the information of the config parameter */
select "Parameter Name" = convert(char(30), name),
"Default" = convert(char(11), space(11-char_length(
convert(varchar(11), defvalue))) +
convert(varchar(11), defvalue)),
"Memory Used" = convert(char(11), space(11-char_length(
convert(varchar(11), c.comment))) +
convert(varchar(11), c.comment)),
"Config Value" = convert(char(11), space(11-char_length(
isnull(b.value2, convert(char(32), b.value)))) +
isnull(b.value2, convert(char(32), b.value))),
"Run Value" = convert(char(11), space(11-char_length(
isnull(c.value2, convert(char(32), c.value)))) +
isnull(c.value2, convert(char(32), c.value)))
from master.dbo.sysconfigures b,
master.dbo.syscurconfigs c
where
b.config *= c.config
and name like "%" + @configname + "%"
and b.config != 19
and parent != 19
end
end
else
begin
select @msg = "Group: " + @sysname
print ""
print @msg
print ""
/* this poor guy has kids, so recurse to leaves */
declare config_curs cursor for
select config, name, parent
from master.dbo.sysconfigures
where parent = @parent
order by name
open config_curs
fetch config_curs into @sysconfig, @sysname, @sysparent
while (@@sqlstatus = 0)
begin
execute @status = sp_configure @sysname
if (@status = 1)
begin
/*
** this guy has leaves as kids,
** so print out the leaves with
** display level <= @user_displaylevel
** Note: If a config parameter has more than one
** parent, the extra parents are stored in
** 'sysattribures'.
*/
create table #configure_temp (config int)
insert into #configure_temp
select a.config
from master.dbo.sysconfigures a,
master.dbo.syscurconfigs b
where
display_level <= @user_displaylevel
and parent = @parent
and a.config != 19
and a.config = b.config
union
select config
from master.dbo.syscurconfigs,
master.dbo.sysattributes
where
display_level <= @user_displaylevel
and class = 4
and attribute = 1
and object_type = 'CP'
and int_value = @parent
and object = config
and config != 19
if exists (select * from #configure_temp)
begin
select "Parameter Name" = convert(char(30), name),
"Default" = convert(char(11), space(11-char_length(
convert(varchar(11), defvalue))) +
convert(varchar(11), defvalue)),
"Memory Used" = convert(char(11), space(11-char_length(
convert(varchar(11), c.comment))) +
convert(varchar(11), c.comment)),
"Config Value" = convert(char(11),space(11-char_length(
isnull(b.value2, convert(char(32), b.value)))) +
isnull(b.value2, convert(char(32), b.value))),
"Run Value" = convert(char(11), space(11-char_length(
isnull(c.value2, convert(char(32), c.value)))) +
isnull(c.value2, convert(char(32), c.value)))
from master.dbo.sysconfigures b,
master.dbo.syscurconfigs c
where b.config in
(select config from #configure_temp)
and b.config = c.config
order by name
end
drop table #configure_temp
close config_curs
deallocate cursor config_curs
return(0)
end
else
begin
/*
** this lucky guy has grandkids, so, continue
*/
fetch config_curs into
@sysconfig, @sysname, @sysparent
end
end
close config_curs
deallocate cursor config_curs
return(0)
end
go
IF OBJECT_ID('dbo.sp_configure') IS NOT NULL
BEGIN
GRANT EXECUTE ON dbo.sp_configure TO public
END
go
/* Procedure sp_dboption, owner dbo */
setuser 'dbo'
go
/* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
/* 4.8 1.1 06/14/90 sproc/src/a_values */
/*
** Messages for "sp_dboption" 17420
** Use "langid" when looking at spt_values ???
**
** 17260, "Can't run %1! from within a transaction."
** 17420, "Settable database options."
** 17421, "No such database -- run sp_helpdb to list databases."
** 17422, "The 'master' database's options can not be changed."
** 17423, "Usage: sp_dboption [dbname, optname, {true | false}]"
** 17424, "Database option doesn't exist or can't be set by user."
** 17425, "Run sp_dboption with no parameters to see options."
** 17426, "Database option is not unique."
** 17428, "You must be in the 'master' database in order to change
** database options."
** 17429, "The database is currently in use -- 'read only' option
** disallowed."
** 17430, "Run the CHECKPOINT command in the database that was changed."
** 17431, "true"
** 17432, "false"
** 17433, "Database option '%1!' turned ON for database '%2!'."
** 17434, "Database option '%1!' turned OFF for database '%2!'."
** 17289, "Set your curwrite to the hurdle of current database."
** 17436, "The 'single user' option is not valid for the 'tempdb'
** database."
** 17439, "You cannot turn on ''%1!' for '%2!' because it is an HA server
** that has been configured with the proxy_db option."
*/
create procedure sp_dboption
@dbname varchar(30) = NULL, /* database name to change */
@optname varchar(20) = NULL, /* option name to turn on/off */
@optvalue varchar(10) = NULL /* true or false */
as
declare @dbid int /* dbid of the database */
declare @dbuid int /* id of the owner of the database */
declare @statvalue smallint, /* number of option */
@stattype char(2), /* status field flag */
@statopt smallint, /* option mask, part 1 */
@stat2opt smallint /* option mask, part 2 */
declare @optcount int /* number of options like @optname */
declare @success_msg varchar(255) /* success status message */
declare @msg varchar(250)
declare @sptlang int
declare @true varchar(10)
declare @false varchar(10)
declare @whichone int /* which language? */
declare @name varchar(30)
declare @optmsgnum int /* identify one msgnum to compare */
declare @msgcnt int /* count distinct dups */
if @@trancount = 0
begin
set chained off
end
set transaction isolation level 1
select @sptlang = @@langid, @whichone = 0
if @@langid != 0
begin
if not exists (
select * from master.dbo.sysmessages where error
between 17050 and 17069
and langid = @@langid)
select @sptlang = 0
end
/*
** If no @dbname given, just list the possible dboptions.
** Only certain status bits may be set or cleared.
** settable not settable
** ------------------------------ --------------------------
** allow select into/bulkcopy (4) don't recover (32)
** read only (1024) not recovered (256)
** dbo use only (2048) dbname has changed (16384)
** single user (4096)
** truncate log on checkpoint (8)
** no checkpoint on recovery (16)
** allow null (8192)
** ddl in tran (512)
** ALL SETTABLE OPTIONS (15900)
** abort xact on log full (1, type='D2')
** no space accounting (2, type='D2')
** auto identity(4, type='D2')
** identity in nonunique index(8, type='D2')
** auto identity unique index(64, type='D2')
*/
/*
** Look for the "settable options" mask in spt_values
*/
select @statopt = number
from master.dbo.spt_values
where type = "D"
and name = "ALL SETTABLE OPTIONS"
select @stat2opt = number
from master.dbo.spt_values
where type = "D2"
and name = "ALL SETTABLE OPTIONS"
/*
** If we can't find the option masks, guess at them
*/
if @statopt is null
select @statopt = 4 | 8 | 16 | 512 | 1024 | 2048 | 4096 | 8192
if @stat2opt is null
select @stat2opt = 1 | 2 | 4 | 8 | 64
if @dbname is null
begin
/*
** 17420, "Settable database options."
*/
exec sp_getmessage 17420, @msg output
print @msg
if @sptlang = 0
select database_options = name
from master.dbo.spt_values
where ((type = "D"
and number & @statopt = number
and number & @statopt != @statopt)
or (type = "D2"
and number & @stat2opt = number
and number & @stat2opt != @stat2opt))
order by name
else
select database_options = name, convert(char(22), description)
from master.dbo.spt_values, master.dbo.sysmessages
where ((type = "D"
and number & @statopt = number
and number & @statopt != @statopt)
or (type = "D2"
and number & @stat2opt = number
and number & @stat2opt != @stat2opt))
and msgnum = error
and langid = @sptlang
order by name
return (0)
end
/*
** Verify the database name and get the @dbid and @dbuid
*/
select @dbid = dbid, @dbuid = suid
from master.dbo.sysdatabases
where name = @dbname
/*
** If @dbname not found, say so and list the databases.
*/
if @dbid is NULL
begin
/*
** 17421, "No such database -- run sp_helpdb to list databases."
*/
raiserror 17421
return (1)
end
/*
** Only the Database Owner (DBO) or
** Accounts with SA role can execute it.
** Call proc_role() with the required SA role.
*/
if ((suser_id() != @dbuid) and (proc_role("sa_role") < 1))
return(1)
/*
** You can not change any of the options in master. If the user tries to
** do so tell them they can't.
*/
if @dbid = 1
begin
/*
** 17422, "The 'master' database's options can not be changed."
*/
raiserror 17422
return (1)
end
/*
** Check remaining parameters.
*/
/* 17431, "true" */
exec sp_getmessage 17431, @true out
/* 17432, "false" */
exec sp_getmessage 17432, @false out
if @optname is NULL or lower(@optvalue) not in
("true", "false", @true, @false) or @optvalue is null
begin
/*
** 17423, "Usage: sp_dboption [dbname, optname, {true | false}]"
*/
raiserror 17423
return (1)
end
/*
** Use @optname and try to find the right option.
** If there isn't just one, print appropriate diagnostics and return.
*/
select @optcount = count(*)
from master.dbo.spt_values
where name like "%" + @optname + "%"
and ((type = "D"
and number & @statopt = number)
or (type = "D2"
and number & @stat2opt = number))
/*
** If more than one option like @optname, make sure they are not the same
** option ("trunc" and "trunc.", for example)
*/
if @optcount > 1
begin
select @optmsgnum = msgnum
from master.dbo.spt_values
where name like "%" + @optname + "%"
and ((type = "D"
and number & @statopt = number)
or (type = "D2"
and number & @stat2opt = number))
select @msgcnt = count(msgnum)
from master.dbo.spt_values
where name like "%" + @optname + "%"
and ((type = "D"
and number & @statopt = number)
or (type = "D2"
and number & @stat2opt = number))
and msgnum != @optmsgnum
/*
** msgcnt of 0 indicates we really have just 1 unique dboption,
** probably due to alternate spelling.
*/
if (@msgcnt = 0)
select @optcount = 1
end
/*
** If no option, and alternate language is set, use other language
*/
if @optcount = 0 and @sptlang != 0
begin
select @optcount = count(*)
from master.dbo.spt_values, master.dbo.sysmessages
where description like "%" + @optname + "%"
and ((type = "D"
and number & @statopt = number)
or (type = "D2"
and number & @stat2opt = number))
and msgnum = error
and langid = @sptlang
select @whichone = 1
/*
** If more than one option like @optname, make sure they are not the same
** option ("trunc" and "trunc.", for example)
*/
if @optcount > 1
begin
select @optmsgnum = msgnum
from master.dbo.spt_values, master.dbo.sysmessages
where description like "%" + @optname + "%"
and ((type = "D"
and number & @statopt = number)
or (type = "D2"
and number & @stat2opt = number))
and msgnum = error
and langid = @sptlang
select @msgcnt = count(msgnum)
from master.dbo.spt_values, master.dbo.sysmessages
where description like "%" + @optname + "%"
and ((type = "D"
and number & @statopt = number)
or (type = "D2"
and number & @stat2opt = number))
and msgnum = error
and langid = @sptlang
and msgnum != @optmsgnum
/*
** msgcnt of 0 indicates we really have just 1 unique dboption,
** probably due to alternate spelling.
*/
if (@msgcnt = 0)
select @optcount = 1
end
end
/*
** If no option, show the user what the options are.
*/
if @optcount = 0
begin
/*
** 17424, "Database option doesn't exist or can't be set by user."
*/
raiserror 17424
/*
** 17425, "Run sp_dboption with no parameters to see options."
*/
exec sp_getmessage 17425, @msg output
print @msg
return (1)
end
/*
** If more than one option like @optname, show the duplicates and return.
*/
if @optcount > 1
begin
/*
** 17426, "Database option is not unique."
*/
raiserror 17426
if @sptlang = 0
select duplicate_options = name
from master.dbo.spt_values
where name like "%" + @optname + "%"
and ((type = "D"
and number & @statopt = number)
or (type = "D2"
and number & @stat2opt = number))
else
select duplicate_options = name, convert(char(22), description)
from master.dbo.spt_values, master.dbo.sysmessages
where (name like "%" + @optname + "%"
or description like "%" + @optname + "%")
and ((type = "D"
and number & @statopt = number)
or (type = "D2"
and number & @stat2opt = number))
and msgnum = error
and langid = @sptlang
return (1)
end
if db_name() != "master"
begin
/*
** 17428, "You must be in the 'master' database in order to change database options."
*/
raiserror 17428
return (1)
end
/*
** User cannot set "tempdb" database in single user mode.
*/
select @statvalue = number
from master.dbo.spt_values
where name like "%" + @optname + "%"
and ((type = "D"
and number & @statopt = number)
or (type = "D2"
and number & @stat2opt = number))
if (@dbid = 2) and (@statvalue = 4096)
begin
/*
** 17436, "The 'single user' option is not valid for the 'tempdb'
** database."
*/
raiserror 17436
return (1)
end
/*
** If we're in a transaction, disallow this since it might make recovery
** impossible.
*/
if @@trancount > 0
begin
/*
** 17260, "Can't run %1! from within a transaction."
*/
raiserror 17260, "sp_dboption"
return (1)
end
else
begin
set chained off
end
set transaction isolation level 1
/*
** Get the number which is the bit value to set
*/
if @whichone = 0
select @statvalue = number, @stattype = type, @success_msg = name
from master.dbo.spt_values
where name like "%" + @optname + "%"
and ((type = "D"
and number & @statopt = number)
or (type = "D2"
and number & @stat2opt = number))
else
select @statvalue = number, @stattype = type, @success_msg = name
from master.dbo.spt_values, master.dbo.sysmessages
where description like "%" + @optname + "%"
and ((type = "D"
and number & @statopt = number)
or (type = "D2"
and number & @stat2opt = number))
and msgnum = error
and langid = @sptlang
/*
** We do not allow 'sybsecurity' to be set to 'single user' since,
** if auditing is enabled and we try to set 'sybsecurity' database to
** 'single user' then, the audit process is killed because audit process
** tries to do 'usedb' and it fails (look at utils/auditing.c).
*/
if (@dbname = "sybsecurity") and (@statvalue = 4096)
begin
/*
** 17435, "The 'single user' option is not valid for the
** 'sybsecurity' database.
*/
raiserror 17435
return (1)
end
/*
** Now update sysdatabases.
*/
if lower(@optvalue) in ("true", @true)
begin
/*
** If this the option to make the database read only,
** we need to do some checking first.
** Unless it's the master db, no one can be using it.
** If it's the master db, only the SA may be using it.
*/
if (@statvalue = 1024) and (select count(*)
from master.dbo.sysprocesses
where dbid = @dbid) > 0
begin
/*
** 17429, "The database is currently in use -- 'read only' option disallowed."
*/
raiserror 17429
return (1)
end
/*
** If this is the option to set 'abort tran on log full' to
** true for sybsecurit database, then don't allow.
*/
if (db_name(@dbid) = "sybsecurity"
and @stattype = "D2" and @statvalue = 1)
begin
/*
** AUDIT_CHANGE: New error message needs to be reserved and
** the print statement needs to be removed.
*/
print "You cannot set 'abort tran on log full' to true for sybsecurity database."
return (1)
end
/*
** Disallow DDL IN TRAN
** if proxydb option is set (@@crthaproxy = 1)
** if this server is a HA server (@@cmpstate >= 0)
*/
if ((@statvalue = 512) and (@@crthaproxy = 1) and (@@cmpstate >= 0))
begin
/*
** Cannot set DDL_IN_TRAN option for HA servers
** configured with proxy_db option.
*/
select @name = db_name(@dbid)
exec sp_getmessage 17439, @msg output
print @msg, @success_msg, @name
return (1)
end
if (@stattype = "D")
update master.dbo.sysdatabases
set status = status | @statvalue
where dbid = @dbid
else
update master.dbo.sysdatabases
set status2 = status2 | @statvalue
where dbid = @dbid
/*
** 17433, "Database option %1! turned ON for database %2!."
*/
exec sp_getmessage 17433, @msg output
select @name = db_name(@dbid)
print @msg, @success_msg, @name
end
/*
** We want to turn it off.
*/
else
begin
if (@stattype = "D")
update master.dbo.sysdatabases
set status = status & ~@statvalue
where dbid = @dbid
else
update master.dbo.sysdatabases
set status2 = status2 & ~@statvalue
where dbid = @dbid
/*
** 17434, "Database option %1! turned OFF for database %2!."
*/
exec sp_getmessage 17434, @msg output
select @name = db_name(@dbid)
print @msg, @success_msg, @name
end
/*
** Advise the user to run the CHECKPOINT command in the database that
** was changed.
*/
/*
** 17430, "Run the CHECKPOINT command in the database that was changed."
*/
exec sp_getmessage 17430, @msg output
print @msg
return (0)
go
IF OBJECT_ID('dbo.sp_dboption') IS NOT NULL
BEGIN
GRANT EXECUTE ON dbo.sp_dboption TO public
END
go
/* Procedure sp_dbupgrade, owner dbo */
setuser 'dbo'
go
/* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
create procedure sp_dbupgrade
as
if @@trancount = 0
begin
set chained off
end
set transaction isolation level 1
/*
** Do the sysindexes column names update which is part of the 38 upgrade.
** The sysgams updates have already been taken care of by pg_gamalloc.
*/
print "Upgrading Sysindexes columns in the database"
if not exists (select name from syscolumns where id = 2 and name = 'doampg')
begin
update syscolumns
set name = 'doampg'
where id = 2 and name = 'dpages'
update syscolumns
set name = 'ioampg'
where id = 2 and name = 'reserved'
update syscolumns
set name = 'spare1'
where id = 2 and name = 'used'
update syscolumns
set name = 'spare2'
where id = 2 and name = 'rows'
end
/* The following updates are part of the 42 upgrade. */
/*
** Tweak the Syscolumns entries for the Sysindexes table so that
** "soid" and "csid" replace half of "usagecnt".
*/
if not exists (select * from syscolumns where id = 2 and name = 'soid')
begin
begin transaction
update syscolumns
set type = 52, length = 2, usertype = 6, offset = 42
where id = 2 and colid = 13
insert into syscolumns
(id, number, colid, status, type, length, offset,
usertype, cdefault, domain, name, printfmt)
values (2, 0, 23, 0, 48, 1, 40, 5, 0, 0, 'soid', '')
insert into syscolumns
(id, number, colid, status, type, length, offset,
usertype, cdefault, domain, name, printfmt)
values (2, 0, 24, 0, 48, 1, 41, 5, 0, 0, 'csid', '')
commit transaction
end
print "Adding new datatypes to the database."
if exists (select * from systypes where name = 'text')
begin
delete systypes where name = 'text'
end
insert systypes (uid, usertype, variable, allownulls, type, length,
tdefault, domain, name, printfmt)
values (1, 19, 0, 1, 35, 16, 0, 0, 'text', null)
if exists (select * from systypes where name = 'image')
begin
delete systypes where name = 'image'
end
insert systypes (uid, usertype, variable, allownulls, type, length,
tdefault, domain, name, printfmt)
values (1, 20, 0, 1, 34, 16, 0, 0, 'image', null)
if exists (select * from systypes where name = 'timestamp')
begin
delete systypes where name = 'timestamp'
end
insert systypes (uid, usertype, variable, allownulls, type, length,
tdefault, domain, name, printfmt)
values (1, 80, 0, 1, 37, 8, 0, 0, 'timestamp', null)
if exists (select * from systypes where name = 'smallmoney')
begin
delete systypes where name = 'smallmoney'
end
insert systypes (uid, usertype, variable, allownulls, type, length,
tdefault, domain, name, printfmt)
values (1, 21, 0, 1, 122, 4, 0, 0, 'smallmoney', null)
if exists (select * from systypes where name = 'smalldatetime')
begin
delete systypes where name = 'smalldatetime'
end
insert systypes (uid, usertype, variable, allownulls, type, length,
tdefault, domain, name, printfmt)
values (1, 22, 0, 1, 58, 4, 0, 0, 'smalldatetime', null)
if exists (select * from systypes where name = 'real')
begin
delete systypes where name = 'real'
end
insert systypes (uid, usertype, variable, allownulls, type, length,
tdefault, domain, name, printfmt)
values (1, 23, 0, 1, 59, 4, 0, 0, 'real', null)
/* 4.9 user types for national character */
if exists (select * from systypes where name = 'nchar')
begin
delete systypes where name = 'nchar'
end
insert systypes (uid, usertype, variable, allownulls, type, length,
tdefault, domain, name, printfmt )
values (1, 24, 0, 1, 47, 255, 0, 0, 'nchar', null)
if exists (select * from systypes where name = 'nvarchar')
begin
delete systypes where name = 'nvarchar'
end
insert systypes (uid, usertype, variable, allownulls, type, length,
tdefault, domain, name, printfmt)
values (1, 25, 1, 1, 39, 255, 0, 0, 'nvarchar', null)
if exists (select * from systypes where name = 'NULL')
begin
delete systypes where name = 'NULL'
end
insert systypes (uid, usertype, variable, allownulls, type, length,
tdefault, domain, name, printfmt)
values (1, 0, 0, 1, 0, 0, 0, 0, 'NULL', null)
/* 4.9 system table creation */
print "Creating system catalog: sysusermessages and its indexes."
if not exists (select * from sysobjects where name='sysusermessages')
begin
execute sp_configure 'allow updates', 1
reconfigure with override
dbcc traceon(3701)
begin
create table sysusermessages(error int,uid smallint,
description varchar(255), langid smallint null) lock allpages
create clustered index csysusermessages
on sysusermessages (error)
create unique nonclustered index ncsysusermessages
on sysusermessages (error, langid)
end
dbcc traceoff(3701)
execute sp_configure 'allow updates', 0
reconfigure with override
end
print "Shutting down SQL Server"
shutdown
return (0)
go
/* Procedure sp_getmessage, owner dbo */
setuser 'dbo'
go
/* generic/sproc/getmessage 14.2 4/25/91 */
/* Messages from sysmessages
** 17200, "Message number must be greater than or equal to 17000."
** 17201, "'%1!' is not an official language name from Syslanguages."
** 17202, "Message number %1! does not exist in the %2! language."
*/
create procedure sp_getmessage
@message_num int,
@result varchar(255) output,
@language varchar(30) = NULL
as
declare @lang_id smallint
declare @msg varchar(255)
declare @returncode smallint
if @@trancount = 0
begin
set chained off
end
set transaction isolation level 1
/*
** Use default language if none specified,
** and initialize result
*/
select @lang_id = @@langid, @result = NULL
/* Only retrieve external errors */
if @message_num < 17000
BEGIN
/* 17200 "Message number must be greater than or equal to 17000." */
select @msg = description from master.dbo.sysmessages
where error = 17200
print @msg
return (1)
END
/*
** Check that language is valid.
*/
if @language is not NULL
BEGIN
execute @returncode = sp_validlang @language
if @returncode != 0
begin
/* Us_english is always valid */
if @language != "us_english"
BEGIN
/*
** 17201, "'%1!' is not an official language
** name from Syslanguages."
*/
select @msg = description from master.dbo.sysmessages
where error = 17201
and langid = @@langid
/* Get english if the current language is missing */
if @msg is null
select @msg = description from master.dbo.sysmessages
where error = 17201
and langid is NULL
print @msg, @language
return @returncode
END
/* set to us_english */
select @lang_id = NULL
end
else
select @lang_id = langid from master.dbo.syslanguages
where @language = name
END
/* The langid is assigned 0 since it gets its value from @@langid. */
/* For us_english, we have to insert it as NULL and not 0, this is */
/* to maintain compatibility with the current conventions */
if @lang_id = 0
begin
select @lang_id = NULL
end
/* Get message from the proper place */
/* System messages */
if @message_num < 20000
BEGIN
select @result = description from master.dbo.sysmessages
where langid = @lang_id
and error = @message_num
/* Get english if the current language is missing */
if @result is null
select @result = description from master.dbo.sysmessages
where error = @message_num
and (langid is NULL or langid =0)
END
else
/* User messages */
BEGIN
/* There is no proper alternate language for user messages */
select @result = description from sysusermessages
where langid = @lang_id
and error = @message_num
/* this is in here for compatibility with older revs which */
/* by mistake used to add langid as 0 in sysusermessages */
if @result is null and @lang_id is NULL
select @result = description from sysusermessages
where (langid = 0 or langid is NULL)
and error = @message_num
END
/* Warn the user if the message can't be found */
if @result is null
begin
/* 17202, "Message number %1! does not exist in the %2! language." */
select @msg = description from master.dbo.sysmessages
where error = 17202
and langid = @@langid
if @language is null
select @language = @@language
if @msg is null
select @msg = "Message number %1! does not exist in the %2! language."
print @msg, @message_num, @language
return (1)
end
return (0)
go
IF OBJECT_ID('dbo.sp_getmessage') IS NOT NULL
BEGIN
GRANT EXECUTE ON dbo.sp_getmessage TO public
END
go
/* Procedure sp_loaddbupgrade, owner dbo */
setuser 'dbo'
go
/* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
create procedure sp_loaddbupgrade
@databasename varchar(30),
@devname varchar(30)
as
if @@trancount = 0
begin
set chained off
end
set transaction isolation level 1
dbcc traceon(3402)
/* The recovery which is part of load database will perform the upgrade */
load database @databasename from @devname
dbcc traceoff(3402)
return (0)
go
/* Procedure sp_procxmode, owner dbo */
setuser 'dbo'
go
/* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
/*
** 17756, "The execution of the stored procedure '%1!' in database
** '%2!' was aborted because there was an error in writing the
** replication log record."
*/
create procedure sp_procxmode
@procname varchar(255) = null,
@tranmode varchar(30) = null
as
declare @uid smallint
declare @oid int
declare @msg varchar(250) /* message text */
declare @dbname varchar(30)
if @@trancount = 0
begin
set chained off
end
set transaction isolation level 1
/* If either parameter is null we will be joining with a temporary table
** to convert transaction mode numbers (0, 1, 2) to strings ("Unchained",
** "Chained", "Any Mode").
*/
if ((@procname is null) or (@tranmode is null))
begin
create table #tranmode (intval integer, charval varchar(15))
insert into #tranmode values(0, "Unchained")
insert into #tranmode values(1, "Chained")
insert into #tranmode values(2, "Any Mode")
end
/* If the first parameter is null, we're to report the transaction-modes
** of every stored procedure in the current database.
*/
if (@procname is null)
begin
select "procedure name" = o.name, "user name" = user_name(o.uid),
"transaction mode" = t.charval
from sysobjects o, #tranmode t
where ((o.type = "P") or (o.type = "XP")) and (t.intval = ((o.sysstat2 / 16) & 3))
order by o.name
return(0)
end
/* If only the second parameter is null, we're to report the
** transaction-mode of the specified stored procedure.
*/
if ((@procname is not null) and (@tranmode is null))
begin
if (not exists (select name from sysobjects
where ((type = "P") or (type = "XP")) and
(name = @procname)))
begin
/*
** Force an error message, since we haven't
** installed sp_getmessage yet.
*/
dbcc update_tmode(@procname, "Chained")
return (1)
end
select "procedure name" = o.name, "user name" = user_name(o.uid),
"transaction mode" = t.charval
from sysobjects o, #tranmode t
where ((o.type = "P") or (o.type = "XP")) and (@procname = o.name) and
(t.intval = ((o.sysstat2 / 16) & 3))
return(0)
end
/* If neither parameter is null, we're to set the transaction-mode
** of the specified procedure to the specified value.
*/
if ((@procname is not null) and (@tranmode is not null))
begin
/* Start the transaction to log the execution of this procedure.
**
** IMPORTANT: The name "rs_logexec is significant and is used by
** Replication Server
*/
begin transaction rs_logexec
/*
** Update transaction-mode in both sysobjects and DES.
*/
dbcc update_tmode(@procname, @tranmode)
/* If dbcc update_tmode returned an error, return
** an error now.
*/
if (@@error != 0)
begin
rollback transaction rs_logexec
return(1)
end
/*
** Write the log record to replicate this invocation
** of the stored procedure.
*/
if (logexec() != 1)
begin
/*
** 17756, "The execution of the stored procedure '%1!'
** in database '%2!' was aborted because there
** was an error in writing the replication log
** record."
*/
select @dbname = db_name()
raiserror 17756, "sp_procxmode", @dbname
rollback transaction rs_logexec
return(1)
end
commit transaction
end
go
IF OBJECT_ID('dbo.sp_procxmode') IS NOT NULL
BEGIN
GRANT EXECUTE ON dbo.sp_procxmode TO public
END
go
/* Procedure sp_prtsybsysmsgs, owner dbo */
setuser 'dbo'
go
/* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
/*
** This procedure is needed to extract messages for the batch that creates
** the sybsystemprocs database. When return parameters are used in an execute
** statement that is a part of a SQL batch, the return values are printed
** with a heading before subsequent statements in the batch are executed.
** These headings could be confusing to a user that is looking at the results
** of the batch. Hence we print the message in a stored procedure
*/
create procedure sp_prtsybsysmsgs
@i int, @size int = NULL, @size2 int = NULL
as
declare @msg varchar(250)
exec sp_getmessage @i, @msg out
print @msg, @size, @size2
go
/* Procedure sp_validlang, owner dbo */
setuser 'dbo'
go
/* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
/* 4.8 1.1 06/14/90 sproc/src/serveroption */
create procedure sp_validlang
@name varchar(30)
as
if @@trancount = 0
begin
set chained off
end
set transaction isolation level 1
/* Check to see if this language is in Syslanguages. */
if exists (select *
from master.dbo.syslanguages
where name = @name)
begin
return 0
end
return 1
go
IF OBJECT_ID('dbo.sp_validlang') IS NOT NULL
BEGIN
GRANT EXECUTE ON dbo.sp_validlang TO public
END
go
/* Triggers... */
/* No triggers found. */
/** New testing for views and procedures **/
--
-- View: vs_xdp_data
--
CREATE VIEW vs_xdp_data
AS SELECT
d.id AS device_id,
x.discoveryProtocol AS discovery_protocol,
dbo.inet_ntoa( remoteIpAddr ) AS remote_ip_address,
x.remoteSysDescr AS remote_sys_descr,
x.remoteSysName AS remote_sys_name,
x.remoteInterfaceName AS remote_interface_name,
x.remotePlatform AS remote_platform,
x.localInterfaceName AS local_interface_name,
x.class_name AS category,
e.dateSeen AS date_seen,
m.user_id AS user_access
FROM
xdp_cache x,
discovery_event e,
device d,
asset ast,
M_ACCESS_CONTROL m
WHERE
x.discoveryEvent_id = e.id AND
e.adminIpAddr = d.ipaddr AND
ast.foreign_asset_id2 = d.device_id AND
ast.acl_id = m.acl_id
GO
--
-- Procedure: Tx_B_Get_Vlan
--
create procedure Tx_B_Get_Vlan
(
@Device_Id bigint,
@User_Access int,
@Managed_Element_Id bigint
)
AS
Begin
SELECT VlanName, VlanNumber, VlanStatus
from nim_vlan V
JOIN Device D on V.fk_device = D.Id
JOIN Asset A on A.foreign_asset_id2 = D.device_id
JOIN M_Access_Control MA on MA.acl_id = A.Acl_id
where D.Id = @Device_Id and V.fk_managed_element = @Managed_Element_Id and MA.User_Id = @User_Access
return 0
end
GO
--
-- Function: inet_ntoa
--
CREATE FUNCTION [dbo].inet_ntoa
(
@ipLong BIGINT
)
RETURNS CHAR(15)
AS
BEGIN
DECLARE
@octet1 BIGINT,
@octet2 BIGINT,
@octet3 BIGINT,
@octet4 BIGINT,
@rv CHAR(15)
SELECT
@octet1 = ( @ipLong / 16777216 ) & 255,
@octet2 = ( @ipLong / 65536 ) & 255,
@octet3 = ( @ipLong / 256 ) & 255,
@octet4 = @ipLong & 255
SELECT
@rv =
CONVERT( VARCHAR(5), @octet1 ) +
'.' +
CONVERT( VARCHAR(5), @octet2 ) +
'.' +
CONVERT( VARCHAR(5), @octet3 ) +
'.' +
CONVERT( VARCHAR(5), @octet4 )
return @rv
END
GO