Active Directory GUID - uniqueidentifier

Question

Active Directory GUID - uniqueidentifier

MauriceH

SSC Enthusiast

Points: 146
More actions
June 5, 2004 at 4:56 pm

#85751

...there has to be an easier way. What I worked out is that the values that I get from the csv export are binary. Since we run a WIN based system it uses 2 word binary sets. The first 16 bit of the exported 32 bit string needs to be 'swapped around' to become a string that can be casted as a uniqueidentifier. This is the logic:
Exported string.....
AB CD EF GH IJ KL MN NO (FIRST HALF)
SHOUDL GO INTO.....
GH EF CD AB KL IJ NO MN (NEW FIRST HALF)
So it swaps the 2 word sets (1x the first 4 sets and 1x teh 2nd and 1x teh third)
There is a CAST(blah as VARBINARY..... but I can't work that out. Basically, example A should be converted to wxample B below (this is a real sample):
A: X'0ae9fc90cf317f4cb0096425bb2bbaf3'
B: 90FCE90A-31CF-4C7F-B009-6425BB2BBAF3
This is the code that I wipped togehter to do the logic, but again, there must be a better way.......
UPDATE tblTQUser SET UID =
    (
    SELECT '{'+X.D+X.C+X.B+X.A+'-'+(X.F)+(X.E)+('-')+(X.H)+(X.G)+('-')+(
    SUBSTRING(LEFT(RIGHT(X.objectGUID,35),32),19,4))+('-')+(
    SUBSTRING(LEFT(RIGHT(X.objectGUID,35),34),23,12)+'}') AS NewObjectGUID
    FROM
        (
        SELECT sAMAccountName, pkIDUSer, objectGUID,
        SUBSTRING(LEFT(RIGHT(objectGUID,35),32),3,2) AS A,
        SUBSTRING(LEFT(RIGHT(objectGUID,35),32),5,2) AS B,
        SUBSTRING(LEFT(RIGHT(objectGUID,35),32),7,2) AS C,
        SUBSTRING(LEFT(RIGHT(objectGUID,35),32),9,2) AS D,
        SUBSTRING(LEFT(RIGHT(objectGUID,35),32),11,2) AS E,
        SUBSTRING(LEFT(RIGHT(objectGUID,35),32),13,2) AS F,
        SUBSTRING(LEFT(RIGHT(objectGUID,35),32),15,2) AS G,
        SUBSTRING(LEFT(RIGHT(objectGUID,35),32),17,2) AS H
        FROM tblTQUser
   &nbsp X
    WHERE X.pkIDUSer = Y.pkIDUser)  FROM tblTQUser Y

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

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

Razvan Socol SSCarpal Tunnel Points: 4962 More actions · Answer 1

There is an easier way: dynamic sql. But it works only if you need to convert only one value... so it's kind of useless for you.

DECLARE @Str varchar(36)
SET @Str=N'0x0ae9fc90cf317f4cb0096425bb2bbaf3'

DECLARE @SQL nvarchar(4000)
SET @SQL='SELECT CONVERT(uniqueidentifier,'+@Str+')'
EXEC(@SQL)

Or, if you need the result in a variable:

DECLARE @Str varchar(36), @MyGUID uniqueidentifier
SET @Str=N'0x0ae9fc90cf317f4cb0096425bb2bbaf3'
 
DECLARE @SQL nvarchar(4000)
SET @SQL='SELECT @GUID=CONVERT(uniqueidentifier,'+@Str+')'
EXEC sp_executesql @SQL, N' @GUID uniqueidentifier OUTPUT', @MyGUID OUTPUT
 
PRINT @MyGUID
In your case (you need to update many rows), you could use this solution in a loop, but this is far worse than the solution you already use.
Razvan

MauriceH SSC Enthusiast Points: 146 More actions · Answer 2

Thank you Razvan. I did work out the dyn. SQL logic, but yes, since it is not very efficient I didn't bother using it. I guess I just have to live with the update query.

Although the update query logic makes sense, if you look at the binary logic, it will most likley not make sense to a developer who will look at this once I'm gone here. Suppose it's just a matter of proper documentation.

Thank you for your prompt reply.

Active Directory GUID - uniqueidentifier

Cookies on SQLServerCentral