Hello all,

if you ever need to get all Persons and its string values in an easy way directly from your database such as the xpath select

" Select * from /Person" use following Database select against your FimService Database:

--determine dynamically the columns for pivot

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Name)
                    FROM [fim].[ObjectValueString] AS [o]
                    INNER JOIN [fim].[AttributeInternal] AS [ai]
                    ON [ai].[Key] = [o].[AttributeKey]
                    WHERE            
                    [o].[ObjectTypeKey] = 24   
                    group by Name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

set @query = N'SELECT ' + @cols + N' from
             (SELECT        
                [ai].[Name],
                CAST ([o].[ValueString] AS NVARCHAR(MAX)) as Value,
                ROW_NUMBER() over(partition by [ai].Name order by [o].[ValueString]) rn
                FROM [fim].[ObjectValueString] AS [o]
                INNER JOIN [fim].[AttributeInternal] AS [ai]
                ON [ai].[Key] = [o].[AttributeKey]
                WHERE       
                [o].[ObjectTypeKey] = 24 --Type for Persons
             ) x
             pivot
             (
                Max(Value)
                for Name in (' + @cols + N')
             ) p '

exec sp_executesql @query;

Faith,

Thanks for posting this. Great use of dynamic SQL to be able to get all of the string attributes for all people. A couple of comments

1) The Product group won't support SQL queries against any FIM database. They discourage it. Strongly.

2) If you are going to query it you should be sure to "set transaction isolation level read uncommitted" or use the nolock hint in your queries to avoid deadlocks

3) Be aware that the underlying data structures can change with a hotfix or service pack.

Further to David's comments,

Why even bother to do this in FIMservice DB, when you can get it from FIMSycnhronizationService DB which stores data in clear text. 

Also the MV Console has a quick and easy way to query and sort data any way you