How tdo I get managers full name using BDC XML

Thu, 02 Jul 2009 14:09:33 Z

I can get every thing I need except I cannot figure out how to get the managers full name, I can get the managers domain\name, but cannot get the full name to work.

I am using this as a base line for the SQL query for the XML: (The data is contained in two tables, the first is the UserProfile_Full table, which contains the Basic Profile Information. The second is the UserProfileValue table. The two are linked by the RecordID Field)

select a.Employee, o.[Last Name],n.[First Name], a.[Office Phone], d.[Cell Phone], c.Email, h.Location, e.Department, b.Title, g.FAX
from
(select
a.RecordID,
a.PreferredName as Employee,
b.PropertyVal as [Office Phone]
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=8 and
a.RecordID=b.RecordID) a

left outer join
(select
a.RecordID,
b.PropertyVal as Title
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=13 and
a.RecordID=b.RecordID) b
on a.RecordID=b.RecordID

left outer join
(select
a.RecordID,
b.PropertyVal as Email
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=9 and
a.RecordID=b.RecordID) c
on a.RecordID=c.RecordID

left outer join
(select
a.RecordID,
b.PropertyVal as [Cell Phone]
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=19 and
a.RecordID=b.RecordID) d
on a.RecordID=d.RecordID

left outer join
(select
a.RecordID,
b.PropertyVal as Department
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=14 and
a.RecordID=b.RecordID) e
on a.RecordID=e.RecordID

left outer join
(select
a.RecordID,
b.PropertyVal as FAX
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=20 and
a.RecordID=b.RecordID) g
on a.RecordID=g.RecordID

left outer join
(select
a.RecordID,
b.PropertyVal as Location
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=11 and
a.RecordID=b.RecordID) h
on a.RecordID=h.RecordID

left outer join
(select
a.RecordID,
b.PropertyVal as [First Name]
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=4 and
a.RecordID=b.RecordID) n
on a.RecordID=n.RecordID

left outer join
(select
a.RecordID,
b.PropertyVal as [Last Name]
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=5 and
a.RecordID=b.RecordID) o
on a.RecordID=o.RecordID

where
a.Employee like @employeelast + '%'
and cast(n.[First Name] as nvarchar(100)) like @employeeFirst + '%'
and CAST(h.location as nvarchar(100)) like @location + '%'
and cast(e.Department as nvarchar(100)) like @department + '%'
and cast(b.Title as nvarchar(100)) like @Title + '%'
and c.email != ''

order by
o.[Last Name], n.[First Name]