Converting between binary hex and GUID (uniqueidentifier)

  • jay-h

    SSCoach

    Points: 18816

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    ...

    -- FORTRAN manual for Xerox Computers --

  • Lynn Pettis

    SSC Guru

    Points: 442359

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    Well, there is this:

    declare @TestUI uniqueidentifier = '43367830-4437-3543-3132-304538434139';

    select @TestUI, '43367830-4437-3543-3132-304538434139';

  • Y.B.

    SSChampion

    Points: 11549

    This seems to work fine for me unless I'm missing something...

    DECLARE @GUID UNIQUEIDENTIFIER, @binary VARBINARY(16)

    SELECT @GUID = NEWID()

    SELECT @binary = CAST(@guid AS VARBINARY(16))

    SELECT
    @GUID AS Here,
    @binary AS There,
    CAST(@binary AS UNIQUEIDENTIFIER) AS BackAgain


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • Lynn Pettis

    SSC Guru

    Points: 442359

    Lynn Pettis - Thursday, November 8, 2018 1:02 PM

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    Well, there is this:

    declare @TestUI uniqueidentifier = '43367830-4437-3543-3132-304538434139';

    select @TestUI, '43367830-4437-3543-3132-304538434139';

    And after playing a little more:

    declare @TestUI uniqueidentifier = '43367830-4437-3543-3132-304538434139'
       ,@TestBin binary(16) = 0x6C7DC5120E8CA94F8224D0198C9D7CF2;

    select @TestUI, '43367830-4437-3543-3132-304538434139';

    select cast(@TestUI as varbinary(16)), cast(cast(@TestUI as varbinary(16)) as uniqueidentifier);

    select
    @TestBin
    , convert(varchar(36),@TestBin,2)
    , stuff(stuff(stuff(stuff(convert(varchar(36),@TestBin,2),21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-')
    , cast(stuff(stuff(stuff(stuff(convert(varchar(36),@TestBin,2),21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-') as uniqueidentifier)

  • Jeff Moden

    SSC Guru

    Points: 997104

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    This will do it for you.  I also took the liberty of splitting the desired value out of the original message.


    --===== This isn't part of the solution.  We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
         -- converts the text to a BINARY(16) and then converts that to the final GUID.
     SELECT CONVERT(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    ;

    For more information on the convert format of "1" that I used, search for "Binary Style" in the article at the following link...
    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Lynn Pettis

    SSC Guru

    Points: 442359

    Jeff Moden - Thursday, November 8, 2018 10:05 PM

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    This will do it for you.  I also took the liberty of splitting the desired value out of the original message.


    --===== This isn't part of the solution.  We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
         -- converts the text to a BINARY(16) and then converts that to the final GUID.
     SELECT CONVERT(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    ;

    For more information on the convert format of "1" that I used, search for "Binary Style" in the article at the following link...
    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

    Actually, Jeff, your code doesn't quite work.  Look at the following:

    --===== This isn't part of the solution. We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
      -- converts the text to a BINARY(16) and then converts that to the final GUID.
    SELECT SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), convert(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    select SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), cast(stuff(stuff(stuff(stuff(convert(varchar(36),convert(varbinary(16),substring(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1),2),21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-') as uniqueidentifier);
    ;

  • jay-h

    SSCoach

    Points: 18816

    Thanks for the input. Monday I hope to dig back into this.

    ...

    -- FORTRAN manual for Xerox Computers --

  • Jeff Moden

    SSC Guru

    Points: 997104

    Lynn Pettis - Friday, November 9, 2018 8:44 AM

    Jeff Moden - Thursday, November 8, 2018 10:05 PM

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    This will do it for you.  I also took the liberty of splitting the desired value out of the original message.


    --===== This isn't part of the solution.  We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
         -- converts the text to a BINARY(16) and then converts that to the final GUID.
     SELECT CONVERT(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    ;

    For more information on the convert format of "1" that I used, search for "Binary Style" in the article at the following link...
    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

    Actually, Jeff, your code doesn't quite work.  Look at the following:

    --===== This isn't part of the solution. We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
      -- converts the text to a BINARY(16) and then converts that to the final GUID.
    SELECT SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), convert(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    select SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), cast(stuff(stuff(stuff(stuff(convert(varchar(36),convert(varbinary(16),substring(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1),2),21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-') as uniqueidentifier);
    ;

    Ah... be careful now, Lynn.  You have to remember that the value of 0x6C7DC5120E8CA94F8224D0198C9D7CF2 is NOT a GUID.  It's the binary (hex) representation for the GUID.  The code I posted is correct.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Lynn Pettis

    SSC Guru

    Points: 442359

    Jeff Moden - Friday, November 9, 2018 8:13 PM

    Lynn Pettis - Friday, November 9, 2018 8:44 AM

    Jeff Moden - Thursday, November 8, 2018 10:05 PM

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    This will do it for you.  I also took the liberty of splitting the desired value out of the original message.


    --===== This isn't part of the solution.  We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
         -- converts the text to a BINARY(16) and then converts that to the final GUID.
     SELECT CONVERT(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    ;

    For more information on the convert format of "1" that I used, search for "Binary Style" in the article at the following link...
    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

    Actually, Jeff, your code doesn't quite work.  Look at the following:

    --===== This isn't part of the solution. We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
      -- converts the text to a BINARY(16) and then converts that to the final GUID.
    SELECT SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), convert(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    select SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), cast(stuff(stuff(stuff(stuff(convert(varchar(36),convert(varbinary(16),substring(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1),2),21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-') as uniqueidentifier);
    ;

    Ah... be careful now, Lynn.  You have to remember that the value of 0x6C7DC5120E8CA94F8224D0198C9D7CF2 is NOT a GUID.  It's the binary (hex) representation for the GUID.  The code I posted is correct.

    But if you compare that value to the value from the other table it matches.  At least that is what I was seeing the OPs original post.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182507

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    The reason this is not working is that you are comparing a string and a binary that "look" the same.
    😎

    0x6C7DC5120E8CA94F8224D0198C9D7CF2 is not the same as '0x6C7DC5120E8CA94F8224D0198C9D7CF2', you are using the latter when you should be using the former.

    CONVERT(UNIQUEIDENTIFIER, 0x6C7DC5120E8CA94F8224D0198C9D7CF2,1);

    To convert a string into binary, one has to use the convert function with the format parameter value of 1.


    DECLARE @BINSTR VARCHAR(36) = '0x6C7DC5120E8CA94F8224D0198C9D7CF2';
    SELECT
      @BINSTR
     ,CONVERT(VARBINARY(16),@BINSTR,0) BAD_BIN
     ,CONVERT(VARBINARY(16),@BINSTR,1) OK_BIN
    ;

  • Jeff Moden

    SSC Guru

    Points: 997104

    Eirikur Eiriksson - Saturday, November 10, 2018 12:28 AM

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    The reason this is not working is that you are comparing a string and a binary that "look" the same.
    😎

    0x6C7DC5120E8CA94F8224D0198C9D7CF2 is not the same as '0x6C7DC5120E8CA94F8224D0198C9D7CF2', you are using the latter when you should be using the former.

    CONVERT(UNIQUEIDENTIFIER, 0x6C7DC5120E8CA94F8224D0198C9D7CF2,1);

    To convert a string into binary, one has to use the convert function with the format parameter value of 1.


    DECLARE @BINSTR VARCHAR(36) = '0x6C7DC5120E8CA94F8224D0198C9D7CF2';
    SELECT
      @BINSTR
     ,CONVERT(VARBINARY(16),@BINSTR,0) BAD_BIN
     ,CONVERT(VARBINARY(16),@BINSTR,1) OK_BIN
    ;

    Exactly.  And the code I demonstrated also splits the character-based binary out of the original string.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jay-h

    SSCoach

    Points: 18816

    Ah the '1' argument is what I was missing. 

    CONVERT(VARBINARY(16),@BINSTR,1) OK_BIN

    Thanks everyone

    ...

    -- FORTRAN manual for Xerox Computers --

  • Jeff Moden

    SSC Guru

    Points: 997104

    jay-h - Monday, November 12, 2018 12:31 PM

    Ah the '1' argument is what I was missing. 

    CONVERT(VARBINARY(16),@BINSTR,1) OK_BIN

    Thanks everyone

    Except ou know it's always going to be exactly 16 bytes so don't use VARBINARY(16).  Use BINARY(16), instead.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Solomon Rutzky

    SSCoach

    Points: 16266

    Lynn Pettis wrote:

    Jeff Moden - Friday, November 9, 2018 8:13 PM

    Lynn Pettis - Friday, November 9, 2018 8:44 AM

    Jeff Moden - Thursday, November 8, 2018 10:05 PM

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)" )

    ...

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    This will do it for you.  I also took the liberty of splitting the desired value out of the original message.

    --===== This isn't part of the solution.  We're just creating the message as a test.

    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')';

    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,

    -- converts the text to a BINARY(16) and then converts that to the final GUID.

    SELECT CONVERT(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1));

     

    Actually, Jeff, your code doesn't quite work.  Look at the following:

    --===== This isn't part of the solution. We're just creating the message as a test.

    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')';

    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,

    -- converts the text to a BINARY(16) and then converts that to the final GUID.

    SELECT SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), convert(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))

    select SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), cast(stuff(stuff(stuff(stuff(convert(varchar(36),convert(varbinary(16),substring(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1),2),21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-') as uniqueidentifier);

    Ah... be careful now, Lynn.  You have to remember that the value of 0x6C7DC5120E8CA94F8224D0198C9D7CF2 is NOT a GUID.  It's the binary (hex) representation for the GUID.  The code I posted is correct.

    But if you compare that value to the value from the other table it matches.  At least that is what I was seeing the OPs original post.

    Hello. While Jeff's code and statement re: "binary form of GUID not being the same as the string form of the GUID" are both correct, it would probably help to have a little bit of explanation, especially in light of Lynn's unanswered question.

    SELECT CONVERT(UNIQUEIDENTIFIER, 0x6C7DC5120E8CA94F8224D0198C9D7CF2) AS [SimpleBinaryToUniqueIdentifier];
    -- 12C57D6C-8C0E-4FA9-8224-D0198C9D7CF2

    The code and result above show that the raw binary value is similar to the string representation (i.e. the value with the dashes), but they aren't exactly the same. Looking closer, we can see that the second half (i.e. the right-most 16 hex digits / 8 bytes, starting with "8224") is identical between them, but the first half not so much. Yet, the first 8 hex digits / 4 bytes are the same but in reverse (looking in terms of 2 hex digit sets / 1 byte at a time). Meaning, the first two hex digits in the string form (i.e. "12") are actually digits 7 and 8 in the binary value. And the next two hex digits in the string form (i.e. "C5") are digits 5 and 6 in the binary value. Then, the 5th and 6th bytes in the string value (i.e. "8C" and "0E", respectively) are bytes 6 and 5, respectively, in the binary value. Likewise, the 7th and 8th bytes are also reversed between the two forms of the same GUID.

    All of that, including the final 8 bytes being in the same order between the two forms, is due to a GUID / UNIQUEIDENTIFIER being a composite value, made up of 4 components: a 4-byte integer, a 2-byte integer, another 2-byte integer, and an 8-element byte array. Arrays should be stored in their index order and so are unaffected by the underlying system architecture (which is why the final 8 bytes are the same in both forms). But, integers are often stored according to the underlying system architecture, and PCs are Little Endian, which means storing native types in reverse order (this also governs UTF-16 code units). This is why the first three components of the GUID are each stored (i.e. the binary value) in reverse order.

    I have a more detailed explanation in an answer to a DBA.StackExchange question (just start with the first UPDATE section):

    Is there a penalty for using BINARY(16) instead of UNIQUEIDENTIFIER?

    Executing the following code shows:

    • Jeff's version returns the same output as the simple, direct CONVERT shown above,
    • Lynn's version returns a GUID with all bytes in the same order as the original binary value, which cannot be correct, at least not in Windows / SQL Server
    --===== This isn't part of the solution.  We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')';
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
    -- converts the text to a BINARY(16) and then converts that to the final GUID.
    SELECT CONVERT(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1)) AS [Jeff’s];
    -- 12C57D6C-8C0E-4FA9-8224-D0198C9D7CF2


    select SUBSTRING(@Msg, CHARINDEX('Job 0x',@Msg)+4,34) AS [Substring of sys.dm_exec_sessions.program_name],
    cast(stuff(stuff(stuff(stuff(convert(varchar(36),convert(varbinary(16),substring(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1) ,2),21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-') as uniqueidentifier) AS [Lynn’s];
    -- 0x6C7DC5120E8CA94F8224D0198C9D7CF2 6C7DC512-0E8C-A94F-8224-D0198C9D7CF2

     

    That being said, with regards to the initial question, I should also point out:

    1. This GUID / UNIQUEIDENTIFIER is the [job_id], not [step_id] (which is an INT)
    2. dbo.sysprocesses was deprecated as of SQL Server 2005. It is better to use a combination of sys.dm_exec_sessions and sys.dm_exec_requests , and sometimes also sys.dm_exec_connections.

    Putting all of that together, including Jeff's code, we

    SELECT ses.[program_name], *
    FROM sys.dm_exec_sessions ses
    LEFT JOIN sys.dm_exec_requests req
    ON req.[session_id] = ses.[session_id]
    LEFT JOIN msdb.dbo.sysjobs job
    ON job.[job_id] = CASE PATINDEX(N'%TSQL JobStep (Job 0x[0-9A-F]%',
    ses.[program_name])
    WHEN 0 THEN NULL
    ELSE CONVERT(UNIQUEIDENTIFIER,
    CONVERT(BINARY(16),
    SUBSTRING(ses.[program_name],
    CHARINDEX(N'Job 0x',
    ses.[program_name]) + 4,
    34),
    1))
    END
    WHERE ses.[is_user_process] = 1;

     

     

    Take care,

    Solomon...

     

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply