November 8, 2018 at 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?
...
-- FORTRAN manual for Xerox Computers --
November 8, 2018 at 1:02 pm
jay-h - Thursday, November 8, 2018 12:49 PMSounds 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';
November 8, 2018 at 1:03 pm
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
November 8, 2018 at 1:20 pm
Lynn Pettis - Thursday, November 8, 2018 1:02 PMjay-h - Thursday, November 8, 2018 12:49 PMSounds 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)
November 8, 2018 at 10:05 pm
jay-h - Thursday, November 8, 2018 12:49 PMSounds 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
Change is inevitable... Change for the better is not.
November 9, 2018 at 8:44 am
Jeff Moden - Thursday, November 8, 2018 10:05 PMjay-h - Thursday, November 8, 2018 12:49 PMSounds 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);
;
November 9, 2018 at 11:14 am
Thanks for the input. Monday I hope to dig back into this.
...
-- FORTRAN manual for Xerox Computers --
November 9, 2018 at 8:13 pm
Lynn Pettis - Friday, November 9, 2018 8:44 AMJeff Moden - Thursday, November 8, 2018 10:05 PMjay-h - Thursday, November 8, 2018 12:49 PMSounds 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-2017Actually, 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
Change is inevitable... Change for the better is not.
November 9, 2018 at 9:40 pm
Jeff Moden - Friday, November 9, 2018 8:13 PMLynn Pettis - Friday, November 9, 2018 8:44 AMJeff Moden - Thursday, November 8, 2018 10:05 PMjay-h - Thursday, November 8, 2018 12:49 PMSounds 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-2017Actually, 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.
November 10, 2018 at 12:28 am
jay-h - Thursday, November 8, 2018 12:49 PMSounds 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
;
November 10, 2018 at 7:01 am
Eirikur Eiriksson - Saturday, November 10, 2018 12:28 AMjay-h - Thursday, November 8, 2018 12:49 PMSounds 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
Change is inevitable... Change for the better is not.
November 12, 2018 at 12:31 pm
Ah the '1' argument is what I was missing.
CONVERT(VARBINARY(16),@BINSTR,1) OK_BIN
Thanks everyone
...
-- FORTRAN manual for Xerox Computers --
November 12, 2018 at 5:52 pm
jay-h - Monday, November 12, 2018 12:31 PMAh 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
Change is inevitable... Change for the better is not.
August 12, 2020 at 7:08 am
Jeff Moden - Friday, November 9, 2018 8:13 PMLynn Pettis - Friday, November 9, 2018 8:44 AMJeff Moden - Thursday, November 8, 2018 10:05 PMjay-h - Thursday, November 8, 2018 12:49 PMSounds 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:
--===== 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:
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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply