SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft. Connect to SQL Server to manage data. You can perform various actions such as create, update, get, and delete on rows in a table.

This connector is available in the following products and regions:

Service Class Regions
Logic Apps Standard All Logic Apps regions
Flow Standard All Flow regions
PowerApps Standard All PowerApps regions

To use this connector in PowerApps, learn more here.

Known issues and limitations

The followings are some of the known limitations of using SQL connector

  1. Stored Procedures cannot be invoked from PowerApps.
    • One workaround is to create a Flow and invoke the Flow from PowerApps.
  2. Insert and update to a table does not 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
  3. When invoking a Stored Procedure on an on-premises SQL server, we have the following limitations:
    • Output values for OUTPUT parameters are not returned. You can still specify input values for OUTPUT parameters.
    • Return value is not available.
    • Only the first result set is returned.
    • Dynamics schemas are not supported for result sets.
  4. A Primary Key is required for the following operations:
    • GetItem
    • UpdateItem
    • DeleteItem
  5. When invoking triggers, we have the following limitations:
    • A ROWVERSION column is required for OnUpdatedItems
    • An IDENTITY column is required for OnNewItems
  6. The response size limit is 8MB through on-premises SQL server.
  7. The request size limit is 2MB through on-premises SQL server.
  8. SQL native query is not supported for on-premises SQL server.
  9. The following data types cannot be used as query option predicates:
    • date
    • datetime
    • datetime2
    • smalldatetime
  10. The result set schema after executing SQL queries and stored procedures should contain unique non-empty column names.
  11. The result set schema for any operation inside the stored procedure that returns some set of results (e.g. any inner SELECT statement) should contain unique non-empty column names.

PowerApps data type mappings

SQL Server PowerApps
bigint, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real Number
char, nchar, varchar, nvarchar, text, ntext Text
bit Boolean
date, datetime/datetime2/smalldatetime, datetimeoffset, time DateTime
uniqueidentifier Guid

Note

If a SQL Server data type is not listed in the table above, it is not supported. Examples of unsupported SQL Server data types include: binary(), varbinary(), image, cursor, rowversion, hierarchyid, sql_variant xml, Spatial Geometry Types, Spatial Geography Types, and table.

PowerApps delegable functions and operations for SQL Server

These PowerApps operations, for a given data type, may be delegated to SQL Server for processing (rather than processing locally within PowerApps).

Operation/Function Number Text Boolean DateTime Guid
Filter Yes Yes Yes No [1] Yes
Sort Yes Yes Yes Yes -
SortByColumns Yes Yes Yes Yes -
Search No Yes No No -
Lookup Yes Yes Yes Yes Yes
=, <> Yes Yes Yes No Yes
<, <=, >, >= Yes No No No -
in (substring) - Yes [2] - - -
+ Yes - - No -
- Yes - - No -
* Yes - - No -
/ Yes - - No -
StartsWith - Yes [3] - - -
EndsWith - Yes [4] - - -
IsBlank No [5] No [5] No [5] No [5] No [5]
Len (length) - Yes [6] - - -
Sum Yes - - - -
Average Yes - - - -
Min Yes - - No -
Max Yes - - No -

Note

Expressions joined with And, Or, and Not are delegable to SQL Server.

  1. Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column.
  2. The 'in' (substring) operator. Supported for ("string value" in <column>), but not for (<column> in "string value").
  3. StartsWith. Supported for (StartsWith(<column>, "string value")), but not for (StartsWith("string value", <column>)).
  4. EndsWith. Supported for (EndsWith(<column>, "string value")) but not for (EndsWith("string value", <column>)). If a char(10) column has a value of "hello", EndsWith(<column>, "llo") will return false, and that's by design. A char(10) column has 10 characters.
  5. An expression such as Filter('[dbo].[MyOrders]', !IsBlank(CustomerId)) won't delegate to the server. However, this is semantically close to Filter('[dbo].[MyOrders]', CustomerId <> Blank()), which does delegate to the server. These expressions differ in that the second expression won't treat the empty string ("") as Empty. These expressions aren't equivalent, but the latter might work for your purposes. You can’t use this method for the Guid data type.
  6. Len. PowerApps delegates the Len function, but it might not work as you expect. A char(10) column with value "hello" will always have, in SQL Server, a length of 10. But PowerApps will treat that string as having a length of 5, which could cause discrepancies and confusion. You shouldn't use char/nchar on the server; use varchar/nvarchar instead.

Creating a connection

To connect your account, you will need the following information:

Name Type Description
SQL server name string

SQL server name

SQL database name string

SQL database name

Authentication Type string

Authentication type to connect to your database

Username securestring

Username credential

Password securestring

Password credential

Gateway gatewaySetting

Throttling Limits

Name Calls Renewal Period
API calls per connection10010 seconds

Actions

Delete row

This operation deletes a row from a table.

Delete row (V2)

This operation deletes a row from a table.

Execute a SQL query

Execute a SQL query

Execute a SQL query (V2)

Execute a SQL query (V2)

Execute stored procedure

This operation runs a stored procedure.

Execute stored procedure (V2)

This operation runs a stored procedure.

Get row

This operation gets a row from a table.

Get row (V2)

This operation gets a row from a table.

Get rows

This operation gets rows from a table.

Get rows (V2)

This operation gets rows from a table.

Get tables

This operation gets tables from a database.

Get tables (V2)

This operation gets tables from a database.

Insert row

This operation inserts a new row into a table.

Insert row (V2)

This operation inserts a new row into a table.

Transform data using Power Query

Transform data using Power Query

Update row

This operation updates an existing row in a table.

Update row (V2)

This operation updates an existing row in a table.

Delete row

This operation deletes a row from a table.

Parameters

Name Key Required Type Description
Table name
table True string

Name of table

Row id
id True string

Unique identifier of the row to delete

Delete row (V2)

This operation deletes a row from a table.

Parameters

Name Key Required Type Description
Server name
server True string

Name of SQL server

Database name
database True string

Database name

Table name
table True string

Name of table

Row id
id True string

Unique identifier of the row to delete

Execute a SQL query

Execute a SQL query

Parameters

Name Key Required Type Description
Query
query True dynamic

query body

Returns

The outputs of this operation are dynamic.

Execute a SQL query (V2)

Execute a SQL query (V2)

Parameters

Name Key Required Type Description
Server name
server True string

Name of SQL server

Database name
database True string

Database name

Query
query True dynamic

query body

Returns

The outputs of this operation are dynamic.

Execute stored procedure

This operation runs a stored procedure.

Parameters

Name Key Required Type Description
Procedure name
procedure True string

Name of stored procedure

Parameters list
parameters True dynamic

Input parameters to the stored procedure

Returns

The outputs of this operation are dynamic.

Execute stored procedure (V2)

This operation runs a stored procedure.

Parameters

Name Key Required Type Description
Server name
server True string

Name of SQL server

Database name
database True string

Database name

Procedure name
procedure True string

Name of stored procedure

Parameters list
parameters True dynamic

Input parameters to the stored procedure

Returns

The outputs of this operation are dynamic.

Get row

This operation gets a row from a table.

Parameters

Name Key Required Type Description
Table name
table True string

Name of SQL table

Row id
id True string

Unique identifier of the row to retrieve

Returns

The outputs of this operation are dynamic.

Get row (V2)

This operation gets a row from a table.

Parameters

Name Key Required Type Description
Server name
server True string

Name of SQL server

Database name
database True string

Database name

Table name
table True string

Name of SQL table

Row id
id True string

Unique identifier of the row to retrieve

Returns

The outputs of this operation are dynamic.

Get rows

This operation gets rows from a table.

Parameters

Name Key Required Type Description
Table name
table True string

Name of SQL table

Aggregation transformation
$apply string

A sequence of OData aggregation transformations

Filter Query
$filter string

An ODATA filter query to restrict the entries returned (e.g. stringColumn eq 'string' OR numberColumn lt 123).

Order By
$orderby string

An ODATA orderBy query for specifying the order of entries.

Skip Count
$skip integer

The number of entries to skip (default = 0).

Top Count
$top integer

Total number of entries to retrieve (default = all).

Select Query
$select string

Specific fields to retrieve from entries (default = all).

Returns

The outputs of this operation are dynamic.

Get rows (V2)

This operation gets rows from a table.

Parameters

Name Key Required Type Description
Server name
server True string

Name of SQL server

Database name
database True string

Database name

Table name
table True string

Name of SQL table

Aggregation transformation
$apply string

A sequence of OData aggregation transformations

Filter Query
$filter string

An ODATA filter query to restrict the entries returned (e.g. stringColumn eq 'string' OR numberColumn lt 123).

Order By
$orderby string

An ODATA orderBy query for specifying the order of entries.

Skip Count
$skip integer

The number of entries to skip (default = 0).

Top Count
$top integer

Total number of entries to retrieve (default = all).

Select Query
$select string

Specific fields to retrieve from entries (default = all).

Returns

The outputs of this operation are dynamic.

Get tables

This operation gets tables from a database.

Returns

Represents a list of tables.

Body
TablesList

Get tables (V2)

This operation gets tables from a database.

Parameters

Name Key Required Type Description
Server name
server True string

Name of SQL server

Database name
database True string

Database name

Returns

Represents a list of tables.

Body
TablesList

Insert row

This operation inserts a new row into a table.

Parameters

Name Key Required Type Description
Table name
table True string

Name of table

Row
item True dynamic

Row to insert into the specified table

Returns

The outputs of this operation are dynamic.

Insert row (V2)

This operation inserts a new row into a table.

Parameters

Name Key Required Type Description
Server name
server True string

Name of SQL server

Database name
database True string

Database name

Table name
table True string

Name of table

Row
item True dynamic

Row to insert into the specified table

Returns

The outputs of this operation are dynamic.

Transform data using Power Query

Transform data using Power Query

Parameters

Name Key Required Type Description
query
query mquery

Query Text

Returns

The outputs of this operation are dynamic.

Update row

This operation updates an existing row in a table.

Parameters

Name Key Required Type Description
Table name
table True string

Name of table

Row id
id True string

Unique identifier of the row to update

Row
item True dynamic

Row with updated values

Returns

The outputs of this operation are dynamic.

Update row (V2)

This operation updates an existing row in a table.

Parameters

Name Key Required Type Description
Server name
server True string

Name of SQL server

Database name
database True string

Database name

Table name
table True string

Name of table

Row id
id True string

Unique identifier of the row to update

Row
item True dynamic

Row with updated values

Returns

The outputs of this operation are dynamic.

Triggers

When an item is created

Triggers a flow when an item is created in Sql

When an item is created (V2)

Triggers a flow when an item is created in Sql

When an item is modified

Triggers a flow when an item is modified in Sql

When an item is modified (V2)

Triggers a flow when an item is modified in Sql

When an item is created

Triggers a flow when an item is created in Sql

Parameters

Name Key Required Type Description
Table name
table True string

Name of Sql table

Filter Query
$filter string

An ODATA filter query to restrict the entries returned (e.g. stringColumn eq 'string' OR numberColumn lt 123).

Top Count
$top integer

Total number of entries to retrieve (default = all).

Order By
$orderby string

An ODATA orderBy query for specifying the order of entries.

Select Query
$select string

Specific fields to retrieve from entries (default = all).

Returns

The outputs of this operation are dynamic.

When an item is created (V2)

Triggers a flow when an item is created in Sql

Parameters

Name Key Required Type Description
Server name
server True string

Name of SQL server

Database name
database True string

Database name

Table name
table True string

Name of Sql table

Filter Query
$filter string

An ODATA filter query to restrict the entries returned (e.g. stringColumn eq 'string' OR numberColumn lt 123).

Top Count
$top integer

Total number of entries to retrieve (default = all).

Order By
$orderby string

An ODATA orderBy query for specifying the order of entries.

Select Query
$select string

Specific fields to retrieve from entries (default = all).

Returns

The outputs of this operation are dynamic.

When an item is modified

Triggers a flow when an item is modified in Sql

Parameters

Name Key Required Type Description
Table name
table True string

Name of Sql table

Filter Query
$filter string

An ODATA filter query to restrict the entries returned (e.g. stringColumn eq 'string' OR numberColumn lt 123).

Top Count
$top integer

Total number of entries to retrieve (default = all).

Order By
$orderby string

An ODATA orderBy query for specifying the order of entries.

Select Query
$select string

Specific fields to retrieve from entries (default = all).

Returns

The outputs of this operation are dynamic.

When an item is modified (V2)

Triggers a flow when an item is modified in Sql

Parameters

Name Key Required Type Description
Server name
server True string

Name of SQL server

Database name
database True string

Database name

Table name
table True string

Name of Sql table

Filter Query
$filter string

An ODATA filter query to restrict the entries returned (e.g. stringColumn eq 'string' OR numberColumn lt 123).

Top Count
$top integer

Total number of entries to retrieve (default = all).

Order By
$orderby string

An ODATA orderBy query for specifying the order of entries.

Select Query
$select string

Specific fields to retrieve from entries (default = all).

Returns

The outputs of this operation are dynamic.

Definitions

TablesList

Represents a list of tables.

Name Path Type Description
value
value array of Table

List of Tables

Table

Represents a table.

Name Path Type Description
Name
Name string

The name of the table. The name is used at runtime.

DisplayName
DisplayName string

The display name of the table.

DynamicProperties
DynamicProperties object

Additional table properties provided by the connector to the clients.