SQL Anywhere Integration with SharePoint

SQL Anywhere data can be integrated codelessly with native SharePoint lists using the Layer2 Business Data List Connector. Please take a look here for more supported systems and applications.​​​ In case you are looking for Online data integration, you will find the right tool here.

Benefits of SQL Anywhere Integration in SharePoint

• Very easy to setup in a few minutes: Create a SharePoint list, click "Connect to external data source" in the list settings, select the data provider, enter connection settings and data query as shown below. That's it.
• No changes in SQL Anywhere required: No programming, no additional tools.
• Connected data always up-to-date: The connected SQL Anywhere data updates automatically in background (via SharePoint Timer Job), or alternatively, on-demand (Action Menu / Ribbon Button, URL, via workflow, API).
• One-way and optional two-way connection: You can write-back the changes made in SharePoint to the external SQL Anywhere data source automatically with full CRUD (Create / Update / Delete) functionality. The SharePoint list can act as a full-featured front-end for external systems.

• Well-known BCS "external list" issues and limitations are completely solved: ALL list features are to you. Views, sorting and grouping, filters, calculated fields, search, managed metadata. Lookups, additional columns and attachments can be created as normal. All kind of lists can be used, e.g. contacts, tasks, calendar, or custom lists. You can take external data offline via Outlook.

• Workflows and notifications on SQL Anywhere data change: List workflows and change notifications per RSS or email can be used to take business actions in SharePoint, when SQL Anywhere data records are changed.
• Application logging, reporting, and notifications: A SharePoint list is used to store settings and log information. SharePoint item versioning and workflows can be used to manage reporting and notifications. Direct notification per email in case of errors is supported as well.
• Highest Security, best performance, easy to maintain: SharePoint Secure Store can be used to store security relevant configuration information safely in one central place. Users are working with the SharePoint lists as an external data cache with highest security and performance.
• 100+ external systems supported: Layer2 Data Providers included (e.g. for SharePoint/Office 365, Exchange, Dynamics, OData, XML/RSS, SOAP), vendor specific data providers can be used (e.g. SQL Server Oracle, mySQL etc.), 3rd party data providers also supported, e.g. for ERP/CRMs, Facebook or Twitter. See here for supported systems and applications.

SQL Anywhere Specific SharePoint List Configuration Settings

​In the SharePoint List Settings, the data source must be configured as follows to connect to SQL Anywhere:

Fig. Example connection configuration to connect to SQL Anywhere via the .Net Framework Data Provider.

Please note the following about settings, features and possible issues.

• You need to have the SAP SQL Anywhere Database Client for your version of SQL Anywhere on the computer that runs the Layer2 Cloud Connector. You can find the component on the SAP SQL Anywhere Database Client Download page.
• Install the appropriate bit version of the Client. It must match the same bit architecture as the Cloud Connector version installed (32- or 64-bit).
• Please select the "SQL Anywhere <version> Data Provider" from the list of installed providers.
• You can make use of standard connection strings for SQL Anywhere, for example:
  DBN=MyDB; UID=myUser; PWD=myPassword;
  To find more information about Connection strings, see the SAP SQL Anywhere documentation for the appropriate version of the Client.
• Note that you may need to start the database or database server to validate.
• Note that you can use integrated authentication for better security. See the SQL Anywhere User Documentation.
• You can make use of all SQL queries your data provider supports.
• You can make use of aliases in query to force auto-mapping in the Cloud Connector. See the SQL Anywhere User Documentation.
• The primary key is normally set automatically, if available.
• Bi-directional sync is generally supported. You query must be updatable for this (note that most joins are not). You will also need write access rights for this.
• ODBC versions of the SQL Anywhere provider should also work with the Cloud Connector.

Do you have any issues to connect? Please contact sales@layer2solutions.com for next steps.

SQL Anywhere​​​ SharePoint Connection Details

​Provider:

.Net Framework Data Provider for SQL Anywhere <version>

Connection string sample:

DBN=MyDB; UID=myUser; PWD=myPassword;

Select Statement sample: 

select * from yourTable

SQL Anywhere SharePoint Integration - Known Issues & Workarounds

​​The SQL Anywhere data integration with SharePoint has the following known Issues and workarounds:

• Provider Installation with SharePoint 2013 ("The type initializer for 'iAnywhere.Data.SQLAnywhere.SAConnection' threw an exception" error) - in some cases, the provider may not install properly or there may not be proper permissions for BDLC to access the provider files. First, uninstall the provider. Second, check and add permissions to the following location: C:\Users\<SP Application Account>\AppData\Local\Temp\. Third, re-install the provider. If you do not give permissions to the folder before you reinstall, this will not work. Then in some rare cases, you may need to then install one of the more recent versions of the provider (such as v17), and then both will become available/functional. This appears to be due to an issue with the installer itself with older versions.

Ready to go next steps?