Pastebin launched a little side project called HostCabi.net, check it out ;-)Don't like ads? PRO users don't see any ads ;-)
Guest

LoggingDatabase.sql

By: sidshetye on Dec 26th, 2012  |  syntax: SQL  |  size: 7.30 KB  |  hits: 46  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. /****** Object:  Database Logging    Script Date: 8/22/2005 ******/
  2. USE [master]
  3. GO
  4.  
  5. IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Logging')
  6.         DROP DATABASE [Logging]
  7. GO
  8.  
  9. CREATE DATABASE [Logging]
  10.  COLLATE SQL_Latin1_General_CP1_CI_AS
  11. GO
  12.  
  13. EXEC sp_dboption N'Logging', N'autoclose', N'false'
  14. GO
  15.  
  16. EXEC sp_dboption N'Logging', N'bulkcopy', N'false'
  17. GO
  18.  
  19. EXEC sp_dboption N'Logging', N'trunc. log', N'false'
  20. GO
  21.  
  22. EXEC sp_dboption N'Logging', N'torn page detection', N'true'
  23. GO
  24.  
  25. EXEC sp_dboption N'Logging', N'read only', N'false'
  26. GO
  27.  
  28. EXEC sp_dboption N'Logging', N'dbo use', N'false'
  29. GO
  30.  
  31. EXEC sp_dboption N'Logging', N'single', N'false'
  32. GO
  33.  
  34. EXEC sp_dboption N'Logging', N'autoshrink', N'false'
  35. GO
  36.  
  37. EXEC sp_dboption N'Logging', N'ANSI null default', N'false'
  38. GO
  39.  
  40. EXEC sp_dboption N'Logging', N'recursive triggers', N'false'
  41. GO
  42.  
  43. EXEC sp_dboption N'Logging', N'ANSI nulls', N'false'
  44. GO
  45.  
  46. EXEC sp_dboption N'Logging', N'concat null yields null', N'false'
  47. GO
  48.  
  49. EXEC sp_dboption N'Logging', N'cursor close on commit', N'false'
  50. GO
  51.  
  52. EXEC sp_dboption N'Logging', N'default to local cursor', N'false'
  53. GO
  54.  
  55. EXEC sp_dboption N'Logging', N'quoted identifier', N'false'
  56. GO
  57.  
  58. EXEC sp_dboption N'Logging', N'ANSI warnings', N'false'
  59. GO
  60.  
  61. EXEC sp_dboption N'Logging', N'auto create statistics', N'true'
  62. GO
  63.  
  64. EXEC sp_dboption N'Logging', N'auto update statistics', N'true'
  65. GO
  66.  
  67. USE [Logging]
  68. GO
  69.  
  70. SET ANSI_NULLS ON
  71. GO
  72. SET QUOTED_IDENTIFIER ON
  73. GO
  74. IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Category]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  75. BEGIN
  76. CREATE TABLE [dbo].[Category](
  77.         [CategoryID] [INT] IDENTITY(1,1) NOT NULL,
  78.         [CategoryName] [nvarchar](64) NOT NULL,
  79.  CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
  80. (
  81.         [CategoryID] ASC
  82. ) ON [PRIMARY]
  83. ) ON [PRIMARY]
  84. END
  85. GO
  86. SET ANSI_NULLS OFF
  87. GO
  88. SET QUOTED_IDENTIFIER OFF
  89. GO
  90. SET ANSI_NULLS ON
  91. GO
  92. SET QUOTED_IDENTIFIER ON
  93. GO
  94. IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CategoryLog]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  95. BEGIN
  96. CREATE TABLE [dbo].[CategoryLog](
  97.         [CategoryLogID] [INT] IDENTITY(1,1) NOT NULL,
  98.         [CategoryID] [INT] NOT NULL,
  99.         [LogID] [INT] NOT NULL,
  100.  CONSTRAINT [PK_CategoryLog] PRIMARY KEY CLUSTERED
  101. (
  102.         [CategoryLogID] ASC
  103. ) ON [PRIMARY]
  104. ) ON [PRIMARY]
  105. END
  106. GO
  107. SET ANSI_NULLS OFF
  108. GO
  109. SET QUOTED_IDENTIFIER OFF
  110. GO
  111. SET ANSI_NULLS ON
  112. GO
  113. SET QUOTED_IDENTIFIER ON
  114. GO
  115. IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Log]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  116. BEGIN
  117. CREATE TABLE [dbo].[Log](
  118.         [LogID] [INT] IDENTITY(1,1) NOT NULL,
  119.         [EventID] [INT] NULL,
  120.         [Priority] [INT] NOT NULL,
  121.         [Severity] [nvarchar](32) NOT NULL,
  122.         [Title] [nvarchar](256) NOT NULL,
  123.         [TIMESTAMP] [datetime] NOT NULL,
  124.         [MachineName] [nvarchar](32) NOT NULL,
  125.         [AppDomainName] [nvarchar](512) NOT NULL,
  126.         [ProcessID] [nvarchar](256) NOT NULL,
  127.         [ProcessName] [nvarchar](512) NOT NULL,
  128.         [ThreadName] [nvarchar](512) NULL,
  129.         [Win32ThreadId] [nvarchar](128) NULL,
  130.         [Message] [nvarchar](1500) NULL,
  131.         [FormattedMessage] [ntext] NULL,
  132.  CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
  133. (
  134.         [LogID] ASC
  135. ) ON [PRIMARY]
  136. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  137. END
  138. GO
  139. SET ANSI_NULLS OFF
  140. GO
  141. SET QUOTED_IDENTIFIER OFF
  142. GO
  143. SET ANSI_NULLS ON
  144. GO
  145. SET QUOTED_IDENTIFIER ON
  146. GO
  147. IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[InsertCategoryLog]') AND TYPE IN (N'P', N'PC'))
  148. BEGIN
  149. EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE InsertCategoryLog
  150.         @CategoryID INT,
  151.         @LogID INT
  152. AS
  153. BEGIN
  154.         SET NOCOUNT ON;
  155.  
  156.         DECLARE @CatLogID INT
  157.         SELECT @CatLogID FROM CategoryLog WHERE CategoryID=@CategoryID and LogID = @LogID
  158.         IF @CatLogID IS NULL
  159.         BEGIN
  160.                 INSERT INTO CategoryLog (CategoryID, LogID) VALUES(@CategoryID, @LogID)
  161.                 RETURN @@IDENTITY
  162.         END
  163.         ELSE RETURN @CatLogID
  164. END
  165. '
  166. END
  167. GO
  168. SET ANSI_NULLS OFF
  169. GO
  170. SET QUOTED_IDENTIFIER OFF
  171. GO
  172. SET ANSI_NULLS ON
  173. GO
  174. SET QUOTED_IDENTIFIER ON
  175. GO
  176. IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddCategory]') AND TYPE IN (N'P', N'PC'))
  177. BEGIN
  178. EXEC dbo.sp_executesql @statement = N'
  179.  
  180. CREATE PROCEDURE [dbo].[AddCategory]
  181.         -- Add the parameters for the function here
  182.         @CategoryName nvarchar(64),
  183.         @LogID int
  184. AS
  185. BEGIN
  186.         SET NOCOUNT ON;
  187.    DECLARE @CatID INT
  188.         SELECT @CatID = CategoryID FROM Category WHERE CategoryName = @CategoryName
  189.         IF @CatID IS NULL
  190.         BEGIN
  191.                 INSERT INTO Category (CategoryName) VALUES(@CategoryName)
  192.                 SELECT @CatID = @@IDENTITY
  193.         END
  194.  
  195.         EXEC InsertCategoryLog @CatID, @LogID
  196.  
  197.         RETURN @CatID
  198. END
  199.  
  200. '
  201. END
  202. GO
  203. SET ANSI_NULLS OFF
  204. GO
  205. SET QUOTED_IDENTIFIER OFF
  206. GO
  207. SET ANSI_NULLS ON
  208. GO
  209. SET QUOTED_IDENTIFIER ON
  210. GO
  211. IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[ClearLogs]') AND TYPE IN (N'P', N'PC'))
  212. BEGIN
  213. EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE ClearLogs
  214. AS
  215. BEGIN
  216.         SET NOCOUNT ON;
  217.  
  218.         DELETE FROM CategoryLog
  219.         DELETE FROM [Log]
  220.    DELETE FROM Category
  221. END
  222. '
  223. END
  224. GO
  225. SET ANSI_NULLS OFF
  226. GO
  227. SET QUOTED_IDENTIFIER OFF
  228. GO
  229. SET ANSI_NULLS ON
  230. GO
  231. SET QUOTED_IDENTIFIER ON
  232. GO
  233. IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[WriteLog]') AND TYPE IN (N'P', N'PC'))
  234. BEGIN
  235. EXEC dbo.sp_executesql @statement = N'
  236.  
  237.  
  238.  
  239. /****** Object:  Stored Procedure dbo.WriteLog    Script Date: 10/1/2004 3:16:36 PM ******/
  240.  
  241. CREATE PROCEDURE [dbo].[WriteLog]
  242. (
  243.         @EventID int,
  244.         @Priority int,
  245.         @Severity nvarchar(32),
  246.         @Title nvarchar(256),
  247.         @Timestamp datetime,
  248.         @MachineName nvarchar(32),
  249.         @AppDomainName nvarchar(512),
  250.         @ProcessID nvarchar(256),
  251.         @ProcessName nvarchar(512),
  252.         @ThreadName nvarchar(512),
  253.         @Win32ThreadId nvarchar(128),
  254.         @Message nvarchar(1500),
  255.         @FormattedMessage ntext,
  256.         @LogId int OUTPUT
  257. )
  258. AS
  259.  
  260.         INSERT INTO [Log] (
  261.                 EventID,
  262.                 Priority,
  263.                 Severity,
  264.                 Title,
  265.                 [Timestamp],
  266.                 MachineName,
  267.                 AppDomainName,
  268.                 ProcessID,
  269.                 ProcessName,
  270.                 ThreadName,
  271.                 Win32ThreadId,
  272.                 Message,
  273.                 FormattedMessage
  274.         )
  275.         VALUES (
  276.                 @EventID,
  277.                 @Priority,
  278.                 @Severity,
  279.                 @Title,
  280.                 @Timestamp,
  281.                 @MachineName,
  282.                 @AppDomainName,
  283.                 @ProcessID,
  284.                 @ProcessName,
  285.                 @ThreadName,
  286.                 @Win32ThreadId,
  287.                 @Message,
  288.                 @FormattedMessage)
  289.  
  290.         SET @LogID = @@IDENTITY
  291.         RETURN @LogID
  292.  
  293.  
  294.  
  295. '
  296. END
  297. GO
  298. SET ANSI_NULLS OFF
  299. GO
  300. SET QUOTED_IDENTIFIER OFF
  301. GO
  302. IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK_CategoryLog_Category') AND parent_obj = OBJECT_ID(N'[dbo].[CategoryLog]'))
  303. ALTER TABLE [dbo].[CategoryLog]  WITH CHECK ADD  CONSTRAINT [FK_CategoryLog_Category] FOREIGN KEY(      [CategoryID])
  304. REFERENCES [dbo].[Category] (   [CategoryID])
  305. GO
  306. IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK_CategoryLog_Log') AND parent_obj = OBJECT_ID(N'[dbo].[CategoryLog]'))
  307. ALTER TABLE [dbo].[CategoryLog]  WITH CHECK ADD  CONSTRAINT [FK_CategoryLog_Log] FOREIGN KEY(   [LogID])
  308. REFERENCES [dbo].[Log] (        [LogID])
  309. GO
  310.  
  311. SET QUOTED_IDENTIFIER ON
  312. SET ARITHABORT ON
  313. SET CONCAT_NULL_YIELDS_NULL ON
  314. SET ANSI_NULLS ON
  315. SET ANSI_PADDING ON
  316. SET ANSI_WARNINGS ON
  317. SET NUMERIC_ROUNDABORT OFF
  318. GO
  319.  
  320. DECLARE @bErrors AS bit
  321.  
  322. BEGIN TRANSACTION
  323. SET @bErrors = 0
  324.  
  325. CREATE NONCLUSTERED INDEX [ixCategoryLog] ON [dbo].[CategoryLog] ([LogID] ASC, [CategoryID] ASC )
  326. IF( @@error <> 0 ) SET @bErrors = 1
  327.  
  328. IF( @bErrors = 0 )
  329.   COMMIT TRANSACTION
  330. ELSE
  331.   ROLLBACK TRANSACTION