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

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: http://technet.microsoft.com/en-us/library/ee633650.aspx

Answer 3

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 the URL http://www.microsoft.com/en-us/download/details.aspx?id=13255

Here's a connection string reference: http://connectionstrings.com/sharepoint

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 at: http://www.bendsoft.com/net-sharepoint-connector/

Documentation at: http://www.bendsoft.com/documentation/camelot-net-connector/latest/

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");
            }
        }
    }
}