Data Set debugging support script

Microsoft.SystemCenter.DataWarehouse.Script.DatasetDebuggingSupport (DataWarehouseScript)

Creates database objects necessary to produce debug output for a data set

Element properties:

Source Code:

<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Script.DatasetDebuggingSupport" Accessibility="Public">

  <Install> 

                    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DebugMessage' AND TABLE_SCHEMA = 'dbo')

BEGIN

  DROP TABLE dbo.DebugMessage

END

GO



CREATE TABLE dbo.DebugMessage

(                               

      DebugMessageRowId       bigint      NOT NULL    IDENTITY (1, 1)

     ,ProcessId               int         NOT NULL    DEFAULT (@@SPID)

     ,DatasetId               uniqueidentifier NOT NULL

     ,MessageLevel            tinyint     NOT NULL

     ,MessageDateTime         datetime    NOT NULL    DEFAULT (GETUTCDATE())

     ,MessageText             nvarchar(max) NOT NULL

     ,OperationDurationMs     bigint      NULL

 

     ,CONSTRAINT PK_DebugMessageRowId PRIMARY KEY CLUSTERED (DebugMessageRowId)

)

GO



CREATE INDEX IX_DebugMessage_DatasetIdMessageDataTime ON dbo.DebugMessage(DatasetId, MessageDateTime)

GO



EXEC DomainTableRegisterIndexOptimization 'DebugMessage'

GO



                    IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DebugMessageInsert')

	BEGIN

		EXECUTE ('CREATE PROCEDURE dbo.DebugMessageInsert AS RETURN 1')

	END

GO



ALTER PROCEDURE dbo.DebugMessageInsert

   @DatasetId   uniqueidentifier

  ,@MessageLevel int

  ,@MessageText nvarchar(max)

  ,@OperationDurationMs bigint = NULL

AS

BEGIN

  SET NOCOUNT ON

  

  DELETE DebugMessage

  WHERE MessageDateTime &lt; DATEADD(day, -7, GETUTCDATE())

  

  INSERT DebugMessage (

     DatasetId

    ,MessageLevel

    ,MessageText

    ,OperationDurationMs

  )

  VALUES (

     @DatasetId

    ,@MessageLevel

    ,@MessageText

    ,@OperationDurationMs

  )

END

GO



                    </Install>

  <Uninstall>

                    IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DebugMessageInsert')

	BEGIN

		EXECUTE ('DROP PROCEDURE dbo.DebugMessageInsert')

	END

GO



IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DebugMessage' AND TABLE_SCHEMA = 'dbo')

BEGIN

  DROP TABLE dbo.DebugMessage

END

GO



                    </Uninstall>

  <Upgrade/>

</DataWarehouseScript>