Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  Hossein Aarabi 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. –  Hossein Aarabi Jul 19 '12 at 22:30
add comment

6 Answers

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.

share|improve this answer
    
+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
add comment

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

share|improve this answer
    
Here is a blog with some good screenshots: nikspatel.wordpress.com/2010/04/30/… –  Hossein Aarabi Jul 19 '12 at 22:32
add comment

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

share|improve this answer
add comment

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");
            }
        }
    }
}
share|improve this answer
add comment

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

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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