Inquire table SharePoint from SQL Database

Question

sorry for my bad english, I'm Italian.

I want to read a table sharepoint from a SQL database

Version SQL 2005 SP2
Version SharePoint 2007.

I have read other posts in this forum.

A possible solution is to use the sp_addlinkedserver

my code is

EXEC sp_addlinkedserver

@server = 'LinkToSharePoint' ,

@srvproduct = 'ACE 12.0' ,

@provider = 'Microsoft.ACE.OLEDB.12.0' ,

@datasrc = 'http://serverSP:22222/Lists/' ,

@provstr = 'WSS;IMEX=2;RetrieveIds=Yes;LIST={43A34E8B-9B30-4CFB-AE56-213854E354E9};'

SELECT * FROM OPENQUERY ( LinkToSharePoint , 'SELECT * From Test' )

My error is:

Italian:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "LinkToSharePoint" returned message "Il modulo di gestione di database di Microsoft Office Access non è riuscito a trovare l'oggetto 'Test'. Assicurarsi che l'oggetto esista e che il nome e il percorso siano digitati correttamente.".

English:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "LinkToSharePoint" returned message "The database management module of Microsoft Office Access was unable to find the object 'Test'. Make sure the object exists and the name and path name correctly..


Messaggio 7350, livello 16, stato 2, riga 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "LinkToSharePoint".

Could someone help?

Thanks a lot

Marco

Answer 1

Hi.

First of all I would suggest you to query a SharePoint list using the SharePoint Object Model or Web Services and not querying the database table directly.
Second, if you really really want to query this table, why do you use sp_addlinkedserver ? Why don't you query the table directly?

Again, please consider accessing SharePoint data using supported, proven methods.

Regards,
Magnus

---

My blog: InsomniacGeek.com

Answer 2

Acessing the SharePoint DB is not recommended and should be avoided on all costs. The rich SharePoint API (OM+WebServices) provides you great abstraction to perfrom tasks without touching  SharePoint Database. For example if you want to get the data items for the SharePoint List and want them into the .NET DataTable there is a method for this

SPWeb oWebsite = SPContext.Current.Web;
SPList oList = oWebsite.Lists["List_Name"];
SPListItemCollection collListItems = oList.Items;

DataGrid1.DataSource = collListItems.GetDataTable();
DataGrid1.DataBind();

You may want to investigate SharePoint API , some of the links that might help are 

http://msdn.microsoft.com/en-us/library/ms473633.aspx

http://msdn.microsoft.com/en-us/library/ms479390.aspx

www.microsoft.com/click/sharepointdeveloper/ 

---

http://razi.spaces.live.com/

Answer 3

Apart from the fact that you should avoid accessing the SQL database for sharepoint, you can't select the table test because that table isnt available in the sql database.
When creating lists, sharepoint doesn't store a list as a SQL table, but stores the list name with it's information in the AllLists table and the list items in AllUserData / AllUserDocs.  MultiLookup information is stored in another table.

Check http://bloggingabout.net/blogs/bas/archive/2009/08/05/moss-migrating-and-merging-user-accounts-to-another-domain.aspx the sharepoint table structure. Please note that the not all the relations are correct.
 
Regards,
Bas

Answer 4

Skip the Linked server and use someting like:

select * from OPENROWSET ('Microsoft.ACE.OLEDB.12.0','WSS;IMEX=1;RetrieveIds=Yes;User id=<userid>;Password=<password>;DATABASE=http://<sharepointserver>/sites/<mysite>/;LIST=Company Locations;', 'SELECT * FROM LIST')

This uses the standard built-in published List Web Services provided by Sharepoint and works even in WSS3.

IMEX=1   - read only
IMEX=2    - read/write.

Don't need GUID, I just use the list name without any issues.  Spaces in the List name are not a problem either. 

If you don't have Office 2007 you may need to download the Office Core Components, which is the driver layer basically.  If you have Office 2007 its not an issues.  You already have what you need to make it work.

select * from <Anything>.  The actual name is ignored.  I use LIST so its obvious.

The linked server is not needed in this case.

If you have issues with credential challenging add a stored username password on your box and it should go away.  Otherwise if you figure out the layers of MS sercurity, share it with us.  As I have yet to.

essentially the OPENROWSET acts as the object.  So use it any where you would use a SQL object.  Inserts, Updates, Selects, Joins, etc.  Be aware that not text fields can be tricky to update.  Not sure why, as it fusses about data conversion issues where there aren't any.

Its a cool and very simple way to do cross site aggregations.