Export Sharepoint List to MS SQL Database

Question

We would like to export our SharePoint Content to MS SQL Database. Do you please know about any tool that allow so?

The purpose of this is to use MS SQL DB in our reporting tool which is easier and faster.

Thanks a lot.

Answer 1

You have several ways to do this, Programmatically you can use SharePoint server site object model or client side object model and grab each field value of each list item and write to the SQL DB via connection string.

Even you can user powershell and do the same, configure it as a job and schedule it to run at regular intervals.

Detailed example with explanation can be found below:

https://www.linkedin.com/pulse/how-use-c-timer-job-grab-sharepoint-data-insert-sql-database-maillie/

To summarize the answer:

1. refer the sharepoint dlls in the visual studio project.
2. start using the SharePoint objects and extract the sharepoint list items from the required columns (you may use caml queries to filter out the fields and output)

3. create a sql connection which could be standard ADO.net connection to the sql server and map the resulted SharePoint output to the data table and eventually update the same to the SQL server table.

4. you can even opt for implementing this in to a timer job solution that SharePoint itself provides in the framework for a recurrent process or you can just create a console application and schedule that in the windows task scheduler.

Note:

If the sharepoint version is 2010 enterprise edition, then you can use the BCS services (business connectivity services) and create external content type and connection in the sharepoint designer with read/write access and actions so if there is any change in the list items then immediately it will auto reflect in the sql without writing any custom code.

So, try the options referred in the above solutions based on the SharePoint 2010 version.