- About Us
by John Bonfardeci
It’s relatively simple to create external content types in SharePoint Designer via Business Connectivity Services (BCS) and many articles have been published covering the steps. Furthermore, you can filter data with the REST `$filter` parameter. However, SQL server will return all the data in a view or table and only then will SharePoint filter the results. For example, if a view returns 10,000 rows and you only need 10, this can be an obvious performance issue. To compound the problem, SharePoint will throw an error if your external list returns more than 2,000 rows.
You can overcome the 2,000-row limit by setting a default Limit filter on your external content type in SharePoint Designer and include a CAML query in your AJAX POST request as outlined in this Microsoft blog post. Now in simple cases, filtering a view in a database can be just as fast as filtering with parameters in a stored procedure, but not always. Using a CAML query to filter results from a view isn’t the same and it can be much slower depending on the query and how much data a view returns.
With a recent project, I had a payroll report view with 14 joins, complex aggregation, and calculations requiring 3 common table expressions (CTE). The first CTE joined and grouped the tables the report required, and the other 2 CTEs calculated the total payroll, overtime, and average OT rates from multiple timesheets for one client with multiple care attendants among different job codes. Filtering rows from the view took ~45 minutes (Figure A) as opposed to 7 seconds (Figure B) with the same code in a stored procedure with filter parameters. The dramatic speed increase was directly due to passing filter parameters to the first CTE in the stored procedure, something that isn’t possible with views – except for using the SPID approach, but this is complicated. Time comparisons between the view and the stored procedure are shown below.
Figure A. Filtered view results in SSMS. Time to complete ~45 minutes.
Figure B. Stored procedure results in SSMS. Time to complete in 7 seconds.
The remainder of this article will detail how to connect SharePoint to a stored procedure, step-by-step.
Before you can set up an external content type to a database, your SharePoint site must have Business Connectivity Services (BCS) and Secure Store Service (SSS) enabled. Additionally, you’ll need either an Active Directory account or username and password for access permissions to the database. This account will be stored in SSS and used to access your database. If you don’t have access to your Central Administration site, work with your SharePoint administrator to set up these features and credentials.
There are numerous online resources for learning how to set up BCS and SSS, but this article is focused on the specific steps for executing stored procedures, and therefore these steps are outside the scope of this article.
To learn how to setup BCS, please refer to the article, SharePoint Online to Azure SQL Database: Part 1
Going forward, it’s assumed you have BCS and SSS set up in your SharePoint environment already.
For this example, we’ll use the dataset from the 2018 World Happiness Survey which can be downloaded here. After downloading, save the first tab of this Excel workbook as a tab delimited file titled, “WorldHappiness2018” somewhere on your local drive. You can use the Import Flat File wizard in SQL Server Management Studio (SSMS) to import the dataset. Right-click on your database and select Tasks > Import Flat File… (Figure 1).
1a) In the Specify Input File section of the wizard, click Browse… to locate the file to import. Click the Next button at the bottom of the wizard (Figure 1a).
1b) In the Preview Data section of the wizard, you should now see a preview of the dataset (Figure 1b). Click Next again.
1c. In the Modify Columns section of the wizard, be sure to check Allow Nulls on all columns. The import wizard only looks at the first 10 rows to determine the datatypes and if they’re nullable. If you don’t check Allow Nulls, you’ll run into import errors (Figure 1c). Click Next.
1d) After the import operation is complete, press the Close button (Figure 1d i).
Figure 1d i.
You should be able to see the new table within SSMS (Figure 1d ii).
Figure 1d ii.
2a) We’ll write the following stored procedure to filter the table by @country and an optional @year parameter.
Take note of the @dummy parameter. We’ll see how this is used to circumvent an obstacle when setting up the Read Item operation of the external content type in SharePoint Designer.
CREATE PROCEDURE dbo.spWorldHappiness2018 @country nvarchar(50), @year int = null, @dummy int = null AS SELECT CONVERT(int, ROW_NUMBER() OVER(ORDER BY [year])) AS RowNum ,[country] ,[year] ,[Life_Ladder] ,[Log_GDP_per_capita] ,[Social_support] ,[Healthy_life_expectancy_at_birth] ,[Freedom_to_make_life_choices] ,[Generosity] ,[Perceptions_of_corruption] ,[Positive_affect] ,[Negative_affect] ,[Confidence_in_national_government] ,[Democratic_Quality] ,[Delivery_Quality] ,[Standard_deviation_of_ladder_by_country_year] ,[Standard_deviation_Mean_of_ladder_by_country_year] ,[GINI_index__World_Bank_estimate_] FROM [dbo].[WorldHappiness2018]
Before proceeding, it’s worth mentioning Multi Factor Authentication (MFA) in SharePoint Designer. Referencing an excerpt from the aforementioned article, SharePoint Online to Azure SQL Database
“Here is another gotcha that might get you. If you attempt to open your site and get a message telling you that there is a problem with your account, then you probably need an App Password. If your admin has set up multi-factor authentication (MFA) for your organization, then you will need an App Password. How do you know if your admin has set up MFA? If you log in with a username and password and then get a text message sent to your phone to verify your login, MFA has been established.”
3a) In SharePoint Designer, open your SharePoint site’s URL and click on the External Content Type button to create a new external content type (Figure 3a).
3b) Name the external content type, “spWorldHappiness2018” then select Click here to discover external data sources and define operations (Figure 3b).
3c) Expand the Routines folder. You should see the stored procedure we created in the previous step (Figure 3c).
3d) Right-click on the stored procedure and select New Read List Operation (Figure 3d).
3e) Enter the operation name. To keep it simple, I typically name the operation the same as the stored procedure. This will be what you’ll set the Name attribute to within the Method element of the CAML query (Figure 3e).
3f) Within the Input Parameters section, select @country under Data Source Elements. On the right under Properties, click (Click to Add) next to Filter (Figure 3f).
3g) In the Filter Configuration window, click the New Filter radio button and enter, “Country” as the new filter name. You don’t need to change anything else. The filter will correspond to the @country parameter of the stored procedure. Click OK (Figure 3g).
3h) In the Input Parameters section, select @year under Data Source Elements. On the right under Properties, click (Click to Add) next to Filter (Figure 3h).
3i) Within Filter Configuration, click the New Filter radio button and enter, “Year” as the new filter name (Figure 3i). Click OK.
3j) Finally, select @dummy and click Click to Add next to Filter. Select New Filter, enter “RowLimit” as the name then change the Filter Type to “Limit” (Figure 3j). Press OK.
3k) Set the Default Value to 2000. This is the maximum number of rows SharePoint will return without throwing an error (Figure 3k). Press Next.
3l) Set RowNum as the identifier by selecting it and check Map to Identifier in the Properties section on the right (Figure 3l). Press Finish.
3m) For the second and last operation necessary to create an external list, right-click the procedure and select New Read Item Operation (Figure 3m).
3n) Set the Operation name to “ReadItem” and the Display Name to “Read Item.” It doesn’t matter what you name it but remember that any spaces in the operation name will show up as ”%20” in the URL of the operation page. You’ll never need to access it and its existence is extraneous, but you need at least one Read List and one Read Item operation to save and external content type in SharePoint (Figure 3n). Click Next.
3o) Select @dummy and check Map to Identifier. This is the primary need for the dummy variable. If your procedure doesn’t expect a parameter that has the same type as your primary key, you’ll need this workaround to just get past this step. Notice the two red warning messages at the bottom (Figure 3o).
3p) After selecting Map to Identifier, these warnings disappear (Figure 3p). Click Next.
3q) Select RowNum and check Map to Identifier on the right (Figure 3q). Press Finish.
IMPORTANT: Click the Save button in the upper-left corner of the SharePoint Designer window.
4a) In SharePoint designer, select External Content Types of the Site Objects section (Figure 4a).
4b) Locate “spWorldHappiness2018” and right-click it. Select External List to create a new external list (Figure 4b).
4c) Name it, “WorldHappiness2018” and click OK (Figure 4c).
5a) Somewhere in your SharePoint site (I chose Site Pages), create a new .aspx page. Right-click the file and select Edit File in Advanced Mode (Figure 5a).
5c. Save the file and open in your browser. I recommend Google Chrome.
In the form, enter the name of a country in the Country field and optionally a year in the Year field. Press Get Data. After the asynchronous POST request completes, you should see a preview of the JSON returned below the form (Figure 5c).
There it is! I was elated the first time I saw JSON returned from a stored procedure call from SharePoint. Unlike a regular REST response, notice all the numeric types have been returned as strings. You’ll need to convert the values to the correct types including Boolean, date, integer, and float.