Try MySite.GetContent() Catch(Useless Info) Finally Site.Close() End Try

The guide to programming and analyzing SQL, .NET, and SAP
posts - 18, comments - 280, trackbacks - 0

My Links

News

Hello World. I'm Jon Hermiz and am a software engineer here in Michigan. Feel free to look around and leave feedback!

Archives

Post Categories

How to pull Sharepoint list data from SQL Server

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.  There is a great thing about Sharepoint in that its back end is SQL Server.  But along with the pro comes a con, obtaining that data is very painful.  Why?  Well to be quite honest I don't think the job that Microsoft did with creating the Sharepoint database was all that great.  Reasons being:

  • foreign keys (if any…) are never given the same column name as the primary key
  • column names being cryptic
  • for instance tpID in one table (as a pk) can be tpListID in another table (as the foreign key)
  • referential integrity is used in some areas and forgotten??? in other tables
  • the use of NULLS which can return disastorous results if you are not careful is misused beyond belief
  • and whatever happened to 3NF (check out the table UserData for instance)?

Ok after reading that list most of you are thinking why on earth would I ever use such a tool, the answer is I have to, it’s Microsoft.  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.  Sharepoint is nice as a basic package, but companies always have specific business requirements which cannot all be embedded by MS in Sharepoint.  So one must program or at least change a business process to get that functionality in Sharepoint.

One important object used in Sharepoint is the List object.  Lists are much like your .NET data grids allowing users to enter data and fill a grid with relevant information.  The information in a list is stored in the UserData table (which is an odd name for the table), and the lists themselves are stored in the Lists table.  A list in the database has a GUID representation.  So say you need to create a report with the data inside your list one can query these two tables to get this data.  So here is a nice little example for you:

SELECT      dbo.UserData.tp_ID,
  dbo.UserData.tp_ListId,
  dbo.UserData.tp_Author,
  dbo.UserData.nvarchar1,
                dbo.UserData.nvarchar2,
  dbo.UserData.nvarchar3,
  dbo.UserData.nvarchar4,
  dbo.UserData.nvarchar5,
                dbo.UserData.nvarchar6,
  dbo.UserData.nvarchar7,
  dbo.UserData.nvarchar8,
  dbo.UserData.nvarchar9 ,
                dbo.UserData.nvarchar10,
  dbo.UserData.nvarchar11,
  dbo.UserData.nvarchar12,
  dbo.UserData.*                  –dont forget to modify this to snatch only the columns you need
FROM            dbo.Lists
INNER JOIN
                dbo.UserData ON dbo.Lists.tp_ID = dbo.UserData.tp_ListId
WHERE    
(dbo.UserData.tp_ListId = ‘{B44327F5-95E9-4504-A3BF-1E6751C452D6}’) 

–optional WHERE clause, this pulls data for ONE list

Notice my WHERE condition is simply pulling out one list’s data.  You can eliminiate this or use a parameter (with the actual list name as the selection to the end user).  You can also perform any SQL operation on this query since the data is simply sitting on SQL.  Then simply use this as your reports dataset, add some parameters and voila a Sharepoint report!

Print | posted on Wednesday, August 15, 2007 11:25 AM | Filed Under [ SQL Server Sharepoint ]

Feedback

Gravatar

# re: How to pull Sharepoint list data from SQL Server

I am new to this work area.
Here i want ex. like how can i fetch all Database table data into share Point list.
Anyone knows pls let me know.
Thanke in advance :)
Yogesh Pande.
8/24/2007 3:07 AM | Yogesh Pande
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Could you please explain something for me?

====
SELECT dbo.UserData.tp_ID,
dbo.UserData.tp_ListId,
dbo.UserData.tp_Author,
dbo.UserData.nvarchar1,
====

does tp_ListId, tp_Author, etc get changed to field and list names or do you have to fiddle with these fields until they match your list? I've noticed nvarchar goes from 1-64.......if you have to fiddle with these fields, how do I know which goes where?

12/24/2007 5:10 AM | Michael
Gravatar

# re: How to pull Sharepoint list data from SQL Server

This is a great article thanks.

However, I do have another issue as well. I have a list that has lookups to other lists in it. Meaning I have created a list to provide a drop down for attribute "promotion type". 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". I am using this like a lookup table or referencial key. I need to query this data for reports etc. These columns do not show up in a query such as this. Do you know how to reference this data?

Thanks.

bowman
2/5/2008 3:34 PM | bowmandba
Gravatar

# re: How to pull Sharepoint list data from SQL Server

This is great. But, if some of my fields are lookups, it looks like that data is stored in a different table (can be viewed in userdatajunctions). Any idea how it references back to userdata? It would appear to be the tp_fieldId column, but I can't see that referenced anywhere else.

Thanks for any help you can provide.

jay
3/17/2008 12:29 PM | userjay
Gravatar

# re: How to pull Sharepoint list data from SQL Server

I was just wondering if any of the comments got answers. Maybe I do not have the idea of these type sites yet. I often see an article followed by comments and no answers.

How do you get the list id and even the database name for the list?

I actually went thru the exercise once of use SQL Server Manager to find out these details for a survey.
This was easy, my sharepoint had little else. was a lot of work and trying to match up the rows was not easy.
Someone at work found an answer (that he has not shared yet) involving some query that seemed to give the answe.
well, just looking around myself.
4/4/2008 5:04 PM | Forums blogs
Gravatar

# re: How to pull Sharepoint list data from SQL Server

You get the list id by querying the lists table using a LIKE query and searching for the NAME of your site.
It really is that simple.

4/7/2008 1:05 PM | Jon H
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Actually to get the list id you need to look at the Webs table and query the field Title using a like query.
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.
4/8/2008 11:37 AM | Jon H
Gravatar

# re: How to pull Sharepoint list data from SQL Server

OK, I am intrigued but I was wondering more about the way Excel connects back to SharePoint after an export of a list. There is an IQY file that can be saved that seems to contain a call to the owssrv.dll to extract XMLDATA.

Would there be a way to leverage that mechanism to pull XML data into SQL Server through SSIS on a regular basis? This direction would seem to be more in line with a supported means to pull the data versus a back-end way.

My problem is that I am on a corporate SharePoint server withno access to the back-end SQL Server database.
4/21/2008 11:20 PM | Dave Jackson
Gravatar

# re: How to pull Sharepoint list data from SQL Server

I have a custom list that has lookups to other lists in it. If I select all field of my custom list I cannot find the fields with the lookup multi values. Can you tell me how to make a link between the lookup list and my list to find the selected values or better to show the selected items in the custom list?
6/12/2008 9:46 AM | Rien
Gravatar

# re: How to pull Sharepoint list data from SQL Server

This is NOT SUPPORTED in production environments and MS will not support this type of development.

You will create performance issues on the SQL database if you dont create your queries properly.
9/3/2008 11:30 PM | Buzza
Gravatar

# re: How to pull Sharepoint list data from SQL Server

I am still experimenting with ways to use .iqy for SSIS. It is intriguing, but I don't know how much more effort I will put into it now becuase I have found a method that 'should' be supported and certainly works. I am using an Access 2007 db with an external data connection to my share point list. 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". Finally I use four part object naming without naming the owner or schema. Below is my t-sql query.

SELECT [Server Name] FROM INVENTORY_ACCESS_DB...[Server Inventory]

To make life a little cleaner and easier, I created a view which does a little data cleanup. It also appeals to my lazy nature as it gives me a shorter reference to type.
9/19/2008 7:03 PM | Ian Schwamberger
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Sorry - concatenated two thoughts earlier - The linked server is for some other things I do locally. For SSIS and DTS on another box I connect directly to the Access db when configuring a package. The access db sits on a network share. In there I have a collection of Access db's and external data connections (sometimes more that one per access db) to all kinds of sites and lists. The point is using Access as the medium. IMHO it is one of the very few good uses for Access that I have come across.
9/19/2008 7:13 PM | Ian Schwamberger
Gravatar

# re: How to pull Sharepoint list data from SQL Server

We can get data of Lookup Field.
Lookup field stores ID of that Lookup value in int 1,Int2.... etc.
Using this Id query
dbo.UserData where tp_ID=@id
11/14/2008 2:17 AM | Sujit Chirate
Gravatar

# re: How to pull Sharepoint list data from SQL Server

I was on the verge of reading out of the SP database when I learned that it breaks your support agreement with Microsoft. I would not do it, but I'm not sure of the right way to provide SharePoint data from a SQL Server query.

http://armsinfragilehands.blogspot.com/2008/12/reading-from-sharepoint-database.html
12/17/2008 12:22 PM | Thomas
Gravatar

# re: How to pull Sharepoint list data from SQL Server

How can I get information from document libraries, I want to be able to link to documents for external use.

Thanks
Adil
3/12/2009 1:07 PM | Adil
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Hey,

I see from MS and few other sites that, direct Database access in SharePoint worls is not supported by Microsoft at all and it violates the License Agreements. But am not sure it applies only to Data Manipulation through queries or even for 'read Only' queries as well. Can you please clarify this. I am very much in agreement with you that, few of the progamming challenges are not met with, unless we use direct Queries.

Greatly appreiate your reply..!

Regards,
Prakash
3/19/2009 1:40 AM | Prakash
Gravatar

# re: How to pull Sharepoint list data from SQL Server

this query gets all field and records of sharepoint list by it`s id BUT:
1. it would not get lookup single values (there is reference in 'int' fields to id in lookup list so you can join list to get singlelookup values)
2. it would not get multi lookup values (there is a rather dificult method to get multillokup values using sql profile)
my question: is any easier method to get multilookup values? can You explane relations in sharepoint database structure for selecting multilookup values?

Thanks!
4/9/2009 5:33 AM | Dmitriy
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Great examples here of how to pull statistic from SharePoint dabase

http://www.codeproject.com/KB/dotnet/QueriesToAnalyzeSPUsage.aspx
5/15/2009 5:28 AM | Kaj Sjoberg
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Hi,

We have some of data in SQL Server 2000. We want to use that data in Sharepoint lists.
We would like to know how can we pull that data and store in a custom list.

Thanks in Advance,
5/25/2009 6:28 AM | Pradeepti
Gravatar

# re: How to pull Sharepoint list data from SQL Server


Here's another approach, that allows you to query against SQL but in a much more transparent way, and with ALL relationships easily joinable:

http://slam.codeplex.com
8/1/2009 9:53 AM | another approach (SLAM)
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Be carful to just use this approach for exploring, and not actually deploying critical solutions to production. Service packs and upgrades frequently change the scehmas and organizations. I have seen other posts where people a frantically looking for help because an update broke their solution. Unfortunatly, you are on you own to figure out a fix if this happens, microsoft won't help.
8/7/2009 8:27 AM | SP Consultant
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Wouldn't it just be as easy as setting up the SQL server to use XML to import via RSS? RSS can be setup for each list in Sharepoint.
8/13/2009 3:23 PM | RSS?
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Hi ,

I have a corporate Microsoft Share point 2007 server, which is remotely located.

Will I have permission to fire queries on the share point server ? And is it the right thing to do as per license agreements ?

And If "YES" is the answer for above 2 questions, assuming , I have the query, how/where do i write the query and fire it ?

Basically I have a Share point List, and I want an exact copy of the list(with all data in tact) in a MySQL DB. Is it feasible ?

Regards

Vinod
9/9/2009 8:08 AM | Vinod
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Hello,
I´m still with a doubt: I have a list with a "Person or Group" field, but I haven´t been able to figure it out how to read it, may anyone help me?
Thanks a lot
9/21/2009 11:42 AM | Thiago
Gravatar

# Display external data in SharePoint lists (the other direction)

If you have to display external LOB data in a SharePoint list

(e.g. from databases, such as MS SQL Server, Oracle, IBM DB2, MySQL, Sybase Advantage Database Server, Sybase Adaptive Server Enterprise, Informix, Postgre SQL, IBM UniData, IBM UniVerse, AS/400 (IMB iSeries), Progress and more, from data files, e.g. MS Excel, MS Access, Visual Foxpro, DBF, Filemaker, Text-/HTMLfiles, from services like OLAP, AD, MS Project, MS SharePoint, MS Exchange, MS Index Server, IBM Lotus Notes, ODBC/DSN/UDL)

you can use the Business Data List Connector (BDLC) found here:

http://www.layer2.de/de/produkte/Seiten/SharePoint-Business-Data-List-Connector.aspx

It's very simple to configure, you only need a valid connection string, select statement and primary keys (if any).

The best (from my point of view):

It's real SharePoint lists with all features, e.g. you can use SharePoint workflows to start business action if external data records are changed.

Just download and evaluate it for FREE.
10/9/2009 4:48 AM | SharePointFrank
Gravatar

# re: How to pull Sharepoint list data from SQL Server

I've an sql database from which I need to populate data in to the sharepoint list, where should I specify the connection string for the same and how to populate the data in to the list.

Ex:

I've a list , in which one of the field type is selection (dropdown) on click of the new item in the list i want the data of one particular column to be populated in to the dropdown and based on the selection of the item other columns in the list has to be filled. Please give me the solution.
10/14/2009 6:48 AM | Srikanth
Gravatar

# re: How to pull Sharepoint list data from SQL Server

These issues are all solved with a tool called "SharePoint Data Miner"
10/23/2009 1:07 AM | Adrian Bear
Gravatar

# re: How to pull Sharepoint list data from SQL Server

We’ve recently launched a commercial product called SQList that makes this process much easier, and should be relatively cost effective given the time and complexity of the custom development approach.

It takes the pain out of creating clean, normalized SQL Server tables from your SharePoint lists and libraries. SQList is a Windows service; all it needs to access your SharePoint data is a user with at least read permissions and it will keep your SQL tables constantly up to date.

Find out more here: http://www.axioworks.com/sqlist.aspx
11/4/2009 4:47 PM | AxioWorks
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Hi
I am using the same SQL statement . but is not returning updated values from Db.
I execute this SQL after updating data in my list but still it is returning old values only
any advice for this?
11/30/2009 3:34 AM | Suresh
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Rather then specifying the ID of the List, you can just specify the List Name in the where clause
tp_Title='List Name' -- put the name of the list that you want to query here
12/8/2009 11:06 AM | Nik
Gravatar

# re: How to pull Sharepoint list data from SQL Server

i want to update a SP list from a sql table.
has anyone gotten the solution listed above to work?

in SP 2007 i can't find the table named UserData
or any table with a column: ListID

what am I missing???
2/3/2010 1:52 PM | don
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Look at UserInfo and AllLists for 2007, those are the tables you should be after.
2/3/2010 1:54 PM | Jon H
Gravatar

# re: How to pull Sharepoint list data from SQL Server

I got the queries working by specifying the list id and the field I needed, but the deleted and old values still appear in the sql return. There seems to be an IsCurrent column, etc, but I can't really understand how it knows if the record is deleted. The choice does not show up in Sharepoint, as expected, but it does show up in the table. There must be a join I am missing.. help?
3/9/2010 4:04 PM | Keith
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Ahh.. if you are running into the issue of deleted info and old items showing up in your query, make sure you are querying the dbo.UserData table, not the dbo.AllUserData table! Just realized that was my problem.
3/11/2010 1:45 PM | Keith
Gravatar

# re: How to pull Sharepoint list data from SQL Server

I am working in MS Sharepoint 2007.

I have to display
•drop down with list of non system lists or libraries in the system
•a drop down choice list enumerating all workflows associated with the selected list or library.
•drop down view of the list or library the workflow will run on. All views are listed in a drop down choice list.
•Set a schedule for the event. The options should range from one single event to once every minute.
3/13/2010 1:26 PM | faisal
Gravatar

# re: How to pull Sharepoint list data from SQL Server

i spend lots of time on lookup fields and queries in Moss 2007, if you are going to use lookups and other techniques, you may use form services, but be sure, sharepoint will give you low performance for more than 2000 records, what i am going to do is just use Some Database applications, like File Maker to handle my Day to day operations, and have sharepoint for simple portal and announcment.!!!!
3/24/2010 1:03 PM | Daniel Niroumand
Gravatar

# Read this for Microsoft recomended way of doing it using SSIS. It is a much easier approach.

There is SharePoint Adapter that allows you to select a SharePoint List as a source or destination when creating an SSIS package. For those who have not used SSIS, this makes it very easy to both populate a SQL table with data from a SharePoint list as well as populate a list with SharePoint data. The adapter is available on CodePlex and Microsoft wrote an excellent tutorial on using it.

Microsoft tutorial.
http://msdn.microsoft.com/en-us/library/dd365137.aspx


Adapter for SQL 2005 and 2008 download
http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652

5/8/2010 9:41 AM | Dave Moss
Gravatar

# re: Read this for Microsoft recomended way of doing it using SSIS. It is a much easier approach.

I made a typo in my original comment. I meant to say this makes it very easy to both populate a SQL table with data from a SharePoint list as well as populate a SharePoint list with SQL data.

Actually this approached be used with SharePoint lists and virtually any other data source such as Excel spreadsheets, comma delimited files ect.
5/8/2010 9:46 AM | Dave Moss
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Just found this post. I have been using a tool called SPViews from a sharpesttoolsoftware.com. It generates SQL views that you can use to read the list. We use it primarily in our reporting services reports but I have also used it to get data for external systems and once just used it to bind to a form control for valid values. It even has the option of building a view that shows the historical rows or not.
5/13/2010 1:03 PM | JD
Gravatar

# re: How to pull Sharepoint list data from SQL Server

how to pull data from sqlserver2005 and how fill combox of that data
8/5/2010 6:39 AM | keyur
Gravatar

# re: How to update Sharepoint list data from MS Access

Question: How can I add records to my Sharepoint List using MS Access. The Sharepoint List is actually files that people upload. When I've tried adding records via MS Access recordset -- it complains about the name field being blank -- despite that I entered a value into it. (The Name field is the file name). I've started looking at LISTOBJECT but it seems like it wont let me add either. Any ideas?
8/18/2010 8:46 AM | David Ramer
Gravatar

# re: How to pull Sharepoint list data from SQL Server

HI
Thanks a lot, this works fine to get deleted data. Saves lot of our time.
10/12/2010 2:26 AM | srividya
Gravatar

# re: How to pull Sharepoint list data from SQL Server

So say you need to create a report with the data inside your list one can query these two tables to get this data. So here is a nice little example for you:

snow boots for women | columbia sportswear outlet | cheap mac makeup | the north face jackets

womens snow boots | columbia sportswear | cheap makeup | cheap north face jackets
10/21/2010 5:39 PM | columbia jackets
Gravatar

# how to find my profile data in sql server: means which database?

I want to know where exactly the My Profile data is stored in sql (moss 2007), Is that in Mysite DB or SSP DB or do we have seperate DB created for my profile.
10/25/2010 5:35 AM | raghu
Gravatar

# re: How to pull Sharepoint list data from SQL Server

When I initially load the page all of the AJAX like javascript works fine. When I reload or navigate to another page (such as from product.php?id=1 to product.php?id=2) the javascript stops working (ie. lightbox, ajax load, etc.)....
11/2/2010 7:05 AM | online casinot
Gravatar

# There is an ADO adapter for SharePoint

There is an ADO adapter for SharePoint 2007/2010 and WSS 3.0/4.0. It's called Camelot .NET Connector for Microsoft SharePoint.

It enables you to query lists in SharePoint with standard SQL language, using SharePoint as the data layer and not querying the underlying database at all. In example
SELECT * FROM `my list`
INSERT INTO calendar (EventDate,EndDate,Title,Location) VALUES ('2010-11-04 08:00:00','2010-11-04 10:00:00','Morning meeting with Leia','Starbucks')
DELETE FROM `Corp Images` WHERE image_name = 'marketing.jpg'

Currently there is a free testing period of the new beta!
11/2/2010 10:12 PM | Eric Herlitz
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Hi ,
I am trying to populate my data dropdown with the data from sql database
Can somebody help me to get the data from sql to Sharepoint through ADO.NEt as I am getting the error


DataBinding: 'System.Data.Common.DataRecordInternal' does not contain a property with the name 'select CLIENT_ID from av_ELITE_CLIENT'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: DataBinding: 'System.Data.Common.DataRecordInternal' does not contain a property with the name 'select CLIENT_ID from av_ELITE_CLIENT'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[HttpException (0x80004005): DataBinding: 'System.Data.Common.DataRecordInternal' does not contain a property with the name 'select CLIENT_ID from av_ELITE_CLIENT'.]
MakeenagementDocuments.CreateDocument.CreateChildControls() +3040
System.Web.UI.Control.EnsureChildControls() +146
System.Web.UI.Control.PreRenderRecursiveInternal() +61
System.Web.UI.Control.PreRenderRecursiveInternal() +224
System.Web.UI.Control.PreRenderRecursiveInternal() +224
System.Web.UI.Control.PreRenderRecursiveInternal() +224
System.Web.UI.Control.PreRenderRecursiveInternal() +224
System.Web.UI.Control.PreRenderRecursiveInternal() +224
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3394

Regrads
Sanija

11/29/2010 11:54 AM | Sanija
Gravatar

# re: How to pull Sharepoint list data from SQL Server

I use this method all the time. It uses the Access driver from Office to tunnel through the same webservices that sharepoint uses. This works in SQL 2005 for sure, as that is my platform I'm using.

Example
select * from OPENROWSET ('Microsoft.ACE.OLEDB.12.0','WSS;IMEX=1;RetrieveIds=Yes;User id=<your id>;Password=<your pwd>;DATABASE=http://<My SP Site>/<site sub-path>/;LIST=<My List>;', 'SELECT * FROM LIST') B

The < and > are for denoting replacement parms. Don't enter < or > when changing.

In the above, the driver name specifies ACE Oledb.

The WSS specifies WSS (Windows Sharepoint Services)

IMEX specifies Read (1) or Read / Write (2).

User id is the user credentials you want to connect with. Since it uses the Sharepoint List WebService to get the data, user security applies same as the website ui based access.

Password is the user id password, same as logging into the front-end. (note: this depends heavily on the credential challenging profile you would normall get. So your mileage may vary. I set up a Stored Name and Password and it seems to make it much simpler.)

Database is the full name to the site containing the list, same as you would type it in the browser.

List is the Name or GUID of the list. Forums say you must use the list GUID, but I have proved that in fact the list name works great.

1/11/2011 12:30 PM | Don Vineyard
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Reading through this post I am very concerned that people are querying SharePoint content databases directly.

This is not supported by Microsoft and you will likely run into performance problems.

I strongly recommend using the SharePoint OM for accessing list data.

There is a reason that MS haven't made it easy to manipulate SharePoint from the SQL DB - it's because you shouldn't be doing it!

If you want to discuss this further I would be happy to hear your views - I'm @benjaminathawes on Twitter.
2/28/2011 6:23 AM | Benjamin Athawes
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Well this KB is an end of story for anyone reading this post and thinking it's a good idea:

http://support.microsoft.com/kb/841057/en-us

Word of caution: Reading random blogs and following them without doing your homework voids warranties.
5/7/2011 1:24 AM | Matt Bramer
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Thanks a Million !
5/25/2011 4:36 AM | Taliib Ali Khan
Gravatar

# re: How to pull Sharepoint list data from SQL Server

@Matt -- whether it's a good idea or not, unfortunately as developers sometimes we have to get data from one source and archive it in another, or meld it etc. I don't advocate writing to the database as that would surely void a "warranty" but merely reading data? meh how does that violate a warranty?

Unfortunately for me I have to pull from sharepoint because that's the "tool" and push into an operational data warehouse. No choice.

As for homework - always always always double check everything you read or write before you test, and when testing test until they tell you to stop testing already and push to prod.
6/8/2011 11:18 AM | Dave
Gravatar

# re: How to pull Sharepoint list data from SQL Server

@Dave,

The point Matt is making is that that accessing SharePoint's SQL databases is unsupported by Microsoft.
The concern is that people will read this post, think its a good idea and risk their environments supportability.

Pulling list data is amongst the most common tasks that a SharePoint developer is likely to undertake. Hence, Microsoft provide an extensive, robust API for doing so and I see no reason not to use it.

Just my $0.02.
6/16/2011 10:34 AM | Benjamin Athawes
Gravatar

# re: How to pull Sharepoint list data from SQL Server

@Matt

Hi, I will like to understand what you are saying.
I am creating reports for my company. I pull data from SharePoint content database tables like AllUserData and AllLists to create stored procedures and views.
Do you mean that I wasnt suppose to be doing that? and
if yes, what is OM and how is it used?
7/20/2011 5:46 AM | Pru
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Benjamin,

Can you explain the model that you are referring to?

Would like to create a report in ssrs, or schedule the list to be sent in an email once a day.

Thanks,
7/27/2011 9:44 AM | J
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Hello,

I am trying to transfer data from SQL nvarchar(50) to a lookup field in SharePoint List.
I am getting the following error:
.....
Information: 0x0 at Data Flow Task, SharePoint List Destination: Affected 752 records in list 'TEST - Servers Inventory' at 'http://teams.ad/server/serverDBs'. Elapsed time is 12286ms

Error: 0x0 at Data Flow Task, SharePoint List Destination: Error on row ID="2": 0x80020005 - Unspecified Error - Check SharePoint Server Logs if possible.
Error: 0xC0047062 at Data Flow Task, SharePoint List Destination [1122]: Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.PipelineProcessException: Errors detected in this component - see SSIS Errors
at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListDestination.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SharePoint List Destination" (1122) failed with error code 0x80131500 while processing input "Component Input" (1130). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

I noticed the 752 items are the one which do not have data in the field I am trying to transfer,.. all the records with this field having a data in are not transfered....

i am using a script component:
/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts;
using System.Data.SqlClient;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
SqlDataReader Reader;

public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
SqlConnection Conn = new SqlConnection("Data Source=VOSSQL1;Initial Catalog=MITSAssets;Integrated Security=SSPI;");
SqlCommand Cmd = new SqlCommand("Select * from dbo.Assets", Conn);
Conn.Open();
Reader = Cmd.ExecuteReader();
}

public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
/*
Add your code here
*/
}
public override void CreateNewOutputRows()
{
while (Reader.Read())
{
Output0Buffer.AddRow();
Output0Buffer.AssetNameO = Reader["AssetName"].ToString();
Output0Buffer.OwnerO = Reader["Owner"].ToString();
}

}
}

but I already tried with "Derived Column" and I got the same error...

any idea?
Thanks,
DOm
7/27/2011 5:03 PM | Dominique
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Its a nice tutorial

Thanks a lot......
8/5/2011 2:05 AM | Raju
Gravatar

# re: How to pull Sharepoint list data from SQL Server

Make Report by Reporting Service with SharePoint List sharepointtaskmaster.blogspot.com/...
8/22/2011 9:47 AM | Thomas Trung Vo
Gravatar

# re: How to pull Sharepoint list data from SQL Server

This is a good post to someone looking for a quick fix, but it will not work should Microsoft decide to change the schema with CU's or SP's. An ideal way is to write .NET code to use the object model to read items of a list or uysing pwershell as well.

reasons to not do it this way are discussed here www.sharepoint4arabs.com/.../Post.aspx?ID=99
9/20/2011 3:27 AM | SR
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET