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);
;