SharePoint 2010: External Content Types for DBA’s

One of the biggest annoyances of SharePoint 2007 was the lack of a native ability to work directly with SQL Server tables. SharePoint 2010 has addressed that issue with External Content Types. ECT’s combine the best of both worlds — you can modify and manage tables as usual in SQL Server, and display them (somewhat) easily and beautifully in SharePoint.

It’s a bit of a process to understand how it works, but once you do, you’re set. The process can be broken down like this:

  1. Create relational tables/views in a SQL Server database.
  2. Add the database as a Data Source in SharePoint 2010.
  3. Setup Secure Store service for SSO in SharePoint if necessary.
  4. Create an External Content Type.
  5. Create Association Operation to associate FK/PK*.
  6. Add Read/Update/Delete/Create Operations to the type in SharePoint Designer.
  7. Create Lists based off of those Operations.
  8. Display pretty, functional lists within SharePoint and add, update and delete rows to your heart’s content.
  9. Create SharePoint lists using External Data if you want.

Below is a table that outlines the SQL Server equivalents (or as close as it comes) of ECT’s.

SharePoint SQL Server Notes
External Content Type Table, view or stored procedure One content type, to one SQL Server object. So if you are working with 3 views, you will have 3 External Content Types
External Data System Database  
Operation Query/Statement Not exactly, but you get the idea.
Delete operation DELETE FROM Deletes only one record at a time.
Read Item Operation SELECT TOP 1 *
Create Operation INSERT INTO
Read List Operation SELECT * FROM This shows everything in the table unless there is a filter. You must return less than 2000 rows or it’ll error out.
Update Operation UPDATE
Association Operation Primary Key/Foreign Key
Business Data Connectivity Service
Set Permissions
GRANT All access is denied by default. BDC permissions are set in Central Administration.

I developed these skills while working on a project that uses PowerShell to gather information about my servers and enter them into SQL Server database. From there, I setup a SharePoint site that not only displays that information in a useful way, but also manages the data from the web front-end. Hooray for SP2010!

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SharePoint, SQL Server

Leave a Reply