0

I haven't used SharePoint a great deal, but I have worked with other database systems.

If I have a table in a SharePoint site, how can I connect to it and issue a database query in a similar fashion to how you would do this on MySQL etc? I can find the button to load a SharePoint table in Microsoft Access, so presumably this is possible if I could find the URL to connect to. I'd like to do this to integrate the data held in sharepoint with a workflow tool which can query Databases and produce a report, or ideally post new data back into the database.

I can find resources for how to connect SharePoint to other external databases, but how do you connect to the SharePoint backend database itself?

Improve this question

3 Answers 3

Reset to default
1

What you see with Access is more or less the illusion of SQL access to SharePoint data. When you open a list via Access, Access actually queries SharePoint for the structure of the list and builds that as a table in the local access database. It then pulls the data down and populates the local table. Access will maintain the link to the original List and can keep both in Sync, but any queries, updates, etc done in Access are done to the local Access version first before being sync'd up in SharePoint via SharePoint Web Services.

What can be done is to take the nightly backup of the content databases and restore them into a completely different instance of SQL. You could then have another database there that uses linked tables to reference the related SharePoint tables from the database copies. You could then create views there that could be referenced from your workflow tool. This keeps the actual live SharePoint database in a supported state but provides the access you are looking for. This is an option but I doubt it will make your SQL team very happy.

This has two obvious drawbacks: 1) the data is always from the previous night and 2) the data is effectively read-only. If either of these is unacceptable for your purposes then you will need to look for a solution like John's or write your own via the Object Model.

Improve this answer
3

The short answer is you don't.

Directly interacting with a SharePoint content database will cause your SharePoint installation to lose Microsoft support.

This applies even to just using SELECT statements without changing any data.

Any interactions with SharePoint should be performed via the object model.

Improve this answer
1
  • 1
    Just to back this up, a few months ago we were asked to do direct DB read access by a certain large and extremely well known vendor and Microsoft said that not only will it be completely unsupported but in order to regain support we would have to restore the database back to a time before doing this. This would mean months of lost data or the possibility of having to recreate terabytes of data by hand. Obviously, we declined to take that leap but the vendor is still panicking about it because all of their other customers are now in an unsupported state.
    – Dave Wise
    Commented Feb 21, 2013 at 18:18
2

For SharePoint data, you don't access the underlying SQL data directly. Microsoft doesn't support doing this and you will be in an unsupported state.

To access SharePoint data you should use the object models provided by Microsoft. These include through C#/VB, Web Services, and JavaScript.

Improve this answer
1
  • If you were feeling adventurous, you could create a .NET CLR function that calls the C# object model of SharePoint and returns it to SQL. I wrote a blog post about it a while back: sharepointjohn.com/…
    – John Chapman
    Commented Feb 21, 2013 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.