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
;