/****** Object:  Database kalidbOne    Script Date: 9/6/2005 3:46:10 PM ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'kalidbOne')
	DROP DATABASE [kalidbOne]
GO

CREATE DATABASE [kalidbOne]  ON (NAME = N'kalidbOne_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\DatakalidbOne_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'kalidbOne_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\DatakalidbOne_Log.LDF' , SIZE = 3, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO


exec sp_dboption N'kalidbOne', N'autoclose', N'false'
GO

exec sp_dboption N'kalidbOne', N'bulkcopy', N'false'
GO

exec sp_dboption N'kalidbOne', N'trunc. log', N'false'
GO

exec sp_dboption N'kalidbOne', N'torn page detection', N'true'
GO

exec sp_dboption N'kalidbOne', N'read only', N'false'
GO

exec sp_dboption N'kalidbOne', N'dbo use', N'false'
GO

exec sp_dboption N'kalidbOne', N'single', N'false'
GO

exec sp_dboption N'kalidbOne', N'autoshrink', N'false'
GO

exec sp_dboption N'kalidbOne', N'ANSI null default', N'false'
GO

exec sp_dboption N'kalidbOne', N'recursive triggers', N'false'
GO

exec sp_dboption N'kalidbOne', N'ANSI nulls', N'false'
GO

exec sp_dboption N'kalidbOne', N'concat null yields null', N'false'
GO

exec sp_dboption N'kalidbOne', N'cursor close on commit', N'false'
GO

exec sp_dboption N'kalidbOne', N'default to local cursor', N'false'
GO

exec sp_dboption N'kalidbOne', N'quoted identifier', N'false'
GO

exec sp_dboption N'kalidbOne', N'ANSI warnings', N'false'
GO

exec sp_dboption N'kalidbOne', N'auto create statistics', N'true'
GO

exec sp_dboption N'kalidbOne', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
	exec sp_dboption N'kalidbOne', N'db chaining', N'false'
GO

use [kalidbOne]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_InternalLabel_Category]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[InternalLabel] DROP CONSTRAINT FK_InternalLabel_Category
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Products_Customer]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Products] DROP CONSTRAINT FK_Products_Customer
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Category_Department]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Category] DROP CONSTRAINT FK_Category_Department
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_label_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[label] DROP CONSTRAINT FK_label_Products
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_UsersInRoles_UserRoles]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UsersInRoles] DROP CONSTRAINT FK_UsersInRoles_UserRoles
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_UsersInRoles_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UsersInRoles] DROP CONSTRAINT FK_UsersInRoles_Users
GO

/****** Object:  Table [dbo].[Category]    Script Date: 9/6/2005 3:46:11 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Category]
GO

/****** Object:  Table [dbo].[Customer]    Script Date: 9/6/2005 3:46:11 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Customer]
GO

/****** Object:  Table [dbo].[Department]    Script Date: 9/6/2005 3:46:11 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Department]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Department]
GO

/****** Object:  Table [dbo].[InternalLabel]    Script Date: 9/6/2005 3:46:11 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InternalLabel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[InternalLabel]
GO

/****** Object:  Table [dbo].[MsgLog]    Script Date: 9/6/2005 3:46:11 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MsgLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MsgLog]
GO

/****** Object:  Table [dbo].[Products]    Script Date: 9/6/2005 3:46:11 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Products]
GO

/****** Object:  Table [dbo].[UserRoles]    Script Date: 9/6/2005 3:46:11 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserRoles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[UserRoles]
GO

/****** Object:  Table [dbo].[Users]    Script Date: 9/6/2005 3:46:11 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users]
GO

/****** Object:  Table [dbo].[UsersInRoles]    Script Date: 9/6/2005 3:46:11 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UsersInRoles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[UsersInRoles]
GO

/****** Object:  Table [dbo].[label]    Script Date: 9/6/2005 3:46:11 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[label]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[label]
GO

/****** Object:  Table [dbo].[product]    Script Date: 9/6/2005 3:46:11 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[product]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[product]
GO

/****** Object:  User kamal    Script Date: 9/6/2005 3:46:11 PM ******/
if not exists (select * from dbo.sysusers where name = N'kamal' and uid < 16382)
	EXEC sp_grantdbaccess N'kamal', N'kamal'
GO

/****** Object:  User kamal    Script Date: 9/6/2005 3:46:11 PM ******/
exec sp_addrolemember N'db_owner', N'kamal'
GO

/****** Object:  Table [dbo].[Category]    Script Date: 9/6/2005 3:46:16 PM ******/
CREATE TABLE [dbo].[Category] (
	[ProductKey] [int] IDENTITY (1, 1) NOT NULL ,
	[ProductID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Label] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Active] [bit] NULL ,
	[DtCreated] [datetime] NULL ,
	[InternalUse] [bit] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Customer]    Script Date: 9/6/2005 3:46:18 PM ******/
CREATE TABLE [dbo].[Customer] (
	[CustKey] [int] IDENTITY (1, 1) NOT NULL ,
	[CustID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Name] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Active] [bit] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Department]    Script Date: 9/6/2005 3:46:18 PM ******/
CREATE TABLE [dbo].[Department] (
	[CustKey] [int] IDENTITY (1, 1) NOT NULL ,
	[CustID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Name] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Active] [bit] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[InternalLabel]    Script Date: 9/6/2005 3:46:18 PM ******/
CREATE TABLE [dbo].[InternalLabel] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[ProductID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CustID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DtCreated] [datetime] NULL ,
	[Data] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Active] [bit] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[MsgLog]    Script Date: 9/6/2005 3:46:18 PM ******/
CREATE TABLE [dbo].[MsgLog] (
	[LogId] [int] IDENTITY (1, 1) NOT NULL ,
	[ModuleID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LogTime] [datetime] NULL ,
	[MsgType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ServerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UserAgent] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UserMachineDNS] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UserIP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Authentication] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Message] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Products]    Script Date: 9/6/2005 3:46:18 PM ******/
CREATE TABLE [dbo].[Products] (
	[ProductKey] [int] IDENTITY (1, 1) NOT NULL ,
	[ProductID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Label] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Active] [bit] NULL ,
	[DtCreated] [datetime] NULL ,
	[InternalUse] [bit] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[UserRoles]    Script Date: 9/6/2005 3:46:19 PM ******/
CREATE TABLE [dbo].[UserRoles] (
	[RoleKey] [int] IDENTITY (1, 1) NOT NULL ,
	[RoleName] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AddLabel] [bit] NULL ,
	[AddCustomer] [bit] NULL ,
	[AddProduct] [bit] NULL ,
	[Printing] [bit] NULL ,
	[UpdateLabel] [bit] NULL ,
	[UpdateCustomer] [bit] NULL ,
	[UpdateProduct] [bit] NULL ,
	[LPSStatus] [bit] NULL ,
	[Permission] [int] NULL ,
	[Active] [bit] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Users]    Script Date: 9/6/2005 3:46:19 PM ******/
CREATE TABLE [dbo].[Users] (
	[UserId] [int] IDENTITY (1, 1) NOT NULL ,
	[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Email] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Approved] [bit] NOT NULL ,
	[Trusted] [bit] NOT NULL ,
	[DateCreated] [datetime] NOT NULL ,
	[LastLogin] [datetime] NULL ,
	[LastActivity] [datetime] NULL ,
	[Occupation] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[UsersInRoles]    Script Date: 9/6/2005 3:46:19 PM ******/
CREATE TABLE [dbo].[UsersInRoles] (
	[Username] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Rolename] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[label]    Script Date: 9/6/2005 3:46:19 PM ******/
CREATE TABLE [dbo].[label] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[ProductID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CustID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Description] [char] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NDC] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Quantity] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Misc] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Active] [bit] NULL ,
	[DtCreated] [datetime] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[product]    Script Date: 9/6/2005 3:46:19 PM ******/
CREATE TABLE [dbo].[product] (
	[CustKey] [int] IDENTITY (1, 1) NOT NULL ,
	[CustID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Name] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Active] [bit] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Category] WITH NOCHECK ADD 
	CONSTRAINT [PK_Category] PRIMARY KEY  CLUSTERED 
	(
		[ProductID],
		[CustID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD 
	CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED 
	(
		[CustID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Department] WITH NOCHECK ADD 
	CONSTRAINT [PK_Department] PRIMARY KEY  CLUSTERED 
	(
		[CustID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Products] WITH NOCHECK ADD 
	CONSTRAINT [PK_Products] PRIMARY KEY  CLUSTERED 
	(
		[ProductID],
		[CustID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserRoles] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserRoles] PRIMARY KEY  CLUSTERED 
	(
		[RoleName]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD 
	CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED 
	(
		[UserName]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UsersInRoles] WITH NOCHECK ADD 
	CONSTRAINT [PK_UsersInRoles] PRIMARY KEY  CLUSTERED 
	(
		[Username],
		[Rolename]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserRoles] ADD 
	CONSTRAINT [IX_UserRoles] UNIQUE  NONCLUSTERED 
	(
		[RoleName]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Users] ADD 
	CONSTRAINT [DF_Users_Approved] DEFAULT (1) FOR [Approved],
	CONSTRAINT [DF_Users_Trusted] DEFAULT (0) FOR [Trusted],
	CONSTRAINT [DF_Users_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
	CONSTRAINT [DF_Users_LastLogin] DEFAULT (getdate()) FOR [LastLogin],
	CONSTRAINT [DF_Users_LastActivity] DEFAULT (getdate()) FOR [LastActivity],
	CONSTRAINT [DF_Users_Occupation] DEFAULT ('') FOR [Occupation],
	CONSTRAINT [IX_Users_UniqueEmail] UNIQUE  NONCLUSTERED 
	(
		[Email]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Category] ADD 
	CONSTRAINT [FK_Category_Department] FOREIGN KEY 
	(
		[CustID]
	) REFERENCES [dbo].[Department] (
		[CustID]
	)
GO

ALTER TABLE [dbo].[InternalLabel] ADD 
	CONSTRAINT [FK_InternalLabel_Category] FOREIGN KEY 
	(
		[ProductID],
		[CustID]
	) REFERENCES [dbo].[Category] (
		[ProductID],
		[CustID]
	)
GO

ALTER TABLE [dbo].[Products] ADD 
	CONSTRAINT [FK_Products_Customer] FOREIGN KEY 
	(
		[CustID]
	) REFERENCES [dbo].[Customer] (
		[CustID]
	)
GO

ALTER TABLE [dbo].[UsersInRoles] ADD 
	CONSTRAINT [FK_UsersInRoles_UserRoles] FOREIGN KEY 
	(
		[Rolename]
	) REFERENCES [dbo].[UserRoles] (
		[RoleName]
	),
	CONSTRAINT [FK_UsersInRoles_Users] FOREIGN KEY 
	(
		[Username]
	) REFERENCES [dbo].[Users] (
		[UserName]
	)
GO

ALTER TABLE [dbo].[label] ADD 
	CONSTRAINT [FK_label_Products] FOREIGN KEY 
	(
		[ProductID],
		[CustID]
	) REFERENCES [dbo].[Products] (
		[ProductID],
		[CustID]
	)
GO

/****** Object:  Stored Procedure dbo.ResetData    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ResetData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ResetData]
GO

/****** Object:  Stored Procedure dbo.TempGetAppID    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempGetAppID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempGetAppID]
GO

/****** Object:  Stored Procedure dbo.CreateTempTables    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateTempTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateTempTables]
GO

/****** Object:  Stored Procedure dbo.DeleteExpiredSessions    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteExpiredSessions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteExpiredSessions]
GO

/****** Object:  Stored Procedure dbo.DropTempTables    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DropTempTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DropTempTables]
GO

/****** Object:  Stored Procedure dbo.GetHashCode    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetHashCode]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetHashCode]
GO

/****** Object:  Stored Procedure dbo.GetMajorVersion    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMajorVersion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMajorVersion]
GO

/****** Object:  Stored Procedure dbo.TempGetStateItem    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempGetStateItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempGetStateItem]
GO

/****** Object:  Stored Procedure dbo.TempGetStateItem2    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempGetStateItem2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempGetStateItem2]
GO

/****** Object:  Stored Procedure dbo.TempGetStateItemExclusive    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempGetStateItemExclusive]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempGetStateItemExclusive]
GO

/****** Object:  Stored Procedure dbo.TempGetStateItemExclusive2    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempGetStateItemExclusive2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempGetStateItemExclusive2]
GO

/****** Object:  Stored Procedure dbo.TempInsertStateItemLong    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempInsertStateItemLong]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempInsertStateItemLong]
GO

/****** Object:  Stored Procedure dbo.TempInsertStateItemShort    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempInsertStateItemShort]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempInsertStateItemShort]
GO

/****** Object:  Stored Procedure dbo.TempReleaseStateItemExclusive    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempReleaseStateItemExclusive]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempReleaseStateItemExclusive]
GO

/****** Object:  Stored Procedure dbo.TempRemoveStateItem    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempRemoveStateItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempRemoveStateItem]
GO

/****** Object:  Stored Procedure dbo.TempResetTimeout    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempResetTimeout]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempResetTimeout]
GO

/****** Object:  Stored Procedure dbo.TempUpdateStateItemLong    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempUpdateStateItemLong]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempUpdateStateItemLong]
GO

/****** Object:  Stored Procedure dbo.TempUpdateStateItemLongNullShort    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempUpdateStateItemLongNullShort]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempUpdateStateItemLongNullShort]
GO

/****** Object:  Stored Procedure dbo.TempUpdateStateItemShort    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempUpdateStateItemShort]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempUpdateStateItemShort]
GO

/****** Object:  Stored Procedure dbo.TempUpdateStateItemShortNullLong    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempUpdateStateItemShortNullLong]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempUpdateStateItemShortNullLong]
GO

/****** Object:  Table [dbo].[ASPStateTempApplications]    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ASPStateTempApplications]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ASPStateTempApplications]
GO

/****** Object:  Table [dbo].[ASPStateTempSessions]    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ASPStateTempSessions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ASPStateTempSessions]
GO

/****** Object:  User Defined Datatype tAppName    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.systypes where name = N'tAppName')
exec sp_droptype N'tAppName'
GO

/****** Object:  User Defined Datatype tSessionId    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.systypes where name = N'tSessionId')
exec sp_droptype N'tSessionId'
GO

/****** Object:  User Defined Datatype tSessionItemLong    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.systypes where name = N'tSessionItemLong')
exec sp_droptype N'tSessionItemLong'
GO

/****** Object:  User Defined Datatype tSessionItemShort    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.systypes where name = N'tSessionItemShort')
exec sp_droptype N'tSessionItemShort'
GO

/****** Object:  User Defined Datatype tTextPtr    Script Date: 24.03.2005 12:17:13 ******/
if exists (select * from dbo.systypes where name = N'tTextPtr')
exec sp_droptype N'tTextPtr'
GO

/****** Object:  Login ASPsession    Script Date: 24.03.2005 12:17:10 ******/
if not exists (select * from master.dbo.syslogins where loginname = N'ASPsession')
BEGIN
	declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'ASPState', @loginlang = N'us_english'
	if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
		select @logindb = N'master'
	if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
		select @loginlang = @@language
	exec sp_addlogin N'ASPsession', N'ASPsession', @logindb, @loginlang
END
GO

/****** Object:  User ASPsession    Script Date: 24.03.2005 12:17:11 ******/
if not exists (select * from dbo.sysusers where name = N'ASPsession' and uid < 16382)
	EXEC sp_grantdbaccess N'ASPsession', N'ASPsession'
GO

/****** Object:  User dbo    Script Date: 24.03.2005 12:17:11 ******/
/****** Object:  User ASPsession    Script Date: 24.03.2005 12:17:11 ******/
exec sp_addrolemember N'db_datareader', N'ASPsession'
GO

/****** Object:  User ASPsession    Script Date: 24.03.2005 12:17:11 ******/
exec sp_addrolemember N'db_datawriter', N'ASPsession'
GO

/****** Object:  User Defined Datatype tTextPtr    Script Date: 24.03.2005 12:17:13 ******/
setuser
GO

EXEC sp_addtype N'tTextPtr', N'varbinary (16)', N'null'
GO

setuser
GO

/****** Object:  User Defined Datatype tAppName    Script Date: 24.03.2005 12:17:14 ******/
setuser
GO

EXEC sp_addtype N'tAppName', N'varchar (280)', N'not null'
GO

setuser
GO

/****** Object:  User Defined Datatype tSessionId    Script Date: 24.03.2005 12:17:14 ******/
setuser
GO

EXEC sp_addtype N'tSessionId', N'char (32)', N'not null'
GO

setuser
GO

/****** Object:  User Defined Datatype tSessionItemLong    Script Date: 24.03.2005 12:17:14 ******/
setuser
GO

EXEC sp_addtype N'tSessionItemLong', N'image', N'null'
GO

setuser
GO

/****** Object:  User Defined Datatype tSessionItemShort    Script Date: 24.03.2005 12:17:14 ******/
setuser
GO

EXEC sp_addtype N'tSessionItemShort', N'varbinary (7000)', N'null'
GO

setuser
GO

/****** Object:  Table [dbo].[ASPStateTempApplications]    Script Date: 24.03.2005 12:17:14 ******/
CREATE TABLE [dbo].[ASPStateTempApplications] (
	[AppId] [int] NOT NULL ,
	[AppName] [char] (280) COLLATE Romanian_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[ASPStateTempSessions]    Script Date: 24.03.2005 12:17:14 ******/
CREATE TABLE [dbo].[ASPStateTempSessions] (
	[SessionId] [char] (32) COLLATE Romanian_CI_AS NOT NULL ,
	[Created] [datetime] NOT NULL ,
	[Expires] [datetime] NOT NULL ,
	[LockDate] [datetime] NOT NULL ,
	[LockDateLocal] [datetime] NOT NULL ,
	[LockCookie] [int] NOT NULL ,
	[Timeout] [int] NOT NULL ,
	[Locked] [bit] NOT NULL ,
	[SessionItemShort] [varbinary] (7000) NULL ,
	[SessionItemLong] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ASPStateTempApplications] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[AppId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ASPStateTempSessions] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[SessionId]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [Index_AppName] ON [dbo].[ASPStateTempApplications]([AppName]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ASPStateTempSessions] ADD 
	CONSTRAINT [DF__ASPStateT__Creat__0AD2A005] DEFAULT (getutcdate()) FOR [Created]
GO

GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[ASPStateTempApplications]  TO [ASPsession]
GO

GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[ASPStateTempSessions]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.CreateTempTables    Script Date: 24.03.2005 12:17:14 ******/

        CREATE PROCEDURE CreateTempTables
        AS
            CREATE TABLE ASPState..ASPStateTempSessions (
                SessionId           CHAR(32)        NOT NULL PRIMARY KEY,
                Created             DATETIME        NOT NULL DEFAULT GETUTCDATE(),
                Expires             DATETIME        NOT NULL,
                LockDate            DATETIME        NOT NULL,
                LockDateLocal       DATETIME        NOT NULL,
                LockCookie          INT             NOT NULL,
                Timeout             INT             NOT NULL,
                Locked              BIT             NOT NULL,
                SessionItemShort    VARBINARY(7000) NULL,
                SessionItemLong     IMAGE           NULL,
            ) 

            CREATE TABLE ASPState..ASPStateTempApplications (
                AppId               INT             NOT NULL PRIMARY KEY,
                AppName             CHAR(280)       NOT NULL,
            ) 

            CREATE NONCLUSTERED INDEX Index_AppName ON ASPState..ASPStateTempApplications(AppName)

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[CreateTempTables]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.DeleteExpiredSessions    Script Date: 24.03.2005 12:17:14 ******/

        CREATE PROCEDURE DeleteExpiredSessions
        AS
            DECLARE @now DATETIME
            SET @now = GETUTCDATE()

            DELETE ASPState..ASPStateTempSessions
            WHERE Expires < @now

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[DeleteExpiredSessions]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.DropTempTables    Script Date: 24.03.2005 12:17:15 ******/

CREATE PROCEDURE DropTempTables
AS
    IF OBJECT_ID('ASPState..ASPStateTempSessions','U') IS NOT NULL BEGIN
        DROP TABLE ASPState..ASPStateTempSessions
    END

    IF OBJECT_ID('ASPState..ASPStateTempApplications','U') IS NOT NULL BEGIN
        DROP TABLE ASPState..ASPStateTempApplications
    END

    RETURN 0

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[DropTempTables]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.GetHashCode    Script Date: 24.03.2005 12:17:15 ******/

CREATE PROCEDURE GetHashCode
    @input tAppName,
    @hash INT OUTPUT
AS
    /* 
       This sproc is based on this C# hash function:

        int GetHashCode(string s)
        {
            int     hash = 5381;
            int     len = s.Length;

            for (int i = 0; i < len; i++) {
                int     c = Convert.ToInt32(s[i]);
                hash = ((hash << 5) + hash) ^ c;
            }

            return hash;
        }

        However, SQL 7 doesn't provide a 32-bit integer
        type that allows rollover of bits, we have to
        divide our 32bit integer into the upper and lower
        16 bits to do our calculation.
    */
       
    DECLARE @hi_16bit   INT
    DECLARE @lo_16bit   INT
    DECLARE @hi_t       INT
    DECLARE @lo_t       INT
    DECLARE @len        INT
    DECLARE @i          INT
    DECLARE @c          INT
    DECLARE @carry      INT

    SET @hi_16bit = 0
    SET @lo_16bit = 5381
    
    SET @len = DATALENGTH(@input)
    SET @i = 1
    
    WHILE (@i <= @len)
    BEGIN
        SET @c = ASCII(SUBSTRING(@input, @i, 1))

        /* Formula:                        
           hash = ((hash << 5) + hash) ^ c */

        /* hash << 5 */
        SET @hi_t = @hi_16bit * 32 /* high 16bits << 5 */
        SET @hi_t = @hi_t & 0xFFFF /* zero out overflow */
        
        SET @lo_t = @lo_16bit * 32 /* low 16bits << 5 */
        
        SET @carry = @lo_16bit & 0x1F0000 /* move low 16bits carryover to hi 16bits */
        SET @carry = @carry / 0x10000 /* >> 16 */
        SET @hi_t = @hi_t + @carry
        SET @hi_t = @hi_t & 0xFFFF /* zero out overflow */

        /* + hash */
        SET @lo_16bit = @lo_16bit + @lo_t
        SET @hi_16bit = @hi_16bit + @hi_t + (@lo_16bit / 0x10000)
        /* delay clearing the overflow */

        /* ^c */
        SET @lo_16bit = @lo_16bit ^ @c

        /* Now clear the overflow bits */	
        SET @hi_16bit = @hi_16bit & 0xFFFF
        SET @lo_16bit = @lo_16bit & 0xFFFF

        SET @i = @i + 1
    END

    /* Do a sign extension of the hi-16bit if needed */
    IF (@hi_16bit & 0x8000 <> 0)
        SET @hi_16bit = 0xFFFF0000 | @hi_16bit

    /* Merge hi and lo 16bit back together */
    SET @hi_16bit = @hi_16bit * 0x10000 /* << 16 */
    SET @hash = @hi_16bit | @lo_16bit

    RETURN 0

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[GetHashCode]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.GetMajorVersion    Script Date: 24.03.2005 12:17:15 ******/

CREATE PROCEDURE GetMajorVersion
    @@ver int output
AS
/* Find out the version */

IF OBJECT_ID('tempdb..#AspstateVer') IS NOT NULL BEGIN
    DROP TABLE #AspstateVer
END

CREATE TABLE #AspstateVer
(
    c1 INT,
    c2 CHAR(100),
    c3 CHAR(100),
    version CHAR(100)
)

INSERT INTO	#AspstateVer
EXEC master..xp_msver ProductVersion

DECLARE @version CHAR(100)
DECLARE @dot INT

SELECT @version = version FROM #AspstateVer
SELECT @dot = CHARINDEX('.', @version)
SELECT @@ver = CONVERT(INT, SUBSTRING(@version, 1, @dot-1))

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[GetMajorVersion]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempGetStateItem    Script Date: 24.03.2005 12:17:15 ******/

        CREATE PROCEDURE TempGetStateItem
            @id         tSessionId,
            @itemShort  tSessionItemShort OUTPUT,
            @locked     BIT OUTPUT,
            @lockDate   DATETIME OUTPUT,
            @lockCookie INT OUTPUT
        AS
            DECLARE @textptr AS tTextPtr
            DECLARE @length AS INT
            DECLARE @now as DATETIME
            SET @now = GETUTCDATE()

            UPDATE ASPState..ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, @now), 
                @locked = Locked,
                @lockDate = LockDateLocal,
                @lockCookie = LockCookie,
                @itemShort = CASE @locked
                    WHEN 0 THEN SessionItemShort
                    ELSE NULL
                    END,
                @textptr = CASE @locked
                    WHEN 0 THEN TEXTPTR(SessionItemLong)
                    ELSE NULL
                    END,
                @length = CASE @locked
                    WHEN 0 THEN DATALENGTH(SessionItemLong)
                    ELSE NULL
                    END
            WHERE SessionId = @id
            IF @length IS NOT NULL BEGIN
                READTEXT ASPState..ASPStateTempSessions.SessionItemLong @textptr 0 @length
            END

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempGetStateItem]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempGetStateItem2    Script Date: 24.03.2005 12:17:15 ******/

        CREATE PROCEDURE TempGetStateItem2
            @id         tSessionId,
            @itemShort  tSessionItemShort OUTPUT,
            @locked     BIT OUTPUT,
            @lockAge    INT OUTPUT,
            @lockCookie INT OUTPUT
        AS
            DECLARE @textptr AS tTextPtr
            DECLARE @length AS INT
            DECLARE @now as DATETIME
            SET @now = GETUTCDATE()

            UPDATE ASPState..ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, @now), 
                @locked = Locked,
                @lockAge = DATEDIFF(second, LockDate, @now),
                @lockCookie = LockCookie,
                @itemShort = CASE @locked
                    WHEN 0 THEN SessionItemShort
                    ELSE NULL
                    END,
                @textptr = CASE @locked
                    WHEN 0 THEN TEXTPTR(SessionItemLong)
                    ELSE NULL
                    END,
                @length = CASE @locked
                    WHEN 0 THEN DATALENGTH(SessionItemLong)
                    ELSE NULL
                    END
            WHERE SessionId = @id
            IF @length IS NOT NULL BEGIN
                READTEXT ASPState..ASPStateTempSessions.SessionItemLong @textptr 0 @length
            END

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempGetStateItem2]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempGetStateItemExclusive    Script Date: 24.03.2005 12:17:15 ******/

        CREATE PROCEDURE TempGetStateItemExclusive
            @id         tSessionId,
            @itemShort  tSessionItemShort OUTPUT,
            @locked     BIT OUTPUT,
            @lockDate   DATETIME OUTPUT,
            @lockCookie INT OUTPUT
        AS
            DECLARE @textptr AS tTextPtr
            DECLARE @length AS INT
            DECLARE @now as DATETIME
            DECLARE @nowLocal as DATETIME

            SET @now = GETUTCDATE()
            SET @nowLocal = GETDATE()
            
            UPDATE ASPState..ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, @now), 
                LockDate = CASE Locked
                    WHEN 0 THEN @now
                    ELSE LockDate
                    END,
                @lockDate = LockDateLocal = CASE Locked
                    WHEN 0 THEN @nowLocal
                    ELSE LockDateLocal
                    END,
                @lockCookie = LockCookie = CASE Locked
                    WHEN 0 THEN LockCookie + 1
                    ELSE LockCookie
                    END,
                @itemShort = CASE Locked
                    WHEN 0 THEN SessionItemShort
                    ELSE NULL
                    END,
                @textptr = CASE Locked
                    WHEN 0 THEN TEXTPTR(SessionItemLong)
                    ELSE NULL
                    END,
                @length = CASE Locked
                    WHEN 0 THEN DATALENGTH(SessionItemLong)
                    ELSE NULL
                    END,
                @locked = Locked,
                Locked = 1
            WHERE SessionId = @id
            IF @length IS NOT NULL BEGIN
                READTEXT ASPState..ASPStateTempSessions.SessionItemLong @textptr 0 @length
            END

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempGetStateItemExclusive]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempGetStateItemExclusive2    Script Date: 24.03.2005 12:17:15 ******/

        CREATE PROCEDURE TempGetStateItemExclusive2
            @id         tSessionId,
            @itemShort  tSessionItemShort OUTPUT,
            @locked     BIT OUTPUT,
            @lockAge    INT OUTPUT,
            @lockCookie INT OUTPUT
        AS
            DECLARE @textptr AS tTextPtr
            DECLARE @length AS INT
            DECLARE @now as DATETIME
            DECLARE @nowLocal as DATETIME

            SET @now = GETUTCDATE()
            SET @nowLocal = GETDATE()
            
            UPDATE ASPState..ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, @now), 
                LockDate = CASE Locked
                    WHEN 0 THEN @now
                    ELSE LockDate
                    END,
                LockDateLocal = CASE Locked
                    WHEN 0 THEN @nowLocal
                    ELSE LockDateLocal
                    END,
                @lockAge = CASE Locked
                    WHEN 0 THEN 0
                    ELSE DATEDIFF(second, LockDate, @now)
                    END,
                @lockCookie = LockCookie = CASE Locked
                    WHEN 0 THEN LockCookie + 1
                    ELSE LockCookie
                    END,
                @itemShort = CASE Locked
                    WHEN 0 THEN SessionItemShort
                    ELSE NULL
                    END,
                @textptr = CASE Locked
                    WHEN 0 THEN TEXTPTR(SessionItemLong)
                    ELSE NULL
                    END,
                @length = CASE Locked
                    WHEN 0 THEN DATALENGTH(SessionItemLong)
                    ELSE NULL
                    END,
                @locked = Locked,
                Locked = 1
            WHERE SessionId = @id
            IF @length IS NOT NULL BEGIN
                READTEXT ASPState..ASPStateTempSessions.SessionItemLong @textptr 0 @length
            END

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempGetStateItemExclusive2]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempInsertStateItemLong    Script Date: 24.03.2005 12:17:15 ******/

        CREATE PROCEDURE TempInsertStateItemLong
            @id         tSessionId,
            @itemLong   tSessionItemLong,
            @timeout    INT
        AS    
            DECLARE @now as DATETIME
            DECLARE @nowLocal as DATETIME
            
            SET @now = GETUTCDATE()
            SET @nowLocal = GETDATE()

            INSERT ASPState..ASPStateTempSessions 
                (SessionId, 
                 SessionItemLong, 
                 Timeout, 
                 Expires, 
                 Locked, 
                 LockDate,
                 LockDateLocal,
                 LockCookie) 
            VALUES 
                (@id, 
                 @itemLong, 
                 @timeout, 
                 DATEADD(n, @timeout, @now), 
                 0, 
                 @now,
                 @nowLocal,
                 1)

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempInsertStateItemLong]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempInsertStateItemShort    Script Date: 24.03.2005 12:17:15 ******/

        CREATE PROCEDURE TempInsertStateItemShort
            @id         tSessionId,
            @itemShort  tSessionItemShort,
            @timeout    INT
        AS    

            DECLARE @now as DATETIME
            DECLARE @nowLocal as DATETIME
            
            SET @now = GETUTCDATE()
            SET @nowLocal = GETDATE()

            INSERT ASPState..ASPStateTempSessions 
                (SessionId, 
                 SessionItemShort, 
                 Timeout, 
                 Expires, 
                 Locked, 
                 LockDate,
                 LockDateLocal,
                 LockCookie) 
            VALUES 
                (@id, 
                 @itemShort, 
                 @timeout, 
                 DATEADD(n, @timeout, @now), 
                 0, 
                 @now,
                 @nowLocal,
                 1)

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempInsertStateItemShort]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempReleaseStateItemExclusive    Script Date: 24.03.2005 12:17:15 ******/

        CREATE PROCEDURE TempReleaseStateItemExclusive
            @id         tSessionId,
            @lockCookie INT
        AS
            UPDATE ASPState..ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, GETUTCDATE()), 
                Locked = 0
            WHERE SessionId = @id AND LockCookie = @lockCookie

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempReleaseStateItemExclusive]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempRemoveStateItem    Script Date: 24.03.2005 12:17:15 ******/

CREATE PROCEDURE TempRemoveStateItem
    @id     tSessionId,
    @lockCookie INT
AS
    DELETE ASPState..ASPStateTempSessions
    WHERE SessionId = @id AND LockCookie = @lockCookie
    RETURN 0

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempRemoveStateItem]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempResetTimeout    Script Date: 24.03.2005 12:17:15 ******/

        CREATE PROCEDURE TempResetTimeout
            @id     tSessionId
        AS
            UPDATE ASPState..ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, GETUTCDATE())
            WHERE SessionId = @id
            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempResetTimeout]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempUpdateStateItemLong    Script Date: 24.03.2005 12:17:15 ******/

        CREATE PROCEDURE TempUpdateStateItemLong
            @id         tSessionId,
            @itemLong   tSessionItemLong,
            @timeout    INT,
            @lockCookie INT
        AS    
            UPDATE ASPState..ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, GETUTCDATE()), 
                SessionItemLong = @itemLong,
                Timeout = @timeout,
                Locked = 0
            WHERE SessionId = @id AND LockCookie = @lockCookie

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempUpdateStateItemLong]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempUpdateStateItemLongNullShort    Script Date: 24.03.2005 12:17:15 ******/

        CREATE PROCEDURE TempUpdateStateItemLongNullShort
            @id         tSessionId,
            @itemLong   tSessionItemLong,
            @timeout    INT,
            @lockCookie INT
        AS    
            UPDATE ASPState..ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, GETUTCDATE()), 
                SessionItemLong = @itemLong, 
                SessionItemShort = NULL,
                Timeout = @timeout,
                Locked = 0
            WHERE SessionId = @id AND LockCookie = @lockCookie

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempUpdateStateItemLongNullShort]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempUpdateStateItemShort    Script Date: 24.03.2005 12:17:15 ******/

        CREATE PROCEDURE TempUpdateStateItemShort
            @id         tSessionId,
            @itemShort  tSessionItemShort,
            @timeout    INT,
            @lockCookie INT
        AS    
            UPDATE ASPState..ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, GETUTCDATE()), 
                SessionItemShort = @itemShort, 
                Timeout = @timeout,
                Locked = 0
            WHERE SessionId = @id AND LockCookie = @lockCookie

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempUpdateStateItemShort]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempUpdateStateItemShortNullLong    Script Date: 24.03.2005 12:17:15 ******/

        CREATE PROCEDURE TempUpdateStateItemShortNullLong
            @id         tSessionId,
            @itemShort  tSessionItemShort,
            @timeout    INT,
            @lockCookie INT
        AS    
            UPDATE ASPState..ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, GETUTCDATE()), 
                SessionItemShort = @itemShort, 
                SessionItemLong = NULL, 
                Timeout = @timeout,
                Locked = 0
            WHERE SessionId = @id AND LockCookie = @lockCookie

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempUpdateStateItemShortNullLong]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.ResetData    Script Date: 24.03.2005 12:17:15 ******/

CREATE PROCEDURE ResetData
AS
    EXECUTE DropTempTables
    EXECUTE CreateTempTables
    RETURN 0

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ResetData]  TO [ASPsession]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TempGetAppID    Script Date: 24.03.2005 12:17:15 ******/

CREATE PROCEDURE TempGetAppID
    @appName    tAppName,
    @appId      INT OUTPUT
AS
    SET @appName = LOWER(@appName)
    SET @appId = NULL
    
    SELECT @appId = AppId
    FROM ASPState..ASPStateTempApplications
    WHERE AppName = @appName
    
    IF @appId IS NULL BEGIN
        BEGIN TRAN        

        SELECT @appId = AppId
        FROM ASPState..ASPStateTempApplications WITH (TABLOCKX)
        WHERE AppName = @appName
        
        IF @appId IS NULL
        BEGIN
            EXEC GetHashCode @appName, @appId OUTPUT
            
            INSERT ASPState..ASPStateTempApplications
            VALUES
            (@appId, @appName)
            
            IF @@ERROR = 2627 
            BEGIN
                DECLARE @dupApp tAppName
            
                SELECT @dupApp = RTRIM(AppName)
                FROM ASPState..ASPStateTempApplications 
                WHERE AppId = @appId
                
                RAISERROR('SQL session state fatal error: hash-code collision between applications ''%s'' and ''%s''. Please rename the 1st application to resolve the problem.', 
                            18, 1, @appName, @dupApp)
            END
        END

        COMMIT
    END

    RETURN 0

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[TempGetAppID]  TO [ASPsession]
GO