Created
February 19, 2016 17:37
-
-
Save emorin/893ff595ef5d0a7da3b1 to your computer and use it in GitHub Desktop.
Elmah SQL Azure Script
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--~Changing index [dbo].[ELMAH_Error].PK_ELMAH_Error to a clustered index. You may want to pick a different index to cluster on. | |
SET ANSI_NULLS ON | |
SET QUOTED_IDENTIFIER ON | |
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [ELMAH_Error]') AND type in (N'U')) | |
BEGIN | |
CREATE TABLE [dbo].[ELMAH_Error]( | |
[ErrorId] [uniqueidentifier] NOT NULL, | |
[Application] [nvarchar](60) NOT NULL, | |
[Host] [nvarchar](50) NOT NULL, | |
[Type] [nvarchar](100) NOT NULL, | |
[Source] [nvarchar](60) NOT NULL, | |
[Message] [nvarchar](500) NOT NULL, | |
[User] [nvarchar](50) NOT NULL, | |
[StatusCode] [int] NOT NULL, | |
[TimeUtc] [datetime] NOT NULL, | |
[Sequence] [int] IDENTITY(1,1) NOT NULL, | |
[AllXml] [nvarchar](max) NOT NULL, | |
CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY CLUSTERED | |
( | |
[ErrorId] ASC | |
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) | |
) | |
END | |
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ELMAH_Error]') AND name = N'IX_ELMAH_Error_App_Time_Seq') | |
CREATE NONCLUSTERED INDEX [IX_ELMAH_Error_App_Time_Seq] ON [dbo].[ELMAH_Error] | |
( | |
[Application] ASC, | |
[TimeUtc] DESC, | |
[Sequence] DESC | |
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) | |
GO | |
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_ELMAH_Error_ErrorId]') AND type = 'D') | |
BEGIN | |
ALTER TABLE [dbo].[ELMAH_Error] ADD CONSTRAINT [DF_ELMAH_Error_ErrorId] DEFAULT (newid()) FOR [ErrorId] | |
END | |
GO | |
SET ANSI_NULLS ON | |
SET QUOTED_IDENTIFIER ON | |
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ELMAH_GetErrorsXml]') AND type in (N'P', N'PC')) | |
BEGIN | |
EXEC dbo.sp_executesql @statement = N' | |
CREATE PROCEDURE [dbo].[ELMAH_GetErrorsXml] | |
( | |
@Application NVARCHAR(60), | |
@PageIndex INT = 0, | |
@PageSize INT = 15, | |
@TotalCount INT OUTPUT | |
) | |
AS | |
SET NOCOUNT ON | |
DECLARE @FirstTimeUTC DATETIME | |
DECLARE @FirstSequence INT | |
DECLARE @StartRow INT | |
DECLARE @StartRowIndex INT | |
SELECT | |
@TotalCount = COUNT(1) | |
FROM | |
[ELMAH_Error] | |
WHERE | |
[Application] = @Application | |
-- Get the ID of the first error for the requested page | |
SET @StartRowIndex = @PageIndex * @PageSize + 1 | |
IF @StartRowIndex <= @TotalCount | |
BEGIN | |
SET ROWCOUNT @StartRowIndex | |
SELECT | |
@FirstTimeUTC = [TimeUtc], | |
@FirstSequence = [Sequence] | |
FROM | |
[ELMAH_Error] | |
WHERE | |
[Application] = @Application | |
ORDER BY | |
[TimeUtc] DESC, | |
[Sequence] DESC | |
END | |
ELSE | |
BEGIN | |
SET @PageSize = 0 | |
END | |
-- Now set the row count to the requested page size and get | |
-- all records below it for the pertaining application. | |
SET ROWCOUNT @PageSize | |
SELECT | |
errorId = [ErrorId], | |
application = [Application], | |
host = [Host], | |
type = [Type], | |
source = [Source], | |
message = [Message], | |
[user] = [User], | |
statusCode = [StatusCode], | |
time = CONVERT(VARCHAR(50), [TimeUtc], 126) + ''Z'' | |
FROM | |
[ELMAH_Error] error | |
WHERE | |
[Application] = @Application | |
AND | |
[TimeUtc] <= @FirstTimeUTC | |
AND | |
[Sequence] <= @FirstSequence | |
ORDER BY | |
[TimeUtc] DESC, | |
[Sequence] DESC | |
FOR | |
XML AUTO | |
' | |
END | |
GO | |
SET ANSI_NULLS ON | |
SET QUOTED_IDENTIFIER ON | |
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ELMAH_GetErrorXml]') AND type in (N'P', N'PC')) | |
BEGIN | |
EXEC dbo.sp_executesql @statement = N' | |
CREATE PROCEDURE [dbo].[ELMAH_GetErrorXml] | |
( | |
@Application NVARCHAR(60), | |
@ErrorId UNIQUEIDENTIFIER | |
) | |
AS | |
SET NOCOUNT ON | |
SELECT | |
[AllXml] | |
FROM | |
[ELMAH_Error] | |
WHERE | |
[ErrorId] = @ErrorId | |
AND | |
[Application] = @Application | |
' | |
END | |
GO | |
SET ANSI_NULLS ON | |
SET QUOTED_IDENTIFIER ON | |
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [ELMAH_LogError]') AND type in (N'P', N'PC')) | |
BEGIN | |
EXEC dbo.sp_executesql @statement = N' | |
CREATE PROCEDURE [dbo].[ELMAH_LogError] | |
( | |
@ErrorId UNIQUEIDENTIFIER, | |
@Application NVARCHAR(60), | |
@Host NVARCHAR(30), | |
@Type NVARCHAR(100), | |
@Source NVARCHAR(60), | |
@Message NVARCHAR(500), | |
@User NVARCHAR(50), | |
@AllXml NVARCHAR(MAX), | |
@StatusCode INT, | |
@TimeUtc DATETIME | |
) | |
AS | |
SET NOCOUNT ON | |
INSERT | |
INTO | |
[ELMAH_Error] | |
( | |
[ErrorId], | |
[Application], | |
[Host], | |
[Type], | |
[Source], | |
[Message], | |
[User], | |
[AllXml], | |
[StatusCode], | |
[TimeUtc] | |
) | |
VALUES | |
( | |
@ErrorId, | |
@Application, | |
@Host, | |
@Type, | |
@Source, | |
@Message, | |
@User, | |
@AllXml, | |
@StatusCode, | |
@TimeUtc | |
) | |
' | |
END | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment