LoggingDatabase.sql

/****** Object:  Database Logging    Script Date: 8/22/2005 ******/
USE [master]
GO

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Logging')
    DROP DATABASE [Logging]
GO

CREATE DATABASE [Logging]
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

EXEC sp_dboption N'Logging', N'autoclose', N'false'
GO

EXEC sp_dboption N'Logging', N'bulkcopy', N'false'
GO

EXEC sp_dboption N'Logging', N'trunc. log', N'false'
GO

EXEC sp_dboption N'Logging', N'torn page detection', N'true'
GO

EXEC sp_dboption N'Logging', N'read only', N'false'
GO

EXEC sp_dboption N'Logging', N'dbo use', N'false'
GO

EXEC sp_dboption N'Logging', N'single', N'false'
GO

EXEC sp_dboption N'Logging', N'autoshrink', N'false'
GO

EXEC sp_dboption N'Logging', N'ANSI null default', N'false'
GO

EXEC sp_dboption N'Logging', N'recursive triggers', N'false'
GO

EXEC sp_dboption N'Logging', N'ANSI nulls', N'false'
GO

EXEC sp_dboption N'Logging', N'concat null yields null', N'false'
GO

EXEC sp_dboption N'Logging', N'cursor close on commit', N'false'
GO

EXEC sp_dboption N'Logging', N'default to local cursor', N'false'
GO

EXEC sp_dboption N'Logging', N'quoted identifier', N'false'
GO

EXEC sp_dboption N'Logging', N'ANSI warnings', N'false'
GO

EXEC sp_dboption N'Logging', N'auto create statistics', N'true'
GO

EXEC sp_dboption N'Logging', N'auto update statistics', N'true'
GO

USE [Logging]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Category]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Category](
    [CategoryID] [INT] IDENTITY(1,1) NOT NULL,
    [CategoryName] [nvarchar](64) NOT NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
    [CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CategoryLog]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[CategoryLog](
    [CategoryLogID] [INT] IDENTITY(1,1) NOT NULL,
    [CategoryID] [INT] NOT NULL,
    [LogID] [INT] NOT NULL,
 CONSTRAINT [PK_CategoryLog] PRIMARY KEY CLUSTERED
(
    [CategoryLogID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Log]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Log](
    [LogID] [INT] IDENTITY(1,1) NOT NULL,
    [EventID] [INT] NULL,
    [Priority] [INT] NOT NULL,
    [Severity] [nvarchar](32) NOT NULL,
    [Title] [nvarchar](256) NOT NULL,
    [TIMESTAMP] [datetime] NOT NULL,
    [MachineName] [nvarchar](32) NOT NULL,
    [AppDomainName] [nvarchar](512) NOT NULL,
    [ProcessID] [nvarchar](256) NOT NULL,
    [ProcessName] [nvarchar](512) NOT NULL,
    [ThreadName] [nvarchar](512) NULL,
    [Win32ThreadId] [nvarchar](128) NULL,
    [Message] [nvarchar](1500) NULL,
    [FormattedMessage] [ntext] NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
    [LogID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[InsertCategoryLog]') AND TYPE IN (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE InsertCategoryLog
    @CategoryID INT,
    @LogID INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @CatLogID INT
    SELECT @CatLogID FROM CategoryLog WHERE CategoryID=@CategoryID and LogID = @LogID
    IF @CatLogID IS NULL
    BEGIN
        INSERT INTO CategoryLog (CategoryID, LogID) VALUES(@CategoryID, @LogID)
        RETURN @@IDENTITY
    END
    ELSE RETURN @CatLogID
END
'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddCategory]') AND TYPE IN (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[AddCategory]
    -- Add the parameters for the function here
    @CategoryName nvarchar(64),
    @LogID int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @CatID INT
    SELECT @CatID = CategoryID FROM Category WHERE CategoryName = @CategoryName
    IF @CatID IS NULL
    BEGIN
        INSERT INTO Category (CategoryName) VALUES(@CategoryName)
        SELECT @CatID = @@IDENTITY
    END

    EXEC InsertCategoryLog @CatID, @LogID

    RETURN @CatID
END

'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[ClearLogs]') AND TYPE IN (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE ClearLogs
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM CategoryLog
    DELETE FROM [Log]
    DELETE FROM Category
END
'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[WriteLog]') AND TYPE IN (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



/****** Object:  Stored Procedure dbo.WriteLog    Script Date: 10/1/2004 3:16:36 PM ******/

CREATE PROCEDURE [dbo].[WriteLog]
(
    @EventID int,
    @Priority int,
    @Severity nvarchar(32),
    @Title nvarchar(256),
    @Timestamp datetime,
    @MachineName nvarchar(32),
    @AppDomainName nvarchar(512),
    @ProcessID nvarchar(256),
    @ProcessName nvarchar(512),
    @ThreadName nvarchar(512),
    @Win32ThreadId nvarchar(128),
    @Message nvarchar(1500),
    @FormattedMessage ntext,
    @LogId int OUTPUT
)
AS

    INSERT INTO [Log] (
        EventID,
        Priority,
        Severity,
        Title,
        [Timestamp],
        MachineName,
        AppDomainName,
        ProcessID,
        ProcessName,
        ThreadName,
        Win32ThreadId,
        Message,
        FormattedMessage
    )
    VALUES (
        @EventID,
        @Priority,
        @Severity,
        @Title,
        @Timestamp,
        @MachineName,
        @AppDomainName,
        @ProcessID,
        @ProcessName,
        @ThreadName,
        @Win32ThreadId,
        @Message,
        @FormattedMessage)

    SET @LogID = @@IDENTITY
    RETURN @LogID



'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK_CategoryLog_Category') AND parent_obj = OBJECT_ID(N'[dbo].[CategoryLog]'))
ALTER TABLE [dbo].[CategoryLog]  WITH CHECK ADD  CONSTRAINT [FK_CategoryLog_Category] FOREIGN KEY(  [CategoryID])
REFERENCES [dbo].[Category] (   [CategoryID])
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK_CategoryLog_Log') AND parent_obj = OBJECT_ID(N'[dbo].[CategoryLog]'))
ALTER TABLE [dbo].[CategoryLog]  WITH CHECK ADD  CONSTRAINT [FK_CategoryLog_Log] FOREIGN KEY(   [LogID])
REFERENCES [dbo].[Log] (    [LogID])
GO

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
GO

DECLARE @bErrors AS bit

BEGIN TRANSACTION
SET @bErrors = 0

CREATE NONCLUSTERED INDEX [ixCategoryLog] ON [dbo].[CategoryLog] ([LogID] ASC, [CategoryID] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
  COMMIT TRANSACTION
ELSE
  ROLLBACK TRANSACTION