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