Posted on

Create an external content type in SharePoint to connect to SQL data

External content types are used to create external lists in SharePoint. Before you can create the list/connection, you must first have created the content type.

Create the content type

1. Open SharePoint Designer and open your site

2. Select “External Content Types” from the “Navigation menu

3. Select “External Content Type” from the “New” ribbon section

4. Rename the content type by selecting the name and entering a new one.

Connect it to SQL data

1. Select “Click here to discover external data sources and define operations”

2. Click “Add Connection” if you haven’t previously connected to the SQL server

3. Select SQL Server

4. Enter the Server and database names. Select User’s identity (your credentials) or a service account to impersonate (and enter it in Secure Store Application ID). Click OK.

5. SharePoint External lists require at least Read item and read list operations. Right click the table or view to which you’re connecting and select “Create all operations” or choose specific operations if you need to, in order to match data source settings.

6. Click next.

  • Resolve any errors or warnings. This may mean checking/unchecking required/read-only to match data source settings. You’ll also need to select one field as “Map to Identifier.”
  • Uncheck any columns you don’t want imported.
  • Click next again.

7. To filter data pulled in (preventing massive imports), you can optionally add a filter otherwise click Finish.

8. Click “Create Lists & Form,” and “Save” when prompted

9. Name the list and give it a description (optional). Click OK.

10. Check your Site Contents to find your new list.

Troubleshooting

Large lists

Is your list larger than 2,000 items? Here’s how to get past the “resource throttle” error.

Target application not found

If you get “Target Application Not Found,” make sure you’ve setup your Secure Store Application ID in the admin center.

  1. Go to service applications

2. Click “Secure Store Service Application”

3. Click “New” if the app isn’t listed, or update it if there.

4. If created new, you’ll need to enter credentials as well. Format the username as Domain\Username.

Posted on

Solution: “Database Connector has throttled the response” error on external list in SharePoint

Applies to SharePoint 2016, can be adapted for O365

If you’re connecting to an external list of significant size, you may run into the following error:

Database Connector has throttled the response. The response from database contains more than ‘2000’ rows. The maximum number of rows that can be read through Database Connector is ‘2000’. The limit can be changed via the ‘Set-SPBusinessDataCatalogThrottleConfig’ cmdlet.

1. Log into your SharePoint server

2. Open your SharePoint 2016 Management Shell as administrator (right-click, run as administrator)

3. Connect to your site

Connect-Site -url https://sharepoint.contoso.com

4. Get ALL service application Proxy IDs (you’ll need this in a later step)

Get-SPServiceApplicationProxy

5. Note the ID of the “Business Data Con…” application

6. Set a new variable “$serviceapp” to that app using the ID you noted as its identity

$serviceapp = Get-SPServiceApplicationProxy -Identity a12b3cf4-d12j...

7. Run the following cmdlet to increase the throttle limit

Get-SPBusinessDataCatalogThrottleConfig -Scope Database -ThrottleType Items -ServiceApplicationProxy $serviceapp | Set-SPBusinessDataCatalogThrottleConfig -Maximum 1000000000 -Default 500000

Your external list will now load correctly!

More info:
https://docs.microsoft.com/en-us/powershell/module/sharepoint-server/get-spbusinessdatacatalogthrottleconfig?view=sharepoint-ps