Azure SQL Connector

The Azure SQL Connector allows you to connect to a remote Azure SQL 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.

To enable live Updates, the database should have the service broker functionality enabled and allow the creation of queues and table triggers by the user whose credentials are being used.

NOTE: A bug introduced in XMPro App Designer v4.4.7 requires a data source to have a primary key. As a workaround, all stored procedures should have a select statement e.g. SELECT 'PK' as PK

Supported data types

The following SQL data types are supported:

String

Numeric

Date and Time

Other

char
varchar
text
nchar
nvarchar
ntext

bit
smallint
int
bigint
decimal
numeric
smallmoney
money
float
real

datetime
datetime2
smalldatetime
date

uniqueidentifier
xml

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 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_i

Guidelines

Query Guidelines

Semicolon

A semicolon is the standard way to separate each SQL statement in database systems, allowing 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.

Stored Procedure Guidelines

Stored Procedures provide advantages to the database administrator when conducting CRUD (Create, Read, Update, and Delete) operations across multiple tables within a single transaction, or when data validation before CRUD operations is necessary - a functionality not attainable by directly accessing tables or table views as data sources.

This connector supports Read, Update, and Insert actions in Stored Procedures. The Stored Procedure requires specific input parameters for Update or Insert actions.

Update

UpdateKey - contains the primary key value of the updated row
- The expected value from the connector would be a JSON string as per the below example
  - {"PrimaryKey":PrimaryKeyValue} e.g. {"Id": 1}
    The Primary Key is set when configuring the page data source
UpdateValues - contains the updated columns and values
- The expected value from the connector would be a JSON string as per the below example
  - {"ColumnName": Values, "ColumnName2":Values, ....} e.g. {"FirstName" : "John", "LastName" : "Doe"}

Insert

InsertValues - contains the new values to be inserted
- The expected value from the connector would be a JSON string as per the below example
  - {"ColumnName": Values, "ColumnName2":Values, ....} e.g. {"FirstName" : "John", "LastName" : "Doe"}

The following is a simple example of a Stored Procedure designed to perform multi-table CRUD operations. This Stored Procedure lists users from the UserTable (Read/Refresh), updates (Update), and inserts records into UserTable (Insert), and logs each Update/Insert operation in the TransactionLog table.

CREATE PROCEDURE [dbo].[sp_StoredProcExample]
(
   @UpdateKey NVARCHAR(MAX) = NULL,
   @UpdateValues NVARCHAR(MAX) = NULL,
   @InsertValues NVARCHAR(MAX) = NULL
)
AS
BEGIN
    SET NOCOUNT ON

    IF( @UpdateKey IS NULL AND @UpdateValues IS NULL AND @InsertValues IS NULL)
        SELECT * FROM UserTable
    ELSE IF (@UpdateKey IS NOT NULL AND @UpdateValues IS NOT NULL AND @InsertValues IS NULL)
    BEGIN
    -- Update
    BEGIN TRANSACTION
        BEGIN TRY
	    DECLARE @sql NVARCHAR(MAX)

            SET @sql = 'UPDATE UserTable SET ';

            SELECT @sql = @sql + [key] + ' = ''' + [value] + ''', '
            FROM OPENJSON(@UpdateValues)
		
            SET @sql = LEFT(@sql, LEN(@sql) - 1);

            SELECT @sql = @sql + ' WHERE ' + [key] + ' = ' + [value]
            FROM OPENJSON(@UpdateKey)

            EXEC sp_executesql @sql;
		
            INSERT INTO TransactionLogs ([Action], [Key],[Value]) VALUES ('Update', @UpdateKey, @UpdateValues)
            COMMIT TRANSACTION
       END TRY
       BEGIN CATCH
            ROLLBACK TRANSACTION
       END CATCH
    END
    ELSE
    BEGIN
    --Insert
    BEGIN TRANSACTION
        BEGIN TRY
            INSERT INTO UserTable
            SELECT * FROM OPENJSON(@InsertValues)
            WITH
            (FirstName NVARCHAR(255) '$.FirstName', LastName NVARCHAR(255) '$.LastName ', IsAdmin BIT '$.IsAdmin', Email NVARCHAR(255) '$.Email')
		
            INSERT INTO TransactionLogs ([Action], [Key], [Value]) VALUES ('Insert', @@IDENTITY, @InsertValues)
            COMMIT TRANSACTION
       END TRY
       BEGIN CATCH
            ROLLBACK TRANSACTION
       END CATCH
    END
END

Pre-requisites

Azure SQL database credentials are required to use this Connector.
For Stored Procedures, only one result set is supported. We recommend using SET NOCOUNT ON at the start - especially if using nested Stored Procedures or temporary tables.

Current Version

Download the Azure SQL Connector v2.01

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

Release Notes

Version

Date

Description

N/A

02 Jun 2025

Noted Stored Procedure primary key workaround and to set row count to zero especially when using temporary tables.

2.01

07 Jan 2025

Repackaged to translate the Connector's properties.

2.00

16 Aug 2024

Updated System.Data.SqlClient from v4.7.0 to v4.8.6 for the CVE-2024-0056 security update.

1.70

26 Apr 2024

Support Update and Insert action in Stored Procedures.

1.65

08 Dec 2023

Fixed connector logging error - entries were not consistently logged to App Designer.

1.64

24 Oct 2023

Added connector logging support.

Moved accessibility and username properties from the Access group to a new group, Usage.

1.63

18 Oct 2023

Support inserts to a table with a unique identifier column.

Added Show Distinct Values Without Access Control Column property, which allows the output payload to exclude the column used for access control.

Added the Data Access Control section.

Fixed insert failing when an int or bigint auto-increment column existed and an "instead of" insert trigger was used.

Fixed updating a row(s) with a composite key.

Fixed issue with Primary Key selection in config.

Fixed filter not being used when fetching record count.

Fixed queries with a Take parameter failing.

Fixed queries with a Skip parameter but without a Take parameter not skipping any records.

Support Insert and Update Stored Proc operations, though the implementation is the same as Read, to invoke Stored Proc under Update Datasources.

1.262

17 Aug 2021

Initial Release.

Troubleshooting

When using a stored procedure, confirm there is one and only one result set.
1. Begin your stored procedure with SET NOCOUNT ON
2. If needed, create a dummy result e.g. SELECT 'PK' as PK
If the execution exceeds the 30 second timeout in App Designer, consider switching to an async pattern where you can configure the timeout and your user is not kept waiting. See What should I do when my SQL stored procedure exceeds the 30-second timeout?

NextExample

Last updated 18 days ago