tutorial pages net asp application asp.net-mvc azure azure-sql-database elmah elmah.mvc

asp.net-mvc - pages - javascript asp net



MVC ELMAH y SQL Azure (1)

Este es el script db que uso para ELMAH DB en SQl Azure:

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

SQL Azure es una versión especial de SQL y hay algunas cosas que no admite.

¿Usó el Asistente para migrar de SQL Azure para migrar su db?

back-story: Utilizamos principalmente AWS para todo (hosting, base de datos, notificaciones, etc.). Ahora, estoy buscando mover el lado de la base de datos a SQL Azure ya que hemos estado recibiendo facturas disparadas en AWS RDS. Entonces, todo lo que intenté hacer fue crear un DB en SQL Azure y actualizar la cadena de conexión para apuntar al nuevo DB. En el pasado, ELMAH (esta implementación específica: https://github.com/alexanderbeletsky/elmah.mvc ) funcionó impecablemente en el pasado.

Situación actual: Acabo de crear un nuevo DB en SQL Azure y noté las diferencias clave de inmediato, es decir, no admitir:

EN [PRIMARIO], LLAVES NO CLASIFICADAS, etc.

Migré mi db fine (por ahora), pero cuando apliqué los scripts actualizados para ELMAH al db e intenté ingresar a la herramienta, ¡recibí errores!

Estoy de alguna manera convencido de que es un problema de DB , porque si <errorLog type="Elmah.SqlErrorLog, Elmah" connectionStringName="DefaultConnection"/> : <errorLog type="Elmah.SqlErrorLog, Elmah" connectionStringName="DefaultConnection"/> que básicamente predetermina ELMAH para almacenar todo localmente, puedo acceder a ELMAH.

¿Alguien consiguió que ELMAH trabajara en SQL Azure? ¿Podría darme el script SQL para generar las tablas y los procedimientos almacenados?