Как-то надо было распарсить контейнер из SysDatabaseLog для SSRS-отчета.
Вот кусок хранимой процедуры.
PHP код:
DECLARE @Data VARBINARY(MAX)
, @FieldId INT
, @FieldType TINYINT
, @NewValue NVARCHAR(MAX)
, @OldValue NVARCHAR(MAX)
, @Marker INT
, @Array INT
, @Id INT
, @Tmp VARBINARY(MAX)
, @CreateDate DATETIME
, @CreateTime INT
, @CreateBy VARCHAR(5)
, @LogRecId BIGINT
, @LogType INT
, @Description VARCHAR(60)
DECLARE DBLCursor CURSOR FOR
SELECT [DBL].[CreatedDate]
, [DBL].[CreatedTime]
, [DBL].[CreatedBy]
, [DBL].[Description]
, [DBL].[LogType]
, [DBL].[LogRecId]
, [DBL].[Data]
FROM SysDataBaseLog AS [DBL]
WHERE [DBL].DataAreaId = N'CLT'
AND [DBL].Table_ = 315
AND [DBL].CreatedDate BETWEEN @FromDate AND @ToDate
DECLARE @DataSet TABLE (
[Id] INT PRIMARY KEY
, [FieldId] INT
, [Array] INT
, [LogType] INT
, [Description] VARCHAR(60)
, [CreatedDate] DATETIME
, [CreatedTime] INT
, [CreatedBy] VARCHAR(5)
, [LogRecId] BIGINT
, [NewValue] NVARCHAR(MAX)
, [OldValue] NVARCHAR(MAX)
)
SET @Id = 0
OPEN DBLCursor
FETCH NEXT FROM DBLCursor
INTO @CreateDate, @CreateTime, @CreateBy, @Description, @LogType, @LogRecId, @Data
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Data IS NOT NULL AND ASCII(SUBSTRING(@Data, 1, 1)) = 0x07 AND ASCII(SUBSTRING(@Data, 2, 1)) = 0xFD)
BEGIN -- Inside container
SET @Marker = 3
WHILE (@Marker <= DATALENGTH(@Data)
AND ASCII(SUBSTRING(@Data, @Marker, 1)) = 0x07
AND ASCII(SUBSTRING(@Data, @Marker+1, 1)) = 0x07
AND ASCII(SUBSTRING(@Data, @Marker+2, 1)) = 0xFD)
BEGIN
SET @Marker = @Marker + 3
SET @Tmp = SUBSTRING(@Data, @Marker, 5)
-- @Tmp[1] = 0x01 (int)
SET @FieldId = ASCII(SUBSTRING(@Tmp, 2, 1)) + 256 * ASCII(SUBSTRING(@Tmp, 3, 1))
SET @Array = ASCII(SUBSTRING(@Tmp, 4, 1)) + 256 * ASCII(SUBSTRING(@Tmp, 5, 1))
SET @Marker = @Marker + 5
SET @FieldType = ASCII(SUBSTRING(@Data, @Marker, 1))
SET @Marker = @Marker + 1
SET @NewValue = dbo.PPOPeekValue(@FieldId, @FieldType, @Data, @Marker)
SET @Marker = @Marker + CASE @FieldType
WHEN 0 THEN DATALENGTH(@NewValue)+2 -- str
WHEN 1 THEN 4 -- int/time
WHEN 2 THEN 10 -- real
WHEN 3 THEN 3 -- date
WHEN 4 THEN 3 -- enum
WHEN 8 THEN DATALENGTH(@NewValue)+2 -- text
WHEN 45 THEN 16 -- guid
WHEN 49 THEN 8 -- int64
END
SET @OldValue = 0
IF ASCII(SUBSTRING(@Data, @Marker, 1)) <> 0xFF
BEGIN
SET @FieldType = ASCII(SUBSTRING(@Data, @Marker, 1))
SET @Marker = @Marker + 1
SET @OldValue = dbo.PPOPeekValue(@FieldId, @FieldType, @Data, @Marker)
SET @Marker = @Marker + CASE @FieldType
WHEN 0 THEN DATALENGTH(@NewValue)+2 -- str
WHEN 1 THEN 4 -- int/time
WHEN 2 THEN 10 -- real
WHEN 3 THEN 3 -- date
WHEN 4 THEN 3 -- enum
WHEN 8 THEN DATALENGTH(@NewValue)+2 -- text
WHEN 45 THEN 16 -- guid
WHEN 49 THEN 8 -- int64
END
END
IF @FieldId <> 61450 -- Skip RecVersion fields
BEGIN
SET @Id = @Id + 1
INSERT INTO @DataSet ([Id], [FieldId], [Array], [CreatedDate], [CreatedTime], [CreatedBy], [Description], [LogType], [LogRecId], [NewValue], [OldValue])
VALUES (@Id, @FieldId, @Array, @CreateDate, @CreateTime, @CreateBy, @Description, @LogType, @LogRecId, @NewValue, @OldValue)
END
-- Skip last byte of container (0xFF)
SET @Marker = @Marker + 1
END
END
FETCH NEXT FROM DBLCursor
INTO @CreateDate, @CreateTime, @CreateBy, @Description, @LogType, @LogRecId, @Data
END
CLOSE DBLCursor
DEALLOCATE DBLCursor