Anyhow as I was saying the nice thing is it’s got that SQL backend which means I can use that data to report on in Reporting Services. Would there be a way to leverage that mechanism to pull XML data into SQL Server through SSIS on a regular basis?Sharepoint is nice as a basic package, but companies always have specific business requirements which cannot all be embedded by MS in Sharepoint. This direction would seem to be more in line with a supported means to pull the data versus a back-end way.If anyone has dealt with Sharepoint they know how difficult it can be to get custom web controls, data extraction, and general programming completed in this monster. Actually to get the list id you need to look at the Webs table and query the field Title using a like query.There is a great thing about Sharepoint in that its back end is SQL Server. For instance, if you have a site called "Jons Tests" and you need the list id you do something to this effect: SELECT *FROM Webs WHERE (Title LIKE '%Test%')That will give you the id you need to go into the lists table and query the data for this site using this list id. You can eliminiate this or use a parameter (with the actual list name as the selection to the end user). This list of "promotion type" is a lookup in list "Promotions" When a user creates a new row in the Promotions list they must choose a drop down of lookup list "promotion type". But, if some of my fields are lookups, it looks like that data is stored in a different table (can be viewed in userdatajunctions). It would appear to be the tp_field Id column, but I can't see that referenced anywhere else. User Data.tp_List Id = ‘’) Notice my WHERE condition is simply pulling out one list’s data. I've noticed nvarchar goes from 1-64......you have to fiddle with these fields, how do I know which goes where? Meaning I have created a list to provide a drop down for attribute "promotion type".
I was on the verge of reading out of the SP database when I learned that it breaks your support agreement with Microsoft. It uses the Access driver from Office to tunnel through the same webservices that sharepoint uses. OLEDB.12.0','WSS; IMEX=1; Retrieve Ids=Yes; User id= when changing. The WSS specifies WSS (Windows Sharepoint Services)IMEX specifies Read (1) or Read / Write (2). I then added that Access DB as a linked server to SQL 2005 using the "Microsoft Office 12.0 Access Database Engin OLE DB Provider".