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.
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.
- 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.