All About SQL Connector Limitations

Power Platform talks to various data sources by using the connectors. Some connectors are standard and some are premium.

Before using any connector, I highly recommend to review Microsoft’s Connector documentation to understand the latest updates with respect to the actions and triggers provided with the connections.

Also, it is very important to understand the limitations with various actions and triggers.

If we don’t review this information, we will hit roadblocks when the data size is huge or if your application design is not aligned with the functionality of the connector.

Let us review the known issues and limitations with SQL Connector’s actions and triggers.

Known Issues and limitations with actions

Short Description	Operation(s)	Long Description
Get deterministic results in an action output	Get rows (V2)	Usage of the Order By parameter is recommended in order to get deterministic results in action output. If Order By isn’t specified, primary keys or unique keys are used by SQL Server by default. Non-deterministic results might cause issues, such as duplicating records in the action output when pagination is enabled. SQL views don’t support primary key, which is the limitation from SQL Server itself.
Execute a SQL query limited support	Execute a SQL query (V2)	Not supported for on-premises SQL Server.
General CRUD requirements	Get row (V2) Get rows (V2) Update row (V2) Delete row (V2)	String values will be trimmed in the case of Azure SQL instance usage. Therefore, blank strings will be converted into empty strings. For INSERT and UPDATE operations, the connector will return the updated rows. This requires that the connection will need to have permission to read data from the SQL Server as well. A Primary Key is required for the following operations: – GetItem – PatchItem – DeleteItem
Aggregation transformation parameter support	Get rows (V2)	Only the following Aggregation transformations are supported: – filter: Allows filtering input set. Supports the same expressions as $filter. Example: filter(ColName1 eq 1). – aggregate: Allows aggregation using one of following methods: average, max, min, sum, countdistinct. Example: aggregate(ColName2 with max as MaxValue). You can combine several operators separated by forward slashes to express that they are consecutively applied. This means the result of each transformation is the input to the next transformation. Example: filter(ColName1 eq 4)/aggregate(ColName2 with sum as MaxWorkItemId).

Known issues and limitations with triggers

Short description	Operation(s)	Long description
Modify an item: row operations	When an item is modified (V2)	Fires on both INSERT and UPDATE row operations.
Modify an item: column requirements	When an item is modified (V2)	A ROWVERSION or an IDENTITY column is required.
Create an item: column requirement	When an item is created (V2)	An IDENTITY column is required.
Create an item	When an item is created (V2)	Use the latest inserted row’s identity column value as a threshold (trigger state) in order to identify newly inserted rows later. The logic assumes every new row’s identity has to be bigger than the previous one. Therefore, in case identity sequence is reset for a table due to explicit t-sql command or table truncation, trigger logic will skip new rows for the existing flow. There are two different workarounds possible: First, you can reset the flow trigger state by updating the trigger action card (for example, rename it, update parameters, or add a new connection), and save the changes. Or, you can restore the target table identity sequence using the following approach.

General known issues and limitations

Following are some of the general known issues and limitations of using SQL connector.

• Stored Procedures can’t be invoked from Power Apps.
  • One workaround is to create a flow and invoke the flow from Power Apps.
• Insert and update to a table doesn’t work if you have a SQL Server side Trigger defined on the table. To workaround this issue, you can do either of the following:
  • Use a Stored Procedure or Native Query.
  • Remove the Trigger from your SQL table.
• When invoking a stored procedure on an on-premises SQL Server, we have the following limitations:
  • Output values for OUTPUT parameters aren’t returned. You can still specify input values for OUTPUT parameters.
  • Return value isn’t available.
  • Only the first result set is returned.
  • Dynamics schemas aren’t supported for result sets.
• The response size limit is 8 MB through on-premises SQL Server.
• The request size limit is 2 MB through on-premises SQL Server.
• The minimum supported version for on-premises SQL Server is SQL Server 2005.
• The following data types can’t be used as query option predicates:
  • date
  • datetime
  • datetime2
  • smalldatetime
• Currently multiple result sets support is limited for SQL native query execution: only result sets, up to the first empty one result set, will be returned. As a workaround, Stored Procedure usage is proposed.
• The result set schema after executing SQL queries and stored procedures should contain unique non-empty column names.
• The result set schema for any operation inside the stored procedure that returns some set of results (for example, any inner SELECT statement) should contain unique non-empty column names.
• If the SQL query/stored procedure execution time exceeds 110 seconds, actions will timeout. To learn how to execute long-running stored procedures in Logic Apps, and to workaround the execution time limitation, go to Long-Running SQL Stored Procedures in LogicApps.
• Private endpoint of SQL Server can only be accessed within ISE. To learn more, go to Access to Azure virtual networks.
• Setting Force Encryption isn’t supported. By default, SQL Database and SQL Managed Instance secure customer data by encrypting data in motion with Transport Layer Security (TLS). To learn more information, go to Azure SQL security overview.
• Using Service Principal to connect to SQL Server is only supported in Logic Apps.
• Due to current authentication pipeline limitations, AAD guest users aren’t supported when using AAD type connections to SQL Server. As a workaround, use SQL/Windows auth type connections.