/**************************************************************************************************

Author		:	Vinod Kumar

Script Dtd	:	22 - August - 2004

DTS Demo 	:	SQL Server Central Site

Demo Details	:	This is the database script for DTS demo in Yukon.

Pre-Requisite	:	The database is created with Compatibility 90 for Yukon. Hence a valid
			Yukon installation is required.

**************************************************************************************************/

USE [master]
GO
CREATE DATABASE [DTSDemo] ON  PRIMARY 
( NAME = N'DTSDemo', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DTSDemo.mdf' , SIZE = 97408KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DTSDemo_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DTSDemo_log.LDF' , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'DTSDemo', @new_cmptlevel=90
GO
EXEC [DTSDemo].[dbo].[sp_fulltext_database] @action = 'enable'
GO
ALTER DATABASE [DTSDemo] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [DTSDemo] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [DTSDemo] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [DTSDemo] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [DTSDemo] SET ARITHABORT OFF 
GO
ALTER DATABASE [DTSDemo] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [DTSDemo] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [DTSDemo] SET AUTO_SHRINK ON 
GO
ALTER DATABASE [DTSDemo] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [DTSDemo] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [DTSDemo] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [DTSDemo] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [DTSDemo] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [DTSDemo] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [DTSDemo] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [DTSDemo] SET  READ_WRITE 
GO
ALTER DATABASE [DTSDemo] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [DTSDemo] SET  MULTI_USER 
GO
ALTER DATABASE [DTSDemo] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [DTSDemo] SET DB_CHAINING OFF 
GO
use DTSDemo
go
SET ANSI_NULLS ON
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aggevnt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[aggevnt](
	[src] [varchar](60) NULL,
	[typ] [varchar](50) NULL

) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET ANSI_NULLS ON
GO
SET ANSI_NULLS OFF
GO
SET ANSI_NULLS ON
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ErrorEvents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[ErrorEvents](
	[Type] [varchar](50) NULL,
	[Date] [varchar](50) NULL,
	[Time] [varchar](50) NULL,
	[Source] [varchar](50) NULL


) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET ANSI_NULLS ON
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[evntlog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[evntlog](
	[evtDate] [datetime] NULL,
	[evtTime] [datetime] NULL,
	[source] [varchar](50) NULL,
	[evtType] [varchar](30) NULL,
	[category] [varchar](25) NULL,
	[eventnum] [int] NULL,
	[evtuser] [varchar](50) NULL,
	[machine] [varchar](50) NULL


) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET ANSI_NULLS ON
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FailureEvents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[FailureEvents](
	[Type] [varchar](50) NULL,
	[Date] [varchar](50) NULL,
	[Time] [varchar](50) NULL,
	[Source] [varchar](50) NULL


) ON [PRIMARY]
END
GO

SET ANSI_NULLS OFF
GO
SET ANSI_NULLS ON
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SuccessAuditEvents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[SuccessAuditEvents](
	[Type] [varchar](50) NULL,
	[Date] [varchar](50) NULL,
	[Time] [varchar](50) NULL,
	[Source] [varchar](50) NULL


) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET ANSI_NULLS ON
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WarningEvents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[WarningEvents](
	[Type] [varchar](50) NULL,
	[Date] [varchar](50) NULL,
	[Time] [varchar](50) NULL,
	[Source] [varchar](50) NULL,
	[Category] [varchar](50) NULL


) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO

-- Use this statement to find the amount of data that has been uploaded into the database.
Select 
(Select Count(*) from [dbo].[aggevnt]) as [aggevnt],
(Select Count(*) from [dbo].[ErrorEvents]) as [ErrorEvents],
(Select Count(*) from [dbo].[evntlog]) as [evntlog],
(Select Count(*) from [dbo].[FailureEvents]) as [FailureEvents],
(Select Count(*) from [dbo].[SuccessAuditEvents]) as [SuccessAuditEvents],
(Select Count(*) from [dbo].[WarningEvents]) as [WarningEvents]