MVC ELMAH and SQL Azure

Question

asked Jul 6, 2019 in Azure by Vaibhav Ameta (17.6k points)

back-story: We mainly use AWS for everything (hosting, database, notifications, etc.). Now, I'm looking at moving the database side to SQL Azure since we've been getting crazy bills on AWS RDS. So all I tried to do was create a DB in SQL Azure and update the connection string to point to the new DB. In the past, ELMAH (this specific implementation: https://github.com/alexanderbeletsky/elmah.mvc) worked flawlessly in the past.

current situation: I just created a new DB in SQL Azure and noticed key differences right away namely on not supporting:

ON [PRIMARY], NONCLUSTERED KEYS, etc.

I migrated my DB fine (for now), but when I applied the updated scripts for ELMAH to the DB and tried to go into the tool, I get errors!

I'm somehow convinced that it's a DB problem, because if I remove:

which basically defaults ELMAH to store everything locally, I get to access ELMAH.

Anybody got ELMAH to work on SQL Azure? Could you give me the SQL script to generate the tables and stored procedures?

1 Answer

Ashok · Answer 1 · 2019-07-08T06:32:43+0000

Use this DB script file on Azure SQL

--~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

MVC ELMAH and SQL Azure

1 Answer

Related questions

Browse Categories