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 PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in SharePoint, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

*