hotfix.sql

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

		/* NUMERIC IDENTITY user_type, create_params, auto_increment */
		values			 (25,	'precision' ,1, 'numeric')

end
else	/*	Pre 6.0 server, add SYSNAME create param */
	begin
		insert into spt_datatype_info_ext
			/* SYSNAME	 user_type, create_param, auto_increments */
			values			 (18,	'max length' ,0, 'sysname')

	end
go

if (charindex('7.00', @@version) = 0 and
	charindex('8.00', @@version) = 0)
begin
	print ''
	print ''
	print 'Warning:'
	print 'you are installing the stored procedures '
	print 'on a pre 8.0 SQL Server.'
	print 'Ignore the following errors.'
end
go
if (charindex('7.00', @@version) > 0 or
	charindex('8.00', @@version) > 0)
begin	/*	Update usertypes for 8.0 server */
	begin tran
	insert into spt_datatype_info_ext
		/* NCHAR	 user_type, create_params, auto_increment */
		values			 (0,	'length' ,0, 'nchar')

	insert into spt_datatype_info_ext
		/* NVARCHAR	 user_type, create_params, auto_increment */
		values			 (0,	'max length' ,0, 'nvarchar')

	/* SET user_type TO SPHINX VALUES */
	update spt_datatype_info_ext set user_type = xusertype
		from spt_datatype_info_ext e, systypes t where t.name = e.typename

	commit tran
end
go

create unique clustered index datatypeinfoextclust on spt_datatype_info_ext(user_type,AUTO_INCREMENT)
go

print 'creating table spt_datatype_info'
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 (
		ss_dtype			tinyint 	not null,
		fixlen				int 		null,		/* datatype len for variable, else null */
		ODBCVer 			tinyint 	null,		/* version if needed, else null */
		TYPE_NAME			varchar(32)	not null,
		DATA_TYPE			smallint	not null,
		data_precision		int 		null,
		numeric_scale		smallint	null,	/* min scale if 6.0 */
		RADIX				smallint	null,
		length				int 		null,
		LITERAL_PREFIX		varchar(32) null,
		LITERAL_SUFFIX		varchar(32) null,
		CREATE_PARAMS		varchar(32) null,
		NULLABLE			smallint	not null,
		CASE_SENSITIVE		smallint	not null,
		SEARCHABLE			smallint	not null,
		UNSIGNED_ATTRIBUTE	smallint	null,
		MONEY				smallint	not null,
		AUTO_INCREMENT		smallint	null,
		LOCAL_TYPE_NAME 	varchar(32)	null,
		charbin 			tinyint 	null, /* 0 for char/binary types, NULL for all others */
		SQL_DATA_TYPE		smallint	not null,
		SQL_DATETIME_SUB	smallint	null)
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 (
		ss_dtype			tinyint 	not null,
		fixlen				int 		null,		/* datatype len for variable, else null */
		ODBCVer 			tinyint 	null,		/* version if needed, else null */
		TYPE_NAME			sysname 	not null,
		DATA_TYPE			smallint	not null,
		data_precision		int 		null,
		numeric_scale		smallint	null,	/* min scale if 6.0 */
		RADIX				smallint	null,
		length				int 		null,
		LITERAL_PREFIX		varchar(32) null,
		LITERAL_SUFFIX		varchar(32) null,
		CREATE_PARAMS		varchar(32) null,
		NULLABLE			smallint	not null,
		CASE_SENSITIVE		smallint	not null,
		SEARCHABLE			smallint	not null,
		UNSIGNED_ATTRIBUTE	smallint	null,
		MONEY				smallint	not null,
		AUTO_INCREMENT		smallint	null,
		LOCAL_TYPE_NAME 	sysname 	null,
		charbin 			tinyint 	null, /* 0 for char/binary types, NULL for all others */
		SQL_DATA_TYPE		smallint	not null,
		SQL_DATETIME_SUB	smallint	null)
end
go

grant select on spt_datatype_info to public
go

/* Get case sensitivity */
if 'A' = 'A' /* create dummy begin block */
begin
	declare @case smallint

	begin tran
	select @case = 0
	select @case = 1 where 'a' <> 'A'

	/* Local Binary */
	insert into spt_datatype_info values
	(45,null,null,'binary',-2,null,null,null,null,'0x',null,'length',1,0,2,null,0,null,'binary',0,-2,null)

	/* Local Bit */
	insert into spt_datatype_info values
	(50,null,null,'bit',-7,1,0,null,1,null,null,null,0,0,2,null,0,null,'bit',null,-7,null)

	/* Local Char */
	insert into spt_datatype_info values
	(47,null,null,'char',1,null,null,null,null,'''','''','length',1,@case,3,null,0,null,'char',0,1,null)

	/* Local Datetime */
	insert into spt_datatype_info values
	(61,8,2,'datetime',11,23,3,NULL,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)
	insert into spt_datatype_info values
	(61,8,3,'datetime',93,23,3,NULL,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)

	/* Local Smalldatetime */
	insert into spt_datatype_info values
	(58,4,2,'smalldatetime',11,16,0,NULL,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
	insert into spt_datatype_info values
	(58,4,3,'smalldatetime',93,16,0,NULL,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)

	/* Local Float */
	insert into spt_datatype_info values
	(62,8,2,'float',6,15,null,10,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)
	insert into spt_datatype_info values
	(62,8,3,'float',6,53,null, 2,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)

	/* Local Real */
	insert into spt_datatype_info values
	(59,4,2,'real',7, 7,null,10,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
	insert into spt_datatype_info values
	(59,4,3,'real',7,24,null, 2,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)

	/* Local Smallmoney */
	insert into spt_datatype_info values
	(122,4,null,'smallmoney',3,10,4,10,12,'$',null,null,1,0,2,0,1,0,'smallmoney',null,3,null)

	/* Local Money */
	insert into spt_datatype_info values
	(60,8,null,'money',3,19,4,10,21,'$',null,null,1,0,2,0,1,0,'money',null,3,null)

	/* Local Int */
	insert into spt_datatype_info values
	(56,4,null,'int',4,10,0,10,4,null,null,null,1,0,2,0,0,0,'int',null,4,null)

	commit tran
end
go

if 'A' = 'A' /* create dummy begin block */
begin
	declare @case smallint

	begin tran
	select @case = 0
	select @case = 1 where 'a' <> 'A'


	/* Local Smallint */
	insert into spt_datatype_info values
	(52,2,null,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null)
	insert into spt_datatype_info values
	(52,2,1,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null)

	/* Local Tinyint */
	insert into spt_datatype_info values
	(48,1,null,'tinyint',-6,3,0,10,1,null,null,null,1,0,2,1,0,0,'tinyint',null,-6,null)

	/* Local Text */
	insert into spt_datatype_info values
	(35,null,null,'text',-1,2147483647,null,null,2147483647,'''','''',null,1,@case,1,null,0,null,'text',0,-1,null)

	/* Local Varbinary */
	insert into spt_datatype_info values
	(37,null,null,'varbinary',-3,null,null,null,null,'0x',null,'max length',1,0,2,null,0,null,'varbinary',0,-3,null)

	/* Local Varchar */
	insert into spt_datatype_info values
	(39,null,null,'varchar',12,null,null,null,null,'''','''','max length',1,@case,3,null,0,null,'varchar',0,12,null)

	/* Local Image */
	insert into spt_datatype_info values
	(34,null,null,'image',-4,2147483647,null,null,2147483647,'0x',null,null,1,0,0,null,0,null,'image',0,-4,null)

	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 */
		/* Local Decimal */
		insert into spt_datatype_info values	/* sql server type is 'decimaln' */
		(55,null,null,'decimal',3,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'decimal',null,3,null)

		/* Local Numeric */
		insert into spt_datatype_info values	/* sql server type is 'numericn' */
		(63,null,null,'numeric',2	,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'numeric',null,2,null)

		/* Identity attribute data types */

		/* Identity Int */
		insert into spt_datatype_info values
		(56,null,null,'int identity',4,10,0,10,null,null,null,null,0,0,2,0,0,1,'int identity',null,4,null)

		/* Identity Smallint */
		insert into spt_datatype_info values
		(52,null,null,'smallint identity',5,5,0,10,null,null,null,null,0,0,2,0,0,1,'smallint identity',null,5,null)

		/* Identity Tinyint */
		insert into spt_datatype_info values
		(48,null,null,'tinyint identity',-6,3,0,10,null,null,null,null,0,0,2,1,0,1,'tinyint identity',null,-6,null)

		/* Identity Numeric */
		insert into spt_datatype_info values	/* sql server type is 'decmaln' */
		(55,null,null,'decimal() identity',3,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'decimal() identity',null,3,null)

		/* Identity Numeric */
		insert into spt_datatype_info values	/* sql server type is 'decmaln' */
		(63,null,null,'numeric() identity',2,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'numeric() identity',null,2,null)

	end

	if (charindex('7.00', @@version) = 0 and
		charindex('8.00', @@version) = 0)
	begin	/*	Add nullable type for non-8.0 server */
		/* Local Datetimn */
		insert into spt_datatype_info values
		(111,4,2,'smalldatetime',11,16,0,10,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
		insert into spt_datatype_info values
		(111,4,3,'smalldatetime',93,16,0,10,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
		insert into spt_datatype_info values /* sql server type is 'datetimn' */
		(111,8,2,'datetime',11,23,3,10,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)
		insert into spt_datatype_info values
		(111,8,3,'datetime',93,23,3,10,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)

		/* Local Floatn */
		insert into spt_datatype_info values /* sql server type is 'floatn' */
		(109,4,2,'real',7, 7,null,10,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
		insert into spt_datatype_info values
		(109,4,3,'real',7,24,null, 2,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
		insert into spt_datatype_info values /* sql server type is 'floatn' */
		(109,8,2,'float',6,15,null,10,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)
		insert into spt_datatype_info values
		(109,8,3,'float',6,53,null, 2,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)

		/* Local Moneyn */
		insert into spt_datatype_info values	/* sql server type is 'moneyn' */
		(110,4,null,'smallmoney',3,10,4,10,12,'$',null,null,1,0,2,0,1,0,'smallmoney',null,3,null)
		insert into spt_datatype_info values	/* sql server type is 'moneyn' */
		(110,8,null,'money',3,19,4,10,21,'$',null,null,1,0,2,0,1,0,'money',null,3,null)

		/* Local Intn */
		insert into spt_datatype_info values /* sql server type is 'intn' */
		(38,4,null,'int',4,10,0,10,4,null,null,null,1,0,2,0,0,0,'int',null,4,null)
		insert into spt_datatype_info values /* sql server type is 'intn' */
		(38,2,null,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null)
		insert into spt_datatype_info values
		(38,1,null,'tinyint',-6,3,0,10,1,null,null,null,1,0,2,1,0,0,'tinyint',null,-6,null)

		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 */
			/* Local Decimaln */
			insert into spt_datatype_info values	/* sql server type is 'decimaln' */
			(106,null,null,'decimal',3,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'decimal',null,3,null)
			insert into spt_datatype_info values	/* sql server type is 'decmaln' */
			(106,null,null,'decimal() identity',3,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'decimal() identity',null,3,null)

			/* Local Numericn */
			insert into spt_datatype_info values	/* sql server type is 'numericn' */
			(108,null,null,'numeric',2,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'numeric',null,2,null)
			insert into spt_datatype_info values	/* sql server type is 'decmaln' */
			(108,null,null,'numeric() identity',2,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'numeric() identity',null,2,null)
		end
	end

	commit tran
end
go

if (charindex('7.00', @@version) = 0 and
	charindex('8.00', @@version) = 0)
begin
	print ''
	print ''
	print 'Warning:'
	print 'you are installing the stored procedures '
	print 'on a pre 8.0 SQL Server.'
	print 'Ignore the following errors.'
end
go
if (charindex('7.00', @@version) > 0 or
	charindex('8.00', @@version) > 0)
	begin
		declare @ncase smallint

		select @ncase = 0
		select @ncase = 1 where N'a' <> N'A'

		/* Local Timestamp */
		insert into spt_datatype_info values
		(0,null,null,'timestamp',-2,8,null,null,null,'0x',null,null,1,0,2,null,0,null,'timestamp',0,-2,null)

		/* Local GUID */
		insert into spt_datatype_info values
		(0,null,null,'uniqueidentifier',-11,36,null,null,null,'''','''',null,1,0,2,null,0,null,'uniqueidentifier',NULL,-11,null)

		/* Local NChar */
		insert into spt_datatype_info values
		(0,null,null,'nchar',-8,null,null,null,null,'N''','''','length',1,@ncase,3,null,0,null,'nchar',0,-8,null)

		/* Local NVarchar */
		insert into spt_datatype_info values
		(0,null,null,'nvarchar',-9,null,null,null,null,'N''','''','max length',1,@ncase,3,null,0,null,'nvarchar',0,-9,null)

		/* Local NText */
		insert into spt_datatype_info values
		(0,null,null,'ntext',-10,2147483646,null,null,2147483646,'N''','''',null,1,@ncase,1,null,0,null,'ntext',0,-10,null)

		if (charindex('8.00', @@version) > 0)
		begin
--			/* Local BIGINT */
			insert into spt_datatype_info values
			(127,8,null,'bigint',-5,19,0,10,null,null,null,null,1,0,2,0,0,0,'bigint',null,-5,null)
--			/* Identity BIGINT */
			insert into spt_datatype_info values
			(127,8,null,'bigint identity',-5,19,0,10,null,null,null,null,0,0,2,0,0,1,'bigint identity',null,-5,null)


--			/* sql_variant */
			insert into spt_datatype_info values
			(
			98,					--ss_dtype		
			null,				--fixlen
			null,				--ODBCVer
			'sql_variant',		--TYPE_NAME
			-150,				--SQL DATA TYPE
			8000,				--data_precision
			0,				    --numeric_scale
			10,					--RADIX
			8000,				--length
			null,				--PREFIX
			null,				--SUFFIX
			null,				--Create Params
			1,					--Nullable
			0,				--Case sensitive
			2,					--Searchable
			null,				--UNSIGNED_ATTRIBUTE
			0,				--MONEY
			null,				--AUTO_INCREMENT
			'sql_variant',		--LOCAL TYPE NAME
			0,					--CHARBIN
			-150,				--SQL_DATA_TYPE
			null				--SQL_DATETIME_SUB
			)
		
		end



		update spt_datatype_info set NULLABLE = 1
			where TYPE_NAME = 'bit'

		update spt_datatype_info set ss_dtype = isnull((select distinct xtype from systypes
			where TYPE_NAME like name+'%'),0)
	end
go

create unique clustered index datatypeinfoclust on spt_datatype_info(ss_dtype,fixlen,ODBCVer,AUTO_INCREMENT)
go

dump tran master with no_log
go

print 'creating table spt_server_info'
go
create table spt_server_info (
			  attribute_id		int NOT NULL,
			  attribute_name	varchar(60) NOT NULL,
			  attribute_value	varchar(255) NOT NULL)
go

create unique clustered index serverinfoclust on spt_server_info(attribute_id)
go

if (charindex('7.00', @@version) = 0 and charindex('8.00', @@version) = 0)
	begin
		drop procedure sp_add_server_sortinfo	/* not used by other servers */
		drop procedure sp_add_server_sortinfo75	/* not used by older servers */
		dump tran master with no_log
	end
go


insert into spt_server_info
	values (1,'DBMS_NAME','Microsoft SQL Server')
insert into spt_server_info
	values (2,'DBMS_VER',@@version)
insert into spt_server_info
	values (10,'OWNER_TERM','owner')
insert into spt_server_info
	values (11,'TABLE_TERM','table')
insert into spt_server_info
	values (12,'MAX_OWNER_NAME_LENGTH','30')
insert into spt_server_info
	values (13,'TABLE_LENGTH','30')
insert into spt_server_info
	values (14,'MAX_QUAL_LENGTH','30')
insert into spt_server_info
	values (15,'COLUMN_LENGTH','30')
if 'A' = 'a' /* If not case sensitive server */
begin
	insert into spt_server_info
		values (16,'IDENTIFIER_CASE','MIXED')
end
else