X
logo
Bringing your data sources together

Moss Bdc Connect Sql Stored Procedures

Overview

In this example we will connect the Products table from the popular Northwind database to the Business Data Catalog in Microsoft Office SharePoint Server (MOSS BDC) via Stored Procedures.

Step 1 - Create Stored Procedures

The sample T-SQL below shows how to create the Stored Procedures to use with the BDC.

The Finder Stored Procedure

This procedure returns a list of records from the database.
CREATE PROCEDURE GetProductList
AS
 
SELECT 
    [ProductID],
    [ProductName],
    [SupplierID],
    [CategoryID],
    [QuantityPerUnit],
    [UnitPrice],
    [UnitsInStock],
    [UnitsOnOrder],
    [ReorderLevel],
    [Discontinued]
FROM
    [Products]

The SpecificFinder Stored Procedure

This procedure returns a single record from the database.
CREATE PROCEDURE GetProduct    
    @ProductID int
AS
 
SELECT 
    [ProductID],
    [ProductName],
    [SupplierID],
    [CategoryID],
    [QuantityPerUnit],
    [UnitPrice],
    [UnitsInStock],
    [UnitsOnOrder],
    [ReorderLevel],
    [Discontinued]
FROM
    [Products]
WHERE 
    [ProductID] = @ProductID

The IDEnumerator Stored Procedure

This procedure returns a list of Primary Keys from the database for use with the Business Data Catalog Search Service to allow content to be crawled.
CREATE PROCEDURE GetProductIDs
AS
 
SELECT 
    [ProductID]
FROM
    [Products]

Step 2 - Connect to the SQL Database

Choose the Connect Data Source link in the Data Source Schema window to open the Data Source Connection Dialog.
  • Connect to SQL Data Source
    Connect Data Source
  • MOSS BDC Design Studio Main Window
    Northwind Database Opened in Schema Window
 

Step 2 - Create Product Entity

Use the BDC Entity Wizard to Create the default default options for the Entity.

Start the Wizard

To Start the Wizard drag the GetProductList finder Stored Procedure from the Schema window onto the design surface with the mouse.

Step through the Wizard

  • Name the Entity
    Name the Entity
  • Select Entity Method Stored Procedures
    Select Entity Method
    Stored Procedures
  • Select Entity Primary Key
    Select Entity Primary Key
  • Select Entity Link Field
    Select Entity Link Field
 

Products Entity created

  • Entity displayed in MOSS BDC Design Studio
    Entity displayed in MOSS BDC Design Studio
 

Step 3 - Generate BDC Xml

Under the Build menu select Build BDC Xml to generate the Xml document required to upload to your MOSS Server.
  • Generated BDC Xml
    Generated BDC Xml
 

Step 4 - Upload to your MOSS Server

Open a browser to your MOSS Central Administration Console and locate the Business Data Catalog application to upload the Xml Application definition.
Need Help?
We have a dedicated support team available to help you with your queries.
If you have a support enquiry please e-mail us at support@simego.com.
If you have a sales enquiry please e-mail us at sales@simego.com.