External Content Type based on Stored Procedure in SharePoint 2013 using Meta Man

In this tutorial we will show you how to connect to your External System using Meta Man, and use an SQL Stored Procedure to return data to a SharePoint Business Data List. In this walkthrough we will be creating just a Finder Method with no filters so our Stored Procedure should have no parameters defined.

  1. Prerequisites:
    • SharePoint Server 2013
    • Meta Man (it doesn’t necessarily need to be installed on the SharePoint server but it requires .net Framework 4.5 installed on the machine where you are going to run Meta Man from)
    • MS SQL server with any sample database (for this walk through we are going to use the NorthWind sample database )
    • An SQL Stored Procedure without parameters such as: USE [Northwind]
      GO
      CREATE PROCEDURE [dbo].[GetAllTerritories]
      AS
      BEGIN
      SET NOCOUNT ON;
      SELECT
      * FROM [Territories]
      END
      GO
  2. Open Meta Man and choose to connect to a SQL Server data source. Select or enter the database name you wish to work with and press arrow in order to connect to selected database.Connect to MS SQL server
  3. Upon successfully connecting to SQL Server you’ll see another window. In order to get a list of stored procedures click “Stored Procedures” item in the list on the left pane. Now we can see listed all the stored procedures for this particular database.List of stored procedures
  4. We create an external content type by dragging and dropping [dbo].[GetAllTerritories] stored procedure onto our design surface which is the large area on the right side. Drag a stored procedure on the design surface
  5. When you drop the stored procedure you will be shown the following dialog, which allows to configure the external content type before creation. You should enter the external content type name and select the operation type as “Finder”. Click “Create” button.Configure the external content type
  6. You should have the following External Content Type on your Diagram Created External Content Type
  7. Now we need to deploy our BDC model. First we need to set URL of where you want to deploy your model. We set this URL by going “Settings” from the title bar.Meta Man settings
  8. In the model deployment URL textbox you should manually type URL of SharePoint 2013 Central Administration . Click “Save” button.Set BDC model deployment URL
  9. Once you have saved the URL to deploy your BDC model file to simply click “Deploy” button and your BDC model file will get deployed.Deploy BDC model file
  10. SharePoint 2013 will now go through and validate your BDC model file is correct, once it is done you will get a success dialog BDC Model was imported
  11. Browse to the SharePoint page where you want to add a Business Data List web part. Add a new Business Data List Web Part to this SharePoint page.Add Business Data List Web Part 
  12. Click on the “Open the tool pane” linkOpen the tool pane 
  13. Click on the icon to show the available External Content Types Open the picker to select the External Content Type
  14. Select our “Northwind_MetaMan.Territory” External Content Type, click “OK”. Click “OK” on the tool pane then.Select External Content Type
  15. Your External System data is now displayed in the SharePoint Web PartExternal data from MS SQL database is displayed on SharePoint 2013 page 

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

<Dmitry Kaloshin/>