This is a step by step configuration of an external data type in SharePoint Designer 2013. The first part explains step by step how to create an external data type. The second part shows how to configure the connection for fetching the data from an SQL Server database that is on another server.
So without further ado let’s get to the step by step description:
1. Open SharePoint Designer
2. Go to “External Content Types” and click the “External Content Type” button
3. You will get a window with the new external content type configuration
4. Change the name of the external content type by clicking on it
5. Go to sources configuration
6. Click “Add Connection”
7. For the source type choose SQL Server
8. Specify the database connection properties
9. Now you are ready to connect to the SQL database. Create a table in your database if you haven’t already.
10. Your source connection should now be visible in SharePoint Designer.
11. Now you need to generate the possible operations on the ProjectsTest table. Make a right click on the table an choose “Create All Operations”
12. On the “Operation Properties” screen click next
13. Check if the parameters are configured properly, and if you have no Errors in the “Errors and Warnings” screen.
14. On the “Filter Parameters” screen click next
15. You can now see the defined “External Content Type Operations”
16. Now we can create a new list based on the external content type that we’ve just created. Go to “List and Libraries” and on the ribbon click on “External List”.
17. Pick our external content type and click “OK”
18. Choose a name for out external list and click “OK”
19. If the SQL Server database is on the same server you should see the list.
If the SQL database is on another server, than you will see an “Access Denied by Business Data Connectivity” error. Handling this problem is covered in the next part of this tutorial.