From SharePoint lists into SQL Server 2008 R2 database

[eo]

Hi,

Technologies - SQL Server 2008 R2, MOSS2007, and Office 2007

I have a set of SharePoint lists which I would like to expose to a variety of reporting technologies (SSAS, SSRS, etc).

I am constrained with out of the box functionality, so custom solutions or third party options is not a luxury I have. SO, I am thinking of...

1) Data entered into a sharepoint list

2) Using MS Access "link to data source by creating a linked table" option in Access to my Sharepoint lists

3) Then creating a linked server in SQL Server to the Access database.

I have tried the solution as described above, but it seems my theory is flawed. When I navigate to the Access linked Server in SSMS I cannot see any tables. Even trying a query such as the one below does nothing. It does not error, but it just runs without returning results (the dataset is small, so it should be immediate). I am assuming it is because I am creating a linked server, to (what is in effect) another linked server (Access).




EO
Hertfordshire, England

 

[SimonSellick]

Can you see the data in the linked tables if you browse them in Access? If so, perhaps you could import them into Access periodically, instead of linking them, and use the imported native Access tables as your data source for SSRS etc?