/******
Object: Database Logging Script Date: 8/22/2005 ******/
USE[master]
GO
IFEXISTS
(SELECT
name FROM
master.dbo.sysdatabases WHERE
name =
N‘Logging‘)
DROPDATABASE[Logging]
GO
CREATEDATABASE[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
IFNOTEXISTS (SELECT*FROM
dbo.sysobjects WHERE id =OBJECT_ID(N‘[dbo].[Category]‘) ANDOBJECTPROPERTY(id, N‘IsUserTable‘) =1)
BEGIN
CREATETABLE[dbo].[Category](
[CategoryID][int]IDENTITY(1,1)
NOTNULL,
[CategoryName][nvarchar](64) NOTNULL,
CONSTRAINT[PK_Categories]PRIMARYKEYCLUSTERED
(
[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
IFNOTEXISTS (SELECT*FROM
dbo.sysobjects WHERE id =OBJECT_ID(N‘[dbo].[CategoryLog]‘) ANDOBJECTPROPERTY(id, N‘IsUserTable‘) =1)
BEGIN
CREATETABLE[dbo].[CategoryLog](
[CategoryLogID][int]IDENTITY(1,1)
NOTNULL,
[CategoryID][int]NOTNULL,
[LogID][int]NOTNULL,
CONSTRAINT[PK_CategoryLog]PRIMARYKEYCLUSTERED
(
[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
IFNOTEXISTS (SELECT*FROM
dbo.sysobjects WHERE id =OBJECT_ID(N‘[dbo].[Log]‘) ANDOBJECTPROPERTY(id, N‘IsUserTable‘) =1)
BEGIN
CREATETABLE[dbo].[Log](
[LogID][int]IDENTITY(1,1)
NOTNULL,
[EventID][int]NULL,
[Priority][int]NOTNULL,
[Severity][nvarchar](32) NOTNULL,
[Title][nvarchar](256) NOTNULL,
[Timestamp][datetime]NOTNULL,
[MachineName][nvarchar](32) NOTNULL,
[AppDomainName][nvarchar](512) NOTNULL,
[ProcessID][nvarchar](256) NOTNULL,
[ProcessName][nvarchar](512) NOTNULL,
[ThreadName][nvarchar](512) NULL,
[Win32ThreadId][nvarchar](128) NULL,
[Message][nvarchar](1500) NULL,
[FormattedMessage][ntext]NULL,
CONSTRAINT[PK_Log]PRIMARYKEYCLUSTERED
(
[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
IFNOTEXISTS (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
IFNOTEXISTS (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
IFNOTEXISTS (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
IFNOTEXISTS (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
IFNOTEXISTS (SELECT*FROM
sysobjects WHERE
id =OBJECT_ID(N‘FK_CategoryLog_Category‘) AND
parent_obj =OBJECT_ID(N‘[dbo].[CategoryLog]‘))
ALTERTABLE[dbo].[CategoryLog]WITHCHECKADDCONSTRAINT[FK_CategoryLog_Category]FOREIGNKEY( [CategoryID])
REFERENCES[dbo].[Category] ( [CategoryID])
GO
IFNOTEXISTS (SELECT*FROM
sysobjects WHERE
id =OBJECT_ID(N‘FK_CategoryLog_Log‘) AND
parent_obj =OBJECT_ID(N‘[dbo].[CategoryLog]‘))
ALTERTABLE[dbo].[CategoryLog]WITHCHECKADDCONSTRAINT[FK_CategoryLog_Log]FOREIGNKEY( [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@bErrorsasbit
BEGINTRANSACTION
SET@bErrors=0
CREATENONCLUSTEREDINDEX[ixCategoryLog]ON[dbo].[CategoryLog] ([LogID]ASC,
[CategoryID]ASC
)
IF(
@@error<>0
) SET@bErrors=1
IF( @bErrors=0
)
COMMITTRANSACTION
ELSE
ROLLBACKTRANSACTION