Using SQL Azure for Elmah

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