Geeks With Blogs

News

 
Subscribe by Email:







Updates:
about

via Twitter


www.flickr.com
This is a Flickr badge showing photos in a set called Public. Make your own badge here.


View Will Pritchard's profile on LinkedIn


View blog authority

MOSS Paradox SharePoint, Microsoft BI, .NET, Microsoft Tech and errata
Using BDC to query our company's SharePoint profiles database ( SharedServices1_DB in our environment ) I was able to create a pretty snappy employee lookup.  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.

First things first - we designed a SQL query that would suit our needs and display the information we needed. Here is what my final query looked like:

declare @department nvarchar(100)
set @department = ''
declare @employee nvarchar(256)
set @employee = ''

select a.Employee, b.Title, e.Department, a.[Office Phone], d.[Cell Phone], c.Email
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,
a.PreferredName as Employee,
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

where
cast (e.Department as nvarchar(100)) like @department + '%'
and a.Employee like @employee + '%'
and a.[Office Phone] is null
order by
a.Employee

Next I needed to build a BDC xml file for the lookup.  We created one to use two wildcard filter descriptors, one for the department and one for the employee name.

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<LobSystem Type="Database" Version="1.5.7.00" Name="EmployeePhoneList" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

<Properties>
<Property Name="WildcardCharacter" Type="System.String">%</Property>
</Properties>

<AccessControlList>
<AccessControlEntry Principal="yourdomain\security_group">
<Right BdcRight="Execute"/>
<Right BdcRight="Edit"/>
<Right BdcRight="SetPermissions"/>
<Right BdcRight="SelectableInClients"/>
</AccessControlEntry>
<AccessControlEntry Principal="yourdomain\another_security_group_or_user">
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
</AccessControlEntry>
</AccessControlList>

<LobSystemInstances>
<LobSystemInstance Name="ProfileDB">
<Properties>
<Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
<Property Name="RdbConnection Data Source" Type="System.String">SP01</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">SharedServices1_DB</Property>
<Property Name="RdbConnection User ID" Type="System.String">SQLUserAccount</Property>
<Property Name="RdbConnection Password" Type="System.String">password</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String"/>
</Properties>
</LobSystemInstance>
</LobSystemInstances>

<Entities>
<Entity Name="Profiles">
<Properties>
<Property Name="Title" Type="System.String">Employee Contact List</Property>
</Properties>
<Identifiers>
<Identifier Name="Department" TypeName="System.String"/>
<Identifier Name="Employee" TypeName="System.String" />
</Identifiers>
<Methods>
<Method Name="GetProfiles">
<Properties>
<Property Name="RdbCommandText" Type="System.String">
select a.Employee, b.Title, e.Department, a.[Office Phone], d.[Cell Phone], c.Email
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,
a.PreferredName as Employee,
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
where
cast (e.Department as nvarchar(100)) like @department + '%'
and a.Employee like @employee + '%'
and a.[Office Phone] is not null
order by
a.Employee
</Property>
<Property Name="RdbCommandType" Type="System.String">Text</Property>
</Properties>
<FilterDescriptors>
<FilterDescriptor Type="Wildcard" Name="Department">
<Properties>
<Property Name="DeptSearch" Type="System.String">Is Like</Property>
</Properties>
</FilterDescriptor>
<FilterDescriptor Type="Wildcard" Name="Employee">
<Properties>
<Property Name="EmplSearch" Type="System.String">Is Like</Property>
</Properties>
</FilterDescriptor>
</FilterDescriptors>
<Parameters>
<Parameter Direction="In" Name="@department">
<TypeDescriptor TypeName="System.String" IdentifierName="Department" AssociatedFilter="Department" Name="Department">
<DefaultValues>
<DefaultValue MethodInstanceName="PhoneFinderInstance" Type="System.String"></DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="In" Name="@employee">
<TypeDescriptor TypeName="System.String" IdentifierName="Employee" AssociatedFilter="Employee" Name="Employee">
<DefaultValues>
<DefaultValue MethodInstanceName="PhoneFinderInstance" Type="System.String"></DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="Return" Name="Lookup">
<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="DepartmentDataReader">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="DepartmentDataRecord">
<TypeDescriptors>
<TypeDescriptor TypeName="System.String" IdentifierName="Department" Name="Department">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Department</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" IdentifierName="Employee" Name="Employee">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Employee</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" Name="Title">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Title</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" Name="Office Phone">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Office Phone</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" Name="Cell Phone">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Cell Phone</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" Name="Email">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Email</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="PhoneFinderInstance" Type="Finder" ReturnParameterName="Lookup" />
</MethodInstances>
</Method>
</Methods>
</Entity>
</Entities>
</LobSystem>

Once we uploaded the BDC, we created a new page for the BDC and implemented the Business Data List Webpart, chose our new BDC as the Type and voila!


Click For Larger Image



kick it on SharePointKicks.com Posted on Thursday, July 12, 2007 5:53 PM SharePoint 2007 | Back to top


Comments on this post: Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup

# re: Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup
Requesting Gravatar...
Those are standard BDC web parts, your not actually importing anything side from the BDC application definition file.
Left by Terry Ashley on Aug 25, 2008 10:38 PM

# re: Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup
Requesting Gravatar...
Hey I was wondering how I could modify the BDC data list webpart to include the photo of an employee ?. (i.e the photo that is display on the user profiles/my site)

Thanks.
Left by Sami on Sep 16, 2008 5:10 PM

# re: Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup
Requesting Gravatar...
I want to pull data into SharePoint from LOB sources and then add additional fields. Basically create a list that has both LOB data and new Sharepoint data. Can anyone suggest some methods to do so?
Left by Binni on Sep 17, 2008 8:38 PM

# re: Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup
Requesting Gravatar...
Very nice feature. Have been looking for this for quite some time. Could you please describe in detail how to use those two files? Where should I paste the code? Just some steps about how to get it working?
Many thanks.
Left by Marre on Sep 18, 2008 6:27 AM

# re: Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup
Requesting Gravatar...
How can I get the managers full name and not the managers domain\name?
Left by Glenn on Apr 03, 2009 2:22 PM

# re: Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup
Requesting Gravatar...
Any way that I can get this to auto-display all the Employees it finds in that database? (ie. not requiring a search)?

I got this to work the way you show above ... just trying to get this to display automatically so we can use it as our Company Directory ... nice work!!
Left by Henry G on Mar 18, 2010 9:41 PM

# re: Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup
Requesting Gravatar...
FYI ... if anyone is interested in pulling data from the Sharepoint Profile PropertyIDs .... I found this great document that lists all of them ... i wasted 3+hours just to find this info ...


> > Here's a SQL quert that will dump most of the SPS profile database info:
> >
> > SELECT dbo.UserProfileValue.RecordID, dbo.UserProfile.UserID,
> > dbo.UserProfile.NTName, dbo.UserProfile.PreferredName,
> > dbo.UserProfile.Email, dbo.UserProfile.SID, dbo.UserProfile.Manager,
> > dbo.UserProfile.LastUpdate, dbo.UserProfile.bDeleted,
> > dbo.UserProfileValue.PropertyID, dbo.PropertyList.PropertyName,
> > dbo.UserProfileValue.PropertyVal, dbo.PropertyList.DisplayName,
> > dbo.PropertyList.PropertyURI, dbo.PropertyList.DataTypeID,
> > dbo.PropertyList.DataType, dbo.PropertyList.Length
> > FROM (dbo.UserProfileValue INNER JOIN dbo.PropertyList ON
> > dbo.UserProfileValue.PropertyID = dbo.PropertyList.PropertyID) INNER JOIN
> > dbo.UserProfile ON dbo.UserProfileValue.RecordID =
> dbo.UserProfile.RecordID
> > ORDER BY dbo.UserProfileValue.RecordID, dbo.UserProfileValue.PropertyID;
Left by Henry G on Mar 19, 2010 7:54 PM

# re: Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup
Requesting Gravatar...
very nice, I tried this on my site, works great!!!

It was working for a while, I just found out that some new users will not show when using this BDC webpart query. But those users can be found using sharepoint people search. I have confirmed they are in profiles.

What could causing this? I'm importing user profiles from AD on daily incremental basis. I have not changed anything on the BDC connector.

I need some tips on troubleshooting this.

thanks in advance!
Eric
Left by Eric Li on Oct 12, 2011 8:29 AM

# re: Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup
Requesting Gravatar...
Hi,
I tried to do the same for one of the webpart. And I am getting the below error; could you please suggest me.

"Application definition import failed. The following error occurred: Property value too long. Error was encountered at or just before Line: '272' and Position: '12'. "
Left by Venkat on Apr 30, 2012 9:56 AM

Your comment:
 (will show your gravatar)


Copyright © Will Pritchard | Powered by: GeeksWithBlogs.net