How to create a SharePoint-to-SharePoint list connection with BDLC?

 

The Business Data List Connector for SharePoint can connect almost any data sources directly with native SharePoint lists via ODBC, OLEDB or .NET based providers. Because auf driver issues with the Microsoft OLEDB driver for SharePoint there were difficulties to connect SharePoint lists to SharePoint lists, e.g. on another installation. Now BDLC 3.4 comes with it's own web services based driver, the Layer2 SharePoint List Provider, that supports SharePoint-to-SharePoint connections out-of-the-box.

 

Please note: This provider is for use with SharePoint 2007 only. For SharePoint 2010 or higher please use this one.

 

 

​You can connect a SharePoint list with another SharePoint list (at the same or at another installation) to pull the data and keep up-to-date with background update as follows:

 

1. Select the Layer2 SharePoint List Provider as driver.
It is automatically provided with the BDLC installation. It is required only at the destination server. The source system is connected via web services. SharePoint 2007 is supported as well as 2010.

 

2. Enter connection string as follows:

 

  • Web- and View-GUID based:

 

WebSiteUrl=https://myserver/;List=ListName;ViewId={ViewId};Login=domain\name;Password=pwd

 

  • or URL-based:

 

Url=http://myserver:80/Lists/mysourcelist/AllItems.aspx;Login=domain\name;Password=pwd

 

  • or with current credentials:

 

Url=http://myserver/Lists/mylist/AllItems.aspx

 

  • with parameters:

 

Url=http://myserver/Lists/mylist/AllItems.aspx?View={7c989618-db65-46ac-9c3b-bd33c222883a}&SortField=Testcol1&SortDir=Descc

 

  • Url or WebSiteUrl specifies the adress of the SharePoint source web site and can contain the complete path to the list.
  • List optionally specifies the name or ID (GUID) of the view in the source list. Guids must not be decoded.
  • Login specifies the user login, optionally including domain. If not present, the credentials of the currently logged in user are used. Please take cate in case of background updates (see below).
  • Password specifies the user password.

 

3. SQL queries 

 

SQL queries are not supported as this time. Create views in the source system to filter data. Use calculated fields in the source systems to simulate "aliases" for field names in SQL queries to fix mapping issues.

 

4. Primary key

 

Set ID as primary key (as pre-filled).

 

5. Add columns

 

The columns of the source list are created automatically in the destination list, if not already available.

 

6. Check Mapping

 

Columns are mapped automatically by internal column name.

 

7. Background Update

 

If background update is planned, please enter user login and password or make sure, that the service account for the timer job has the rights to read your source list.

 

8. Write-back

 

Write-back of changes in the destination list to the source list is not supported yet for SharePoint-to-SharePoint connections in the current version.

 

faq bdlc sharepoint 2007 integration

Fig.: Setup of a SharePoint-to-SharePoint list connection in the list settings dialog with BDLC.

 

 

Please note:

  • Column types, names and settings of the source list are used to create the structure of the destination list.
  • Columns of type managed Metadata, Lookup, User/Group are not fully supported and can be transferred as text-only. You can delete the auto-created columns and replace by the required column type. Please note, that the result of transferred lookup and user / group column content can be undefined, if the context (lookup list, user, group) is not available or has different IDs within the target system.
  • Calculated columns are supported as data source.
  • Column restrictions, descriptions, formatting, default settings, length etc. are taken from source list
  • There is an issue with the preview display on SharePoint 2007 list data source. Additional internal columns are shown.
  • Be careful with with special characters in internal field names of the source list. Sometimes there are issues with the SharePoint web service to retrieve data for those type of columns. 

 

Please take care about the primary key:

 

When selecting the Layer2 SharePoint Provider, ID is set as primary key automatically and the column "bdlcID" is created in the destination list and mapped to host the source ID values.

 

Known Issues:

 

  • Error Message: "List structure could not be created. A column named 'Title' already belongs to this DataTable." Workaround: If you are referencing a view in your connection string, do not include the title field in that view. The connector maps the title field by default, and attempting to add a second mapping via the view appears to be the cause of this error. That is an issue with the provider and will be changed with the next version.

Layer2 Data Provider for SharePoint (CSOM): Use Cases

 

  • ​Retrieve data from another SharePoint list or library (metadata only, no files), e.g. same or other site collection, other SharePoint installation (including externally hosted) and keep it up-to-date automatically in background.
  • Write item changes (insert / update / delete) to another list, e.g. to sync Office 365 / SharePoint Online with local installation.
  • Sync local calendars, tasks, contacts etc. with Office 365, e.g. for mobile access outside company.

Known issues

 

​Calling a webservice over SSL in SharePoint (e.g. Office 365 via CSOM) could raise "Could not establish trust relationship for the SSL/TLS secure channel..." error message.

 

Since Microsoft SharePoint uses its own certificate store, it does not trust the global standard certificates. To make your SharePoint trust your certificates, you have to add them to your trusted certificates in SharePoint Central Administration.

 

That is also an issue with the certificates that Microsoft uses on their Office 365 Login Page and SharePoint Online sites. To make your SharePoint trust these certificates, you have to add them to your trusted certificates in SharePoint Central Administration.

Next Steps

 

​​Register, download and evaluate the free shareware edition of BDLC today here

READY TO GO NEXT STEPS?

Icon for Product Regsitration - Layer2 leading solutions

Register for free download.


Keep your Sharepoint in sync. Download and try today.

Contact Us Icon for Layer2 leading solutions

Questions? Contact us.

We are here to help. Contact us and our consulting will be happy to answer your questions.