|
In order to connect AA Adaptor to SharePoint user profile you need to create a View.
Following is the view in SharePoint Shared Services Database. (default name is SharedServices1_DB)
--Create View in SharePoint Shared Services Database (default name is SharedServices1_DB)
USE [SharedServices1_DB]
GO
/****** Object: View [dbo].[vu_Chart] Script Date: 02/20/2007 21:52:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE View [dbo].[vu_Chart] AS
SELECT aa.PreferredName as 'Name', bb.Title as 'JobTitle', bb.Department as 'DeptName', aa.ORG_ID, aa.MGR_ID, aa.Picture as Picture, '' + aa.Email +'' as Email, bb.Telephone, '0' as 'Secretary', 'Employee' as 'Category'
FROM
(SELECT UserProfile_Full.RecordID, UserProfile_Full.RecordID AS 'ORG_ID', UserProfile_Full.NTName, UserProfile_Full.PreferredName, UserProfile_Full.Email, UserProfile_Full.Manager, UserProfile_Full.DataSource, UserProfile_Full_1.RecordID AS 'MGR_ID',
(CASE WHEN UserProfile_Full.Manager IS NULL THEN 0 ELSE 1 END) AS 'HasParent',
(CASE WHEN b.childcount IS NULL THEN 0 ELSE b.childcount END) AS childcount,
'<Img src=' + char(39) + CAST(UserProfileValue.PropertyVal AS varchar(250)) + char(39) AS Picture
FROM UserProfile_Full
LEFT OUTER JOIN UserProfile_Full AS UserProfile_Full_1
ON UserProfile_Full.Manager = UserProfile_Full_1.NTName
LEFT OUTER JOIN
(SELECT Manager, COUNT(Manager) AS 'ChildCount' FROM UserProfile_Full AS UserProfile_Full_2 WHERE (Email LIKE '%aa%') AND
(Manager IS NOT NULL) GROUP BY
Manager) AS b
ON UserProfile_Full.NTName = b.Manager
LEFT OUTER JOIN UserProfileValue AS UserProfileValue ON UserProfile_Full.RecordID = UserProfileValue.RecordID
AND UserProfileValue.PropertyID = 23
) AS aa
LEFT OUTER JOIN (
SELECT a_1.RecordID, a_1.PropertyVal AS 'Telephone', CAST(b_1.PropertyVal AS varchar(250)) AS 'Title', CAST(c.PropertyVal AS varchar(250)) AS 'Department'
FROM
(SELECT RecordID, PropertyVal FROM UserProfileValue WHERE (PropertyID = 8)) AS a_1 INNER JOIN (SELECT RecordID, PropertyVal FROM UserProfileValue AS UserProfileValue_2 WHERE (PropertyID = 13))
AS b_1
ON a_1.RecordID = b_1.RecordID
INNER JOIN
(SELECT RecordID, PropertyVal FROM UserProfileValue AS UserProfileValue_1 WHERE (PropertyID = 14)) AS c ON a_1.RecordID = c.RecordID) AS bb ON aa.RecordID = bb.RecordID
Followings are two samples of the SQL Connection string that can be used by our adaptor.
Example 1:
<add key="ConnectionString" value="Server=WSS2DEV;Database=SharedServices1_DB;Integrated Security=SSPI;"/>
Example 2:
<add key="ConnectionString" value="Data Source=YourSharePointSQLServer;Initial Catalog=YourSharedServicesdatabaseName;Integrated Security=SSPI;"/>
Top of Page 
|
|
In order to use active directory from our adaptor program you can link Active directory to SQL server using the following command:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
Following article explains how to use AD as a linked server in SQL, then use a view to query AD information:
http://articles.techrepublic.com.com/5100-6345_11-5259887-2.html
Then you need to create a view for this active directory. You can create a view in SQL server like:
SELECT * FROM OpenQuery(ADSI, 'SELECT title, displayName,
sAMAccountName,
givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM
''LDAP://DEVDC'' where objectClass = ''User''')
Now from the adaptor application you can connect to this database using the above view.
Top of Page
|
