SQL Server Connector

The SQL Server Connector allows you to connect to a remote SQL Server database and retrieve data as requested by the application.

You can use this to provide or modify data in your application. It is mostly used to handle contextual data (that is not expected to change often), but can also provide live data in real-time if the database it is connected to is configured to do so.

Most importantly, it should have service broker functionality enabled and should allow the creation of queues and table triggers by the user whose credentials are being used.

Supported data types

The following SQL data types are supported:

SQL Query Guidelines

Semicolon

A semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. However, it is not necessary for a single query.

An incorrect syntax error will appear when a single query with a semicolon is specified on the script box.

Subqueries

SQL Subqueries or query within a query is a tool that allows us to perform tasks more efficiently by having only one query instead of several. However, this feature is not yet supported by this Connector.

Recommendation

Due to the validation of the input query, it is highly recommended that a query is copied and pasted into the script box rather than typing it.

Live Updates

This Connector supports Live Updates, i.e. it can update the entity to display the most up-to-date information. The following is required to activate this capability:

  • The Selected Entity must be a Database Table. Stored Procedures, Views, and Queries are not supported.

  • The SQL Server version must be 2008 or later.

  • The credentials must have the db_owner role, which will allow the Connector to use the SQLTableDependency library.

  • Service Broker must be enabled. You can check by executing the below query:

SELECT is_broker_enabled FROM sys.databases WITH (NOLOCK) WHERE database_id = db_id()

Pre-requisites

SQL Server database credentials are required to use this Connector.

Downloads

Please contact XMPro if you're looking for an older version of this Connector.

Last updated