Search Wiki:

This sample code demonstrates how to query a Microsoft Office SharePoint Server
2007 or Windows SharePoint Services list from inside SQL Server 2005 or later.

With the growing popularity of SharePoint solutions, SharePoint lists are becoming
an important source of enterprise data. There is no OleDb provider for SharePoint,
so integrating SharePoint data with data in SQL Server can be a challenge.

This CLR User Defined Function shows how you can use the SQL Server's Common
Language Runtime integration to access SharePoint list data through the SharePoint
web services.


Here are some general references for SQL CLR and the SharePoint Web Services.

SharePoint Lists Web Service
http://msdn2.microsoft.com/en-us/library/aa152619.aspx

Using CLR Integration in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms345136.aspx

Creating CLR Functions
http://msdn2.microsoft.com/en-us/library/ms189876.aspx

CREATE ASSEMBLY (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms189524.aspx


For detailed instructions on building and installing this sample in SQL Server see the ReadMe.txt in the release.

Then query SharePoint. Something like
select * from dbo.GetListCollection('http://MySharePointSite');
To get the list of SharePoint Lists available. Then retrieve the items for one of the lists.
the GetListItems function returns a single XML document containing all of the items. So to
make use of the data, you would typically use an XML-shreading query like this:
with ListItems as
(
  select dbo.GetListItems('http://MySharePointSite','Site Collection Documents',null) AllListItems
)
select 
    Item.value('@ows_Title', 'varchar(50)') Title, 
    Item.value('@ows_EncodedAbsUrl','varchar(max)') Url
    ,Item.query('.') Item 
from ListItems cross apply ListItems.AllListItems.nodes('/*/*') Items(Item)
There is also an example of a higher-performance solution that shreads the XML in CLR code
and returns a relational result to SQL Server. But you will need to customize the coding
to return the fields that are relavent in your list.

Select * from dbo.GetListItemsTable('http://MySharePointSite','Site Collection Documents',null)
Please let me know if you find this sample useful, and use the Issue Tracker for any problems or changes you'd like to see.

David Baxter Browne
Microsoft Technology Center - Dallas
Last edited Apr 23 2008 at 6:56 PM  by dbrowne, version 11
Comments
OthmanHack wrote  Jan 23 2009 at 4:06 PM  
This works fine from the local machine but if I'm on a workstation connecting to a database server I get:
Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user defined routine or aggregate 'GetListCollection':
System.Net.WebException: The request failed with HTTP status 401: Unauthorized.

Also, the code as is doesn't pass on the viewName - I had to replace the null in the call to this.

m2009SP wrote  May 21 2009 at 6:25 AM  
I am interest in this information. I am trying to create a SELECT query on "SQL Reporting Services/Designer 2005" to SELECT data both SQL database and SharePoint v3. Do you have it in VB version?

My application use Visual 2008, Visual 2005, SQL Server 2005 (with CLR enabled), SharePoint v3 and SQL Reporting Services 2005.


m2009SP wrote  May 21 2009 at 6:38 AM  
By the way, I am only interest in only the SharePoint web service, GetListItems and GetListItems with a specific list of Identifers.

dbrowne wrote  May 21 2009 at 3:03 PM  
There is no VB version, but if you don't need to change the code much, you could just use the C# version. Once you install the CLR functions you can write queries that join both database and SharePoint data.

Regards,

David

m2009SP wrote  May 29 2009 at 12:47 PM  
David,

Can you please redirect some information about <ViewFields>? I am only interest why some fields will not show even when I explicity state the fields i want to display. For example: there is a column called package (package is an internal name) on the list. the package is optional and it is not mandatory field. when i use GetListItems to retrieve the data wtih specific records (using Query and Eq element), I noticed that Package column data is not include! Do you have any ideas how to force Package column to return empty column?

fedebona wrote  Jun 12 2009 at 9:24 AM  
I'm having the 401 Unauthorized issue too. I'm calling the SQL function using a SQL Server account. Can anyone give me some suggestion? What kind of authorization do I need ? What windows user is really making the web service request, provided that SQL Server runs as local system account?

OthmanHack wrote  Jun 12 2009 at 8:25 PM  
My problem turned out to be authentication over the "double-hop" - sql is calling the code which calls sharepoint and was using NTLM. To do this properly you need to use Kerberos. I worked around this by including user credentials in the code, that is in ListExtensions.cs I changed:
this.Credentials = System.Net.CredentialCache.DefaultCredentials;
To: this.Credentials = new System.Net.NetworkCredential("username", "password", "Domain");
and it works fine

MickSturbs wrote  Sep 18 2009 at 12:32 PM  
What version of Visual Studio do I need? I've got 2005 pro and 2008 standard. I'm told that this project type is not supported by my installation.

Sarafoster wrote  Nov 10 2009 at 7:28 PM  
sql is the merge in MS software , purpose of this code :

with ListItems as
(
select dbo.GetListItems('http://MySharePointSite','Site Collection Documents',null) AllListItems
)
select
Item.value('@ows_Title', 'varchar(50)') Title,
Item.value('@ows_EncodedAbsUrl','varchar(max)') Url
,Item.query('.') Item
from ListItems cross apply ListItems.AllListItems.nodes('/*/*') Items(Item)

<a href="http://www.e-dol.com">Universities in Sweden</a>

heguangm wrote  May 4 2010 at 8:06 PM  
I got problems similar to what OthmanHack got: A .NET Framework error occurred during execution of user defined routine or aggregate 'GetListCollection':
System.Net.WebException: The request failed with HTTP status 401: Unauthorized.

The weird thing is that the query works one tim and stops working for a while. It works again but I could not make another event different query. I have to wait for a while: seems SharePoint will release some locks.

Wonder whether related to HttpWebRequest::PreAuthenticate. What is the default settings for sharepoint web services?

thanks,

Updating...
Page view tracker