Lynn Pettis wrote:
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];
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];
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:
- This GUID / UNIQUEIDENTIFIER is the [job_id], not [step_id] (which is an INT)
dbo.sysprocesses was deprecated as of SQL Server 2005. It is better to use a combination of
sys.dm_exec_requests , and sometimes also
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]%',
WHEN 0 THEN NULL
ses.[program_name]) + 4,
WHERE ses.[is_user_process] = 1;