3 thoughts on “PROC: Convert LSN from hex to decimal for fn_dblog()

  1. Hi David,
    Here’s a version of your proc without dynamic SQL & an optional colon feature, plus a scalar-valued function w/ optional colons
    ————————————————————————————
    –To Test
    DECLARE @lsn nvarchar(22) = N’000000a4:000000d8:0001′
    EXEC [dbo].[ConvertLSNHexToDec] @lsn;EXEC [dbo].[ConvertLSNHexToDec_2] @lsn;EXEC [dbo].[ConvertLSNHexToDec_2] @lsn, 0
    SELECT [dbo].[GetIntegerStringFromHex](@lsn,DEFAULT) AS ‘Int_String_From_Hex’;SELECT [dbo].[GetIntegerStringFromHex](@lsn,0) AS ‘Int_String_From_Hex_No_Colons’
    SET @lsn = ‘000000d5:00000140:0001’
    EXEC [dbo].[ConvertLSNHexToDec] @lsn;EXEC [dbo].[ConvertLSNHexToDec_2] @lsn;EXEC [dbo].[ConvertLSNHexToDec_2] @lsn, 0
    SELECT [dbo].[GetIntegerStringFromHex](@lsn,DEFAULT) AS ‘Int_String_From_Hex’;SELECT [dbo].[GetIntegerStringFromHex](@lsn,0) AS ‘Int_String_From_Hex_No_Colons’

    ————————————————————————————
    IF (SELECT OBJECT_ID(‘dbo.ConvertLSNHexToDec_2’)) IS NULL
    BEGIN
    EXEC (‘CREATE PROCEDURE dbo.ConvertLSNHexToDec_2
    AS
    PRINT ”STUB VERSION – Replace with actual procedure.”;
    ‘)
    END
    GO

    ALTER PROCEDURE dbo.ConvertLSNHexToDec_2
    (@lsn nvarchar(22) = N’000000a4:000000d8:0001′,@IncludeColons BIT = 1)
    /*
    DESCRIPTION:
    Converts the LSN in fn_dblog() from a three part hex to three part decimal,
    so you can pass it back to fn_dblog() as a parameter.

    TEST EXEC:
    DECLARE @lsn nvarchar(22) = N’000000a4:000000d8:0001’
    EXEC [dbo].[ConvertLSNHexToDec_2] @lsn

    */
    as
    — If the ‘0x’ marker is NOT present:
    SELECT
    RIGHT(replicate(‘0’,8) + CAST(CONVERT(INT, CONVERT(VARBINARY, left(@lsn,8), 2)) AS NVARCHAR(8)),8)
    + CASE WHEN @IncludeColons = 1 THEN ‘:’ ELSE ” END + RIGHT(replicate(‘0’,8) + CAST(CONVERT(INT, CONVERT(VARBINARY, substring(@lsn,10,8), 2)) AS NVARCHAR(8)),8)
    + CASE WHEN @IncludeColons = 1 THEN ‘:’ ELSE ” END + right(replicate(‘0′,4) + CAST(CONVERT(INT, CONVERT(VARBINARY, right(@lsn,4), 2)) AS NVARCHAR(4)),4)

    go

    CREATE FUNCTION [dbo].[GetIntegerStringFromHex] (@lsn nvarchar(22) = N’000000a4:000000d8:0001′,@IncludeColons BIT = 1)
    /*
    DESCRIPTION:
    Converts the LSN in fn_dblog() from a three part hex to three part decimal,
    so you can pass it back to fn_dblog() as a parameter.

    TEST EXEC:
    DECLARE @lsn nvarchar(22) = N’000000a4:000000d8:0001’;
    SELECT [dbo].[GetIntegerStringFromHex](@lsn,DEFAULT) AS ‘Int_String_From_Hex’;
    SELECT [dbo].[GetIntegerStringFromHex](@lsn,0) AS ‘Int_String_From_Hex_No_Colon’;

    */
    RETURNS NVARCHAR(22)
    AS
    begin
    RETURN
    (select
    — If the ‘0x’ marker is NOT present:
    RIGHT(replicate(‘0’,8) + CAST(CONVERT(INT, CONVERT(VARBINARY, left(@lsn,8), 2)) AS NVARCHAR(8)),8)
    + CASE WHEN @IncludeColons = 1 THEN ‘:’ ELSE ” END + RIGHT(replicate(‘0’,8) + CAST(CONVERT(INT, CONVERT(VARBINARY, substring(@lsn,10,8), 2)) AS NVARCHAR(8)),8)
    + CASE WHEN @IncludeColons = 1 THEN ‘:’ ELSE ” END + right(replicate(‘0’,4) + CAST(CONVERT(INT, CONVERT(VARBINARY, right(@lsn,4), 2)) AS NVARCHAR(4)),4)
    )
    END

    Like

Please Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s