How to pull SharePoint list data from SQL Server

want to make an awesome report.. and link a SharePoint Change Request List with our Navision backend so that we can monitor the change requests from Dynamics.. woohoo!

So I though I will slap together a quick SQL query to make this happen, seemed rather simple at the time. That was before I tried making sense of the SQL backend and table relations.. so I gave up and Googled. While I was doing my searches I decided to set up a linked server from my SharePoint instance to my SSRS instance so I could use the code to make a view for easy reference. This took a while as it seems that SharePoint creates the SP database instance using only Windows authentication, so my little read-only SSRS account didn’t have any luck trying to get to the server initially.. Anyway, that sorted out my Googling brought me to Jon.

And many thanks to Jon Hermiz for his simple guide herehttp://weblogs.sqlteam.com/jhermiz/archive/2007/08/15/60288.aspx

Now to get to the fun part:

SELECT
ud.tp_ID
, ud.tp_ListId
, ud.tp_Author
, ud.nvarchar1
, ud.nvarchar2
, ud.nvarchar3
, ud.nvarchar4
, ud.nvarchar5
, ud.nvarchar6
, ud.nvarchar7
, ud.nvarchar8
, ud.nvarchar9
, ud.nvarchar10
, ud.nvarchar11
, ud.nvarchar12
, ud.*
FROM dbo.Lists l
INNER JOIN dbo.UserData ud ON l.tp_ID = ud.tp_ListId
WHERE (ud.tp_ListId ={XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX})

Note that this does not work in SharePoint 2013, Microsoft got a lot more clever with their columns and store data as XML

Tada! Somewhere in there you should find something useful.

Adrian

0 views0 comments

Recent Posts

See All

SharePoint 2010 and Adobe PDF

SharePoint does not do crawl PDFs out of the box.. here is how to get it to do it. Download and install Adobe’s 64-bit PDF iFilter*1 http://www.adobe.com/support/downloads/detail.jsp?ftpID=4025 Downlo