hotfix.sql

This project is to design the automated system for the inventory of Al-Ahmad Cotton and Oil Mills si

/**********************************************************************
 *		     Microsoft SQL Server HOTFIX.SQL
 *		Copyright Microsoft Corporation, 1992-2000
 **********************************************************************
 *	HOTFIX.SQL IS AN APPENDED FILE CONSISTING OF INSTCAT.SQL,
 *			MSDEP65.SQL, HOTFIX2.SQL	
 **********************************************************************/
/*
**	INSTCAT.SQL
**	Installs catalog stored procedures on the Microsoft SQL Server.
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
*/

/*
INTERNAL NOTE:
When this file is updated, you MUST change the last row inserted into
spt_server_info to be version number of this file.	the convention is
j.nn.bbb, where j is the major version number ('7' now), nn is the minor
version number ('10' now), and bbb is the build number.
insert into spt_server_info
	values (500, 'SYS_SPROC_VERSION', '8.00.bbb')
*/

/****************************************************************************/
/* This portion sets up the ability to perform all the functions in this    */
/* script																	*/
/****************************************************************************/
go
use master
go
dump tran master with no_log
go

set quoted_identifier on
go

if (exists (select * from sysobjects
		where name = 'sp_configure' and type = 'P '))
begin
    execute sp_configure 'update',1
end
reconfigure with override
go

exec sp_MS_upd_sysobj_category 1 /*Capture datetime for use below.*/

go

/*
** If old versions of tables exist, drop them.
*/
if (exists (select * from sysobjects
		where name = 'MSdatatype_info' and type = 'U '))
    drop table MSdatatype_info
go
if (exists (select * from sysobjects
		where name = 'MSdatatype_info_ext' and type = 'U '))
    drop table MSdatatype_info_ext
go
if (exists (select * from sysobjects
		where name = 'MStable_types' and type = 'U '))
	drop table MStable_types
go
if (exists (select * from sysobjects
		where name = 'MSserver_info' and type = 'U '))
    drop table MSserver_info
go
if (exists (select * from sysobjects
		where name = 'spt_table_types' and type = 'U '))
	drop table spt_table_types
go
/*
** If tables or procs already exist, drop them.
*/

if (exists (select * from sysobjects
		where name = 'spt_datatype_info' and type = 'U '))
    drop table spt_datatype_info
go
if (exists (select * from sysobjects
		where name = 'spt_datatype_info_ext' and type = 'U '))
    drop table spt_datatype_info_ext
go
if (exists (select * from sysobjects
		where name = 'sp_add_server_sortinfo' and type = 'P '))
    drop proc sp_add_server_sortinfo
go
if (exists (select * from sysobjects
		where name = 'sp_add_server_sortinfo75' and type = 'P '))
    drop proc sp_add_server_sortinfo75
go
if (exists (select * from sysobjects
		where name = 'spt_server_info' and type = 'U '))
    drop table spt_server_info
go
if (exists (select * from sysobjects
		where name = 'sp_tables' and type = 'P '))
    drop proc sp_tables
go
if (exists (select * from sysobjects
		where name = 'sp_statistics' and type = 'P '))
    drop proc sp_statistics
go
if (exists (select * from sysobjects
		where name = 'sp_columns' and type = 'P '))
    drop proc sp_columns
go
if (exists (select * from sysobjects
		where name = 'sp_fkeys' and type = 'P '))
    drop proc sp_fkeys
go
if (exists (select * from sysobjects
		where name = 'sp_pkeys' and type = 'P '))
    drop proc sp_pkeys
dump tran master with no_log
go

go
if (exists (select * from sysobjects
		where name = 'sp_stored_procedures' and type = 'P '))
    drop proc sp_stored_procedures
go
if (exists (select * from sysobjects
		where name = 'sp_sproc_columns' and type = 'P '))
    drop proc sp_sproc_columns
go
if (exists (select * from sysobjects
		where name = 'sp_table_privileges' and type = 'P '))
    drop proc sp_table_privileges
go
if (exists (select * from sysobjects
		where name = 'sp_column_privileges' and type = 'P '))
	drop proc sp_column_privileges
go
dump tran master with no_log
go
if (exists (select * from sysobjects
		where name = 'sp_server_info' and type = 'P '))
    drop proc sp_server_info
go
if (exists (select * from sysobjects
		where name = 'sp_datatype_info' and type = 'P '))
    drop proc sp_datatype_info
go
if (exists (select * from sysobjects
		where name = 'sp_special_columns' and type = 'P '))
    drop proc sp_special_columns
go
if (exists (select * from sysobjects
		where name = 'sp_databases' and type = 'P '))
	drop proc sp_databases
go
dump tran master with no_log
go
if (exists (select * from sysobjects
		where name = 'sp_ddopen' and type = 'P '))
	drop proc sp_ddopen
go
if (exists (select * from sysobjects
		where name = 'sp_tableswc' and type = 'P '))
	drop proc sp_tableswc
go

if (exists (select * from sysobjects
		where name = 'sp_tablecollations' and type = 'P'))
	drop proc sp_tablecollations
go

if (exists (select * from sysobjects
		where name = 'sp_bcp_dbcmptlevel' and type = 'P'))
	drop proc sp_bcp_dbcmptlevel
go

dump tran master with no_log
go


if (exists (select * from sysobjects
		where name = 'spt_provider_types' and type = 'U '))
	begin
	drop table spt_provider_types
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_catalogs_rowset' and type = 'P '))
	begin
	drop procedure sp_catalogs_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_column_privileges_rowset' and type = 'P '))
	begin
	drop procedure sp_column_privileges_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_columns_rowset' and type = 'P '))
	begin
	drop procedure sp_columns_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_check_constraints_rowset' and type = 'P '))
	begin
	drop procedure sp_check_constraints_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_check_constbytable_rowset' and type = 'P '))
	begin
	drop procedure sp_check_constbytable_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_foreign_keys_rowset' and type = 'P '))
	begin
	drop procedure sp_foreign_keys_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_indexes_rowset' and type = 'P '))
	begin
	drop procedure sp_indexes_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_primary_keys_rowset' and type = 'P '))
	begin
	drop procedure sp_primary_keys_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_procedure_params_rowset' and type = 'P '))
	begin
	drop procedure sp_procedure_params_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_procedures_rowset' and type = 'P '))
	begin
	drop procedure sp_procedures_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_provider_types_rowset' and type = 'P '))
	begin
	drop procedure sp_provider_types_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_schemata_rowset' and type = 'P '))
	begin
	drop procedure sp_schemata_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_statistics_rowset' and type = 'P '))
	begin
	drop procedure sp_statistics_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_tables_rowset' and type = 'P '))
	begin
	drop procedure sp_tables_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_tables_info_rowset' and type = 'P '))
	begin
	drop procedure sp_tables_info_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_table_constraints_rowset' and type = 'P '))
	begin
	drop proc sp_table_constraints_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_table_privileges_rowset' and type = 'P '))
	begin
	drop proc sp_table_privileges_rowset
	dump tran master with no_log
	end
go
if (exists (select * from sysobjects
		where name = 'sp_linkedservers_rowset' and type = 'P '))
	begin
	drop proc sp_linkedservers_rowset
	dump tran master with no_log
	end
go

if (exists (select * from sysobjects
		where name = 'sp_table_statistics_rowset' and type = 'P '))
	begin
	drop proc sp_table_statistics_rowset
	dump tran master with no_log
	end
go

if (exists (select * from sysobjects
		where name = 'sp_oledb_column_constraints' and type = 'P '))
	begin
	drop proc sp_oledb_column_constraints
	dump tran master with no_log
	end
go

if (exists (select * from sysobjects
		where name = 'sp_oledb_indexinfo' and type = 'P '))
	begin
	drop proc sp_oledb_indexinfo
	dump tran master with no_log
	end
go

if (exists (select * from sysobjects
		where name = 'sp_oledb_ro_usrname' and type = 'P '))
	begin
	drop proc sp_oledb_ro_usrname
	dump tran master with no_log
	end
go

if (exists (select * from sysobjects
		where name = 'sp_oledb_deflang' and type = 'P '))
	begin
	drop proc sp_oledb_deflang
	dump tran master with no_log
	end
go

if (exists (select * from sysobjects
		where name = 'sp_oledb_defdb' and type = 'P '))
	begin
	drop proc sp_oledb_defdb
	dump tran master with no_log
	end
go

if (exists (select * from sysobjects
		where name = 'sp_oledb_database' and type = 'P '))
	begin
	drop proc sp_oledb_database
	dump tran master with no_log
	end
go

if (exists (select * from sysobjects
		where name = 'sp_oledb_language' and type = 'P '))
	begin
	drop proc sp_oledb_language
	dump tran master with no_log
	end
go

print 'creating table spt_datatype_info_ext'
go

if (charindex('6.00', @@version) = 0 and
	charindex('6.50', @@version) = 0 and
	charindex('7.00', @@version) = 0 and
	charindex('8.00', @@version) = 0)
begin	/*	Pre 6.0 server */
	print ''
	print ''
	print 'Warning:'
	print 'you are installing the stored procedures '
	print 'on a pre 6.0 SQL Server.'
	print 'Ignore the following error.'
	create table spt_datatype_info_ext (
				user_type		smallint	not null,
				CREATE_PARAMS	varchar(32) null,
				AUTO_INCREMENT	smallint null,
				typename		varchar(32))
end
go
if (charindex('6.00', @@version) > 0 or
	charindex('6.50', @@version) > 0 or
	charindex('7.00', @@version) > 0 or
	charindex('8.00', @@version) > 0)
begin	/* 6.0 or later server */
	create table spt_datatype_info_ext (
				user_type		smallint	not null,
				CREATE_PARAMS	varchar(32) null,
				AUTO_INCREMENT	smallint null,
				typename		sysname)	/* from systypes, to avoid xusertype hard-code */
end
go

grant select on spt_datatype_info_ext to public
go


insert into spt_datatype_info_ext
	/* CHAR 	 user_type, create_params, auto_increment */
	values			 (1,	'length' ,0, 'char')

insert into spt_datatype_info_ext
	/* VARCHAR	 user_type, create_params, auto_increment */
	values			 (2,	'max length' ,0, 'varchar')

insert into spt_datatype_info_ext
	/* BINARY	 user_type, create_params, auto_increment */
	values			 (3,	'length' ,0, 'binary')

insert into spt_datatype_info_ext
	/* VARBINARY user_type, create_params, auto_increment */
	values			 (4,	'max length' ,0, 'varbinary')

if	(charindex('6.00', @@version) > 0 or
	 charindex('6.50', @@version) > 0 or
	 charindex('7.00', @@version) > 0 or
	 charindex('8.00', @@version) > 0)
begin	/*	Add 6.0 data types */
	insert into spt_datatype_info_ext
		/* DECIMAL user_type, create_params, auto_increment */
		values			 (26,	'precision,scale' ,0, 'decimal')

	insert into spt_datatype_info_ext
		/* NUMERIC user_type, create_params, auto_increment */
		values			 (25,	'precision,scale' ,0, 'numeric')

	insert into spt_datatype_info_ext
		/* DECIMAL IDENTITY user_type, create_params, auto_increment */
		values			 (26,	'precision' ,1, 'decimal')

	insert into spt_datatype_info_ext