CEDANIEL.COM

This post applies to Microsoft Office SharePoint Server 2007 and WSS 3.0

Scenario: In many cases SharePoint Lists can grow to be 1000s of rows. Often, you are required to build a report of some kind based upon the data being collected in the SharePoint list. It is an easy solution to build custom views with SharePoint services or SharePoint Designer to render the data in the form of a report. However, I have noticed that once the data grows to a large number, a more advanced approach is needed to aggregate the data with a method that is optimal for performance. When the list grows to 1000s of rows, views tend to be more difficult and perform poorly (depending on what you are trying to display).

Solution: Initially, I started to experiment with getting data out of a SharePoint List via MS Access 2007. There is a great tool that is available via MS Access that allows you to do this and may be optimal for your solution. However, in my experience the performance of MS Access begins to decline greatly once you get data from a list in the numbers of 1000s. It also seems difficult for Access 2007 to accomplish this task across a network when retrieving 1000s of rows of data.

There are also some custom solutions that can be achieved by building a web application that pulls data from a list by programming against SharePoint services. However, in most cases I found this to be just too much trouble for such a simple task.

What I have successfully used and recommend is SQL Server Integration Services. This solution will work for both SQL Server 2005 (SP 2) and SQL Server 2008. Microsoft has released a tool that can be installed and used with the Business Intelligence Studio that comes with SQL Server 2005/2008. This tool installs two additional options that can be used when building an Integration Services Package. It allows you to pull data from a list and send it to SQL server – OR – take data from SQL server and send it to a SharePoint List. It is very effective.

Read this post from MSDN for instructions: http://msdn.microsoft.com/en-us/library/dd365137.aspx

Go here to download the .msi files (You will see the 2005 adapter available for download): http://www.codeplex.com/SQLSrvIntegrationSrv/Release/ProjectReleases.aspx?ReleaseId=17652

Additionally, using this package I was successful in pulling data from a SharePoint List and Importing it into a MySQL database running on Mac OSX server. To do this, you will need to install the MySQL ODBC or ADO.net drivers onto the SQL server box. I may create a separate post specifically on how to do this.

This post applies to Microsoft Office SharePoint Server 2007 and WSS 3.0

Scenario: You have created a custom .aspx page with SharePoint Designer that has a custom data view that shows data from a SharePoint list. You Save the custom site with its custom .aspx pages and move it to another SharePoint site. When opening up the custom .aspx page in the new location, you get an error on the page that says, “Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Windows SharePoint Services-compatible HTML editor such as Microsoft Office SharePoint Designer. If the problem persists, contact your Web server administrator.” How do you fix this?

Solution: Open the custom .aspx page in SharePoint Designer and look for the tag that encloses the DataFormWebPart that is being used to render the data view from the list. It should have a tag that has:

<WebPartPages:DataFormWebPart runat=”server” ………….  ><ParameterBindings>
Now look for:

<ParameterBinding Name=”ListID” Location=”None” DefaultValue=”########-####-####-####-############” />
This ParameterBinding tag that has the ListID defined will be using the ListID of the Original List (the old site). You will need to change this definition to the ListID of the new site.

Now, you will need to go to the List that is located on the new site that the custom .aspx page is looking for to render the data to the view. Once you locate the list, click on Settings  List Settings.  Copy the URL that is rendered in the Address Bar to a text editor. The URL will look something like:

http://vhamoss/sites/whateversite/_layouts/listedit.aspx?List=%7B11111111%2D1111%2D1111%2D1111%2D111111111111%7D

In your text editor, delete everything in the URL up to the % symbol, just after the “List=”.

Now change the “%7B” to “{“.

Change all instances of “%2D” to “-“.

Finally, change the “%7D” to “}”.

You should now have the ListID:  {11111111-1111-1111-1111-111111111111} (Obviously you number will be different than a bunch of ones).

Now that you have the new ListID, go back to SharePoint Designer, open the new custom.aspx page, and locate that tag again as described above and substitute the old ListID with the new one that you have just created in your text editor. You will not need to past the “{“ “}” symbols. You will just need the numbers with the dashes (same format as the old one).
Save the edited custom .aspx page in SharePoint Designer and refresh the page in your Browser.

You will now see the custom .aspx page in the new location with the correct data and no errors.

ALSO NOTE: It is easier to just do a FIND/REPLACE of the old List ID with the new LIst ID across the entire Custom page. It will most likely be a case where an instance of the List ID will need to be replaced in more locations than the above referenced location (depending on how you setup your data view).