SharePoint SQL Business Data Connector Configuration

Activating the feature

Navigate to the site settings. In site actions click on manage site features and look for the feature named Business Data Connector and activate it.

Activate the Business Data Connector feature

Configuring actions for content types

Navigate to a SharePoint list or document library. In list settings/advanced settings enable the management of content types and click OK.

Now you can see the content types, click on the first one to configure the SQL Business Data Connector settings.

click on content type settings, SQL Business Data Connector

Below the import setting dialog the settings page shows sections and check boxes for each configurable event.

ItemAdding and added events occur when an item or document is added to SharePoint.

enable the ItemAdding event action for this content type

ItemUpdating and updated events occur when an item or document is updated.

Update and delete event actions

The ItemDeleting event occurs before an item being deleted is registered on SharePoint. It is still possible to access the data and perform a cleanup.

Configuring an action for ItemAdding

Check the enable ItemAdding checkbox and enter the SQL Server connection string.

enable the itemAdding event action and enter a SQL Server connection string

Click on Test Connection and proceed to the SQL statement details.

In type of operation you may select SELECT and UPDATE. SELECT will return values in parameters, UPDATE will not.

Choose SELECT as the type of operation, and enter 1 as the number of params in. Then enter 1 as the number of values out.

configure a sql statement and parameters

Enter a valid T-SQL statement, the example below should work with @in1 as varchar and @out1 as varchar.

declare @table as table
   inParam varchar
insert into @table (inParam) values(@in1)
select inParam from @table

Configuring actions for other events should be exactly the same.

Configuring an action for a timer job

Check the check box Use this receiver to update items in the timer job and select the schedule from the drop down below.

configure timer job for business data synchronization

To use a scope select a view from the drop down below.

Configuring event firing behavior

Check the enable event firing when item is updated check box. This enables SharePoint designer workflows to be launched when an item is updated.

configure event firing behavior

Update all items

This check box should be checked when you want to update all items of this content type initially and you don’t want to use a timer job. When you use this configuration all items in the list will be updated and all actions configured for updating and updated will be executed.

update all items of this content type

Export settings/Import settings

To export settings you must pick a file location and a file name. To import settings pick the file and click on Import. Finally enable the actions.

export settings on save