13

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?

  • Czesc Agneszka, adding to Wictor and Russells answers.If you need to do some more re the reporting e.g joining to external tables etc..You can use SSIS to pump the data out to sql.See this sqlsrvintegrationsrv.codeplex.com/releases/view/17652 – naijacoder Jan 24 '12 at 23:50
  • Have you tried the solution @Russel provided? That sounds promising. – H A Jul 19 '12 at 22:20
  • Using SSRS you are not limited to using SQL, you can use other data sources, such as SharePoint List Data Extension, mentioned by @Russel. – H A Jul 19 '12 at 22:30
  • If you have access installed then you can open SharePoint lists as linked tables. You can then use access SQL to query this data. – user13635 May 20 '14 at 15:08
11

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.

  • +1 you can always use the Client Object Model, Linq to SharePoint, or the existing object model, but one neat new way to get at list data is with listdata.svc – Falak Mahmood Jan 24 '12 at 14:43
5

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:

  • Here is a blog with some good screenshots: nikspatel.wordpress.com/2010/04/30/… – H A Jul 19 '12 at 22:32
  • It's also important to note that you are side-stepping SharePoint's resource management and potentially introducing deadlocks. Proceeding down this path is dangerous. – Alex Angas Jan 13 '15 at 5:46
4

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.

1

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

1

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

This is a great 3rd party solution that I have used and am happy with: http://store.bamboosolutions.com/pc-30-1-sql-view-web-part-release-12.aspx?gclid=CO_klKmZtbsCFUGTfgodsUMA4w

0

Try use SPUD tool (sharepoint utilities for developers)

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

0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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