Convert View Field Type?

• Tommy Upton

  Newbie

  Points: 1

  More actions

  August 23, 2004 at 3:57 pm

  #89121

  I have a view created that uses ADSI to return some user information from AD:

  CREATE VIEW vwADUsers AS SELECT * FROM OPENQUERY

  ( ADSI,' ldap://DC=stph,DC=org>;(&(objectCategory=Person)(objectClass=user));samaccountname,sn,givenname,objectguid, adspath')

   I want to create a join between this view and another table.  The syntax I want to use is:

  SELECT sn, objectguid, ADGuid, givenname, LogonTime FROM vwADUsers, tblLogonHistory WHERE ObjectGuid=ADguid

  The problem is that in the view, the objectguid is returned as binary and in the table the ADGuid has already been converted to a string so this SELECT statement returns no records.  How can convert the Objectguid field to string before joining the views?  By the way, when I run the view (vwADUsers) in QA, the objectguid field is displayed correctly.  Does QA do some kind of conversion that the raw view does not? 

  Tommy

• babuganesh2000

  Hall of Fame

  Points: 3123

  More actions

  August 24, 2004 at 3:21 am

  #520277

  Tommy,

  When the following code work, it is obvious your query should also work. I think the Adguid doesn't match with ObjectGuid.

  Pls try this following code:

  create table mytable (empname varbinary(100) not null)

  insert into mytable

  select convert(varbinary,'Ganesh Babu hello Tommy')

  create table mytable1 (empname varchar(100) not null)

  insert into mytable1

  select 'Ganesh Babu hello Tommy'

  select * from mytable

  select * from mytable1

  SELECT * FROM mytable AS M1 ,mytable1 AS M2

  WHERE M1.empname = M2.empname

  drop table mytable

  drop table mytable1

  Thanks,

  Ganesh