SQL OpenQuery to Active Directory Data Type Challenge

• SQL Dude-467553

  Hall of Fame

  Points: 3895

  More actions

  January 18, 2011 at 3:33 pm

  #243823

  Hello,

  I've created a linked server ADSI to issue an OpenQuery to Active Directory. I'm grabbing the name and objectGUID for each group. I wrote this query below, and it runs great here is an example of the output

  QUERY:

  SELECT DISTINCT cn, objectguid FROM OpenQuery(ADSI, 'SELECT cn, objectguid FROM ''LDAP://DC=mydomain'' WHERE objectcategory=''group'' ') ORDER BY 1

  RESULTS:

  Group1 0xF964C2D3F5376D479DA2A76C97B6BEB5

  Group2 0xF964C2D3F5376D479DA2A76C97B6BCB5

  Group3 0xF964C2D3F5376D479DA2A76C97B6ZBB5

  However I want this GUID in the format of a traditional GUID string or Varchar. Here is an example of what I want to see E9EC965F-998D-4715-B84A-DB5126AA7376

  How would I cast or convert the ObjetGuid column above to be able to use the substring function on it to get a portion of the data in it?

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  January 19, 2011 at 3:40 pm

  #1275605

  SELECT CONVERT(UNIQUEIDENTIFIER,0xF964C2D3F5376D479DA2A76C97B6BEB5);

  Delivers:

  D3C264F9-37F5-476D-9DA2-A76C97B6BEB5

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato