Active Directory GUID - uniqueidentifier

Question

Post reply

Active Directory GUID - uniqueidentifier

MauriceH

SSC Journeyman

Points: 96
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 2 (of 2 total)

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

Razvan Socol SSCarpal Tunnel Points: 4783 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 Journeyman Points: 96 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.