List of Sharepoint documents for crm dynamics entity

Question

For creating TSQL reports what is the best method of doing this? (e.g. What documents are held against an instance of a specific entity?)

I can see in crm dynamics a table of SharePointDocumentLocation but cannot find a table of documents and their titles.

Can anyone help with this? Thanks

Answer 1

The SharepointDocumentLocation stores the Url of the document pretty much, not the title. You could potentially extract the title based on the Url if they are the same, specially if you just want to display a list of documents. But if they are different, or need extra columns, you will need a 2nd query to pull data from the associated SP document library using a SP List Connection in the SSRS.

Answer 2

To get the list of document, you need to query your sharepoint database server. There could be an array of server hence you can find the exact server instance name from SharepointDocumentLocation table.

Example: CRM SharepointDocumentLocationBase columns

Then you need to query that specific sharepoint database server instance using following alike query:

SELECT *
FROM [dbo].[AllDocs]
WHERE DirName LIKE '%EntityName%'

Answer 3

Solved this. I hope this will help you.

USE YourSharePointDatabaseName;

SELECT       
    AD.DirName,
    AD.LeafName AS Name,
    AD.TimeLastModified
    tp_ColumnSet.value('(/nvarchar7)[1]', 'nvarchar(200)') AS Title,
    tp_ColumnSet.value('(/nvarchar11)[1]', 'nvarchar(200)') AS Col1,
    tp_ColumnSet.value('(/nvarchar12)[1]', 'nvarchar(200)') AS Col2,
    tp_ColumnSet.value('(/nvarchar13)[1]', 'nvarchar(200)') AS Col3,
    tp_ColumnSet.value('(/nvarchar15)[1]', 'nvarchar(200)') AS Col4
FROM 
    AllUserData AUD WITH (NOLOCK) 
    INNER JOIN AllDocs AD WITH (NOLOCK)  ON AD.Id = AUD.tp_docID;

And once this bit is solved, you can join the SharePoint location field in CRM to join this result set.