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 here

Now to get to the fun part:

, 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