The Problem
I had a large dataset stored in SQL Server that I wanted to use in SharePoint 2010 as a lookup field against a contract record. Due to the external data throttling limit in SP2010, I can’t browse through more than 2000 records. This blog is how I managed this with some SQL Stored Procedures and XSLTView changes to page through 2800 rows in the database without modifying SharePoint’s throttling limits.
I started by creating an External Content Type and pointing it directly to my table; follow this MSDN article for how to do this: http://msdn.microsoft.com/en-us/library/ee557243.aspx. Initially I thought that the “All Items” view only selected a small number of rows so displaying in batches of 25 would work but, when you try to display the list you get correlation ID error, would have expected “You selected too many items” rather than an error.
I then did some further investigation and discovered External Data has throttling applied by SharePoint, this can be overridden using PowerShell but can have a performance impact and mostly relates to coded solutions: http://blogs.msdn.com/b/bcs/archive/2010/02/16/bcs-powershell-introduction-and-throttle-management.aspx . Setting the throttling off then showed my list but that wasn’t the ideal option for a production environment.
Steps to my solution
The solution is based on 2 SQL Server stored procedures, an External Content Type and a customised XSLTListView.
First create a stored procedure to select rows in batches and test by sending row numbers and limit sizes.
The SP has 2 parameters, one for the row number (called PageNo) and the other for page limit. The first parameter will be used later to create a filter when picking using as a lookup in a list.
USE [Gel_ExternalData]
GO
/****** Object: StoredProcedure [dbo].[getSuppliers] Script Date: 09/05/2011 10:17:46 ******/
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
— =============================================
— Author: Alan Marshall
— Create date: 05/04/2011
— Description: Get Vendors for External Content Type
— =============================================
ALTER
PROCEDURE [dbo].[getSuppliers]
— Add the parameters for the stored procedure here
@SupplierName nvarchar(200)
=
null,
@pageNo int
= 1,
@limit int
= 200
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET
NOCOUNT
ON;
IF @pageNo IS
null
SET @pageNo = 1;
DECLARE @startIndex int
–SET @startIndex = ((@pageNo – 1) * @limit) + 1
SET @startIndex = @pageNo
DECLARE @endIndex int
SET @endIndex = @startIndex + @limit
DECLARE @SupplierNameLookup nvarchar(200)
=
null
IF @SupplierName IS
NOT
null
SET @SupplierNameLookup =
‘%’
+ @SupplierName +
‘%’;
WITH [CTE] AS (
SELECT
ROW_NUMBER()
OVER (ORDER
BY [MAILING_NAME]) AS [RowNumber]
,[ADDRESS_NUMBER]
,[MAILING_NAME]
,[LEGAL_ENTITY]
,[ADDRESS_LINE_2]
,[ADDRESS_LINE_3]
,[ADDRESS_LINE_4]
,[CITY]
,[POSTAL_CODE]
,[COUNTRY]
FROM [Gel_ExternalData].[dbo].Suppliers
WHERE [MAILING_NAME] LIKE
ISNULL(@SupplierNameLookup, [MAILING_NAME])
)
SELECT
*
FROM [CTE]
WHERE [CTE].[RowNumber] >= @startIndex
AND [CTE].[RowNumber] <= @endIndex;
END
This may not be the most efficient method but I’m not a TSQL expert and I found this solution on another blog
Create a stored procedure to select a single row
USE [Gel_ExternalData]
GO
/****** Object: StoredProcedure [dbo].[getSuppliersItem] Script Date: 09/05/2011 16:21:42 ******/
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
— =============================================
— Author: <Author,,Name>
— Create date: <Create Date,,>
— Description: <Description,,>
— =============================================
ALTER
PROCEDURE [dbo].[getSuppliersItem]
— Add the parameters for the stored procedure here
@AddressNumber int,
@pageNo int
= 1,
@limit int
= 200
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
WITH [CTE] AS (
SELECT
ROW_NUMBER()
OVER (ORDER
BY [MAILING_NAME] DESC) AS [RowNumber]
,[ADDRESS_NUMBER]
,[MAILING_NAME]
,[LEGAL_ENTITY]
,[ADDRESS_LINE_2]
,[ADDRESS_LINE_3]
,[ADDRESS_LINE_4]
,[CITY]
,[POSTAL_CODE]
,[COUNTRY]
FROM [Gel_ExternalData].[dbo].[Suppliers]
WHERE [ADDRESS_NUMBER] = @AddressNumber
)
SELECT
*
FROM [CTE]
END
This SP must have a unique identifier that will be used when the user chooses to view an item in the list. In my case this is ADDRESS_NUMBER.
You’ll have noticed I have a hard coded sort field which is needed to page through the items. A parameter can be added for this later but outside scope of this blog at the moment.
Create an external content type
- Open External Content Types in SharePoint Designer
- Select new content type button on the ribbon
- Provide a name for your content type
- Use Generic list although you can use contacts etc but I suspect you’ll hit the hard limit in Outlook.
- Select to link to external data source
-
Add a connection and select SQL Server
- Note: you must have direct access at this point even if you intend to use Secure Store Service later. I built this solution on a single server so didn’t need Kerberos.
- I noticed the error messages are vague if the connection is unsucceful for instance, I got an unable to connect message even though I only got database name wrong and thought it was a credentials issue
- Open up Routines in the returned database view
- Right click on the getSuppliers SP and select Read operation
- Enter the display name, note that this appears as the name of the default view in SharePoint when you create a list
- Your identifier must be Int32 as I found others don’t work well.
- You now need to specify the settings for the filters
- Click on the link to add a filter for your identifier (ADDRESS_NUMBER)
- Provide a sensible name and set this as a Comparison filter type as below:
- Set PageNo to be a comparison. Don’t be tempted to select PageNumber as the FilterType.
- Set Limit to be of type limit and 200
- Click Next and Map all the fields or rename as required. You need to select Map to Identifier for your unique column.
- If you renamed the identifier in the filters, you need to select it here for the ADDRESS_NUMBER
- Click Finish
- Select the paged items stored procedure and Read Items operation
- Set up the parameters as before with the SupplierName configured as below as this field is used for searching in the lookup picker:
- Set PageNo to be a comparison
- Set Limit as type Limit
- Click Next and then set the fields you want in the Show in Picker and rename to the same as the read item operation
- Click Finish and then Save the ECT.
- Now you can create a list and forms using the button on the ribbon
-
Before you browse to the list, you need to set permissions on the BDC definition
- Open Central admin and navigate to Manage Services
- Select your Business Data Connectivity Service
- Locate the definition you just created and select Set Permissions from the drop down.
- Add administrators and Users with appropriate permissions
- Navigate to your new list and select modify view in the ribbon
-
Set the data source filters.
- PageNo = {dvt_firstrow}
- Limit =25
- Set the Item limit to match the limit above
I then used SQL profiler to see what calls SharePoint was making to the stored procedures
Startup SQL Profiler and capture events for the database. Check values being passed in.
So everything seemed to be working but wait what’s happening on page 2! It sent the correct stored procedure parameter i.e. @PageNo=26 and @limit=25 but it shows only 1 record. I ran the SQL procedure again pasting it from the profiler and it was returning 25 records so what happened.
It turns out that the XSLTListView on the view actually queries all the data and then using XSL selects only the rows it is interested in. Apart from this seeming wildly in efficient, it needs to do this to create the filter drop downs over the whole dataset.
When you look at the XSLTListView XSL stylesheet, you will find the following line:
<xsl:param name=”AllRows” select=”/dsQueryResponse/Rows/Row[$EntityName = ” or (position() >= $FirstRow and position() <= $LastRow)]”/>
What this does is select items from row 26 to row 52. This being the start of the second page first item and last row is firstrow + the limit +1. So it then selected from the returned SQL data row 26 as there were no more rows.
This was easily fixed by doing the following:
<xsl:param name=”AllRows” select=”/dsQueryResponse/Rows/Row”/>
Note: remember to remove all the ghost reference in SP Designer otherwise it won’t save your changes
This new XPath query selects all the rows that come back because there will only ever be 25 or whatever the limit is. This fixed page 2 as I could now see all the records but at the bottom of the page, there wasn’t a right arrow to go to the next page so I moved my attention to the Navigation XSL template and the CommandFooter.
There were 2 values that were essentially incorrect based on my solution; the last row and the row count. The first row would correctly start 26 for the second page but the last row would also be 26 and the rowcount returned 26. So I needed to make the XSLT think that the last row was potentially the first row + the page limit making 51 and the total rows in my table was at least more than currently displayed.
I created 2 new variables to set the last row to be the first row 26 + the current row count and then created a new current row count to always be more that the potential page content by adding it to the Lastrow value.
<xsl:template name=”CommandFooter”>
<xsl:param name=”FirstRow” select=”1″/>
<xsl:param name=”LastRow” select=”1″/>
<xsl:param name=”dvt_RowCount” select=”1″/>
<xsl:variable name=”TrueLastRow“>
<xsl:number value=”$FirstRow + $dvt_RowCount”/>
</xsl:variable>
<xsl:variable name=”Truedvt_RowCount“>
<xsl:number value=”$LastRow + $dvt_RowCount”/>
</xsl:variable>
<xsl:if test=”$FirstRow > 1 or $dvt_nextpagedata”>
<xsl:call-template name=”Navigation“>
<xsl:with-param name=”FirstRow” select=”$FirstRow” />
<xsl:with-param name=”LastRow” select=”$TrueLastRow”/>
<xsl:with-param name=”dvt_RowCount” select=”$Truedvt_RowCount”/>
</xsl:call-template>
</xsl:if>
The next variable which prevent the next option displaying correctly was the RowTotalCount which is passed into the XSLT by the calling web part code. As I can’t retrieve how many rows are actually in my database table I fudged this and put in 1 million as the value. Essentially this means that the value for LastRowValue will always be equal to LastRow.
<xsl:template name=”Navigation”>
<xsl:param name=”FirstRow” select=”1″/>
<xsl:param name=”LastRow” select=”1″/>
<xsl:param name=”dvt_RowCount” select=”1″/>
<xsl:variable name=”TrueRowTotalCount“ select=”1000000″ />
<xsl:variable name=”LastRowValue”>
<xsl:choose>
<xsl:when test=”$EntityName = ” or $LastRow < $TrueRowTotalCount”>
<xsl:value-of select=”$LastRow”/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of
select=”$TrueRowTotalCount”/>
</xsl:otherwise>
</xsl:choose>
The final result is I got to records 2003 – 2028 as shown below before I got bored clicking next:
If you try this out your will notice that the last page is blank when you get to the end of your data but you could probably add a count to the /Row XPath and show a message instead and back button.
Filtering won’t work either as it filters over just the 25 items shown and not the whole dataset.
Sorting can be fixed by passing dvt_sortfield and dvt_sortdir and handling this in your stored procedures.
To complete the solution I created a document library and added my Suppliers List as an “External Data” column:
Add when I upload a document I can search through the list of suppliers to pick the one I want. You can remove the PageNo option by setting its type to Limit instead of comparison. Notice that this does return a warning about too many results.
ECM Team Blog 