AA Adaptor connects to access, SQL server, SharePoint user profile, Oracle database and linked active directory and generates XML data required for the chart.

For more information about AA Adaptor (Version 2.0 for WSS 3.0) click on the following link.
http://www.aasoftech.com/Download/AA_AdapterUserGuide_2.0.pdf.
To download AA Adaptor (Version 2.0 for WSS 3.0) click on the following link:
http://www.aasoftech.com/Download/AAAdaptor_2.0.zip
For more information about AA Adaptor (Version 1.6 for WSS 2.0) click on the following link.
http://www.aasoftech.com/Download/AA_AdapterUserGuide_1.6.pdf.
To download AA Adaptor (Version 1.6 for WSS 2.0) click on the following link:
http://www.aasoftech.com/Download/AA_Adaptor1_6.zip
Top of Page  

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 title as JobTitle, displayName as Name,manager,department as DeptName,
mail as Email, telephoneNumber as Telephone,info as Category,info as Picture,
displayName as ORG_ID, manager as MGR_ID, 0 as 'Secretary',
(CASE WHEN manager IS NULL THEN 0 ELSE 1 END) AS 'HasParent'
FROM OpenQuery(ADSI, 'SELECT title,manager,mail, info,department, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DEV'' where objectClass = ''User''')

Now from the adaptor application you can connect to this database using the above view. Top of Page