0 votes
1 view
in Azure by (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:

 <errorLog type="Elmah.SqlErrorLog, Elmah" connectionStringName="DefaultConnection"/> 

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

0 votes
by (47.2k points)

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

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...