Using SQL Azure for Elmah

Source: Internet
Author: User
Tags rowcount

The MSDN docs contain the list of T-SQL that are either partially supported or not supported. For example see the following location for CREATE table-http://msdn.microsoft.com/en-us/library/ee336258.aspx

In Elmah's case, the filegroup are not supported on ' CREATE TABLE ' DDL.

Use the script below for SQL Azure instead.

/* ------------------------------------------------------------------------
TABLES
------------------------------------------------------------------------ */
IF EXISTS (SELECT * from sysobjects WHERE xtype = ' U ' and name = ' Elmah_error ')
DROP TABLE [dbo]. [Elmah_error]
GO
CREATE TABLE [dbo]. [Elmah_error]
(
[ErrorID] uniqueidentifier not NULL,
[Application] NVARCHAR (COLLATE) sql_latin1_general_cp1_ci_as not NULL,
[Host] NVARCHAR (COLLATE) sql_latin1_general_cp1_ci_as not NULL,
[Type] NVARCHAR (+) COLLATE sql_latin1_general_cp1_ci_as not NULL,
[Source] NVARCHAR (COLLATE) sql_latin1_general_cp1_ci_as not NULL,
[Message] NVARCHAR (+) COLLATE sql_latin1_general_cp1_ci_as not NULL,
[User] NVARCHAR (COLLATE) sql_latin1_general_cp1_ci_as not NULL,
[StatusCode] INT not NULL,
[TIMEUTC] DATETIME not NULL,
[Sequence] INT IDENTITY (1, 1) not NULL,
[Allxml] NTEXT COLLATE SQL_Latin1_General_CP1_CI_AS not NULL
)

GO

ALTER TABLE [dbo]. [Elmah_error] With NOCHECK ADD
CONSTRAINT [Pk_elmah_error] PRIMARY KEY ([ErrorID])
GO

ALTER TABLE [dbo]. [Elmah_error] ADD
CONSTRAINT [Df_elmah_error_errorid] DEFAULT (NEWID ()) for [ErrorID]
GO

CREATE nonclustered INDEX [ix_elmah_error_app_time_seq] on [dbo]. [Elmah_error]
(
[Application] Asc
[TIMEUTC] DESC,
[Sequence] DESC
)
GO

/* ------------------------------------------------------------------------
STORED procedures
------------------------------------------------------------------------ */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS on
GO

IF EXISTS (SELECT * from sysobjects WHERE xtype = ' P ' and name = ' Elmah_geterrorxml ')
DROP PROCEDURE [dbo]. [Elmah_geterrorxml]
GO
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

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS on
GO


IF EXISTS (SELECT * from sysobjects WHERE xtype = ' P ' and name = ' Elmah_geterrorsxml ')
DROP PROCEDURE [dbo]. [Elmah_geterrorsxml]
GO
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 (), [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

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS on
GO


IF EXISTS (SELECT * from sysobjects WHERE xtype = ' P ' and name = ' Elmah_logerror ')
DROP PROCEDURE [dbo]. [Elmah_logerror]
GO
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 NTEXT,
@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
)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS on
GO

Using SQL Azure for Elmah

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.