How to query Sharepoint 2010 lists with sql?

Question

I think it has to be a common task. We have some applications on sharepoint and we need to prepare reports with data from these applications. It would be great if we could use Reporting Services. We need to query this data with sql because we need to join with some other sources. How can I do this? Are there any 3rd party tools or any built-it mechanisms in sharepoint to use Sharepoint lists data like ms sql tables?

Answer 1

AFAIK there is no way to query SharePoint using SQL syntax. You have the following options:

1. Use CAML
2. Use the REST services (_vti_bin/ListData.svc)
3. Use the SharePoint web services
4. Use Search (Search has a SQL like syntax)
5. Some custom stuff that "exports" your data (using method 1-4) to a queryable SQL db

I think your best option are #2 - the REST/OData services.

Answer 2

I found this to be the easiest way...

SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'WSS;IMEX=1; DATABASE=http://portal/sites/Team/;LIST=On Call;VIEW=;RetrieveIds=Yes;', 'SELECT * FROM LIST')

You'll need Microsoft Access Database Engine 2010 installed.

Here's a connection string reference.

Answer 3

Using SharePoint SQL directly is not supported by Microsoft, so for example the internal structure may change in an update, which may break your query and you will be left to fix it alone. Any updates to the database(s) and Microsoft will no longer provide support for your farm.

You can easily use SharePoint list data in SQL Server Reporting Services by using the SharePoint List Data Extension. You point it towards the list web services, specify the fields and away you go.

Here is a technet article about using the SharePoint List Data Extension in SSRS:

Answer 4

I wrote a query which allows to delete list and documents. You could base your query on this by replacing the "delete" by "Select". Pay attention, it is not a good idea to directly querying SQL.

The query is available at URL http://sharepointtips2012.blogspot.be/2012/10/delete-library-and-documents-directly.html

Answer 5

There is, use the Camelot .NET Connector from Bendsoft. You have full CRUD support towards list items and document libraries. It also support join and union without the requirement of you setting up relations inside SharePoint. The Camelot Connector supports all editions of SharePoint 2007, 2010 and 2013.

Download: ADO.NET Connector For SharePoint

& here is Documentation.

A quick example would be this

using System;
using System.Data;
using Camelot.SharePointConnector.Data;

namespace ExampleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            var connectionString = @"Server=mysharepointserver.com;User=spuser;Password=******;Database=sitename;Authentication=Ntlm;";
            using (var connection = new SharePointConnection(connectionString))
            {
                try
                {
                    Console.WriteLine("Connecting to SharePoint...");
                    connection.Open();

                    using (var command = new SharePointCommand(@"SELECT ID, Name FROM Employees WHERE Role = 'Developer' ORDER BY ID ASC LIMIT 30", connection))
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine(reader["ID"].ToString().PadRight(30) + " : " + reader["Name"].ToString());
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }
                Console.WriteLine("Done");
            }
        }
    }
}

Answer 6

As mentioned by every poster so far, it is not supported to access SharePoint lists directly through SQL. However we had a very obscure use case with an Access 2010 web database where we needed to query the lists directly through SQL.

We used http://www.sharpesttoolsoftware.com/ and it worked very well. You create a separate DB and use the tools to create views that correspond one to one with the target SharePoint lists.

I have since then managed to move things to an Access 2013 'Access App' which uses SharePoint for the front end forms and creates native SQL tables in their own database at the back end and so far seems to work surprisingly well.

Additionally as another poster suggested, SSIS could be a good suggestion for you to get the data into a temporary SQL database that can be queried. I used the SharePoint adapters from CodePlex: http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652 and following the examples it was not too difficult to get a package to work.

(In my case I was going the other way and using SSIS to extract data from my Access 2013 Access App tables to populate a read only list in SharePoint).

Answer 7

Try use SPUD tool (sharepoint utilities for developers)

connect to your site, navigate to list, right click, select "generate list SQL"