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 and Integration Service Environments (ISEs) |
| Power Automate | Premium | All Power Automate regions |
| Power Apps | Premium | All Power Apps regions |
| Connector Metadata | |
|---|---|
| Publisher | Microsoft |
Connector in-depth
This article describes the operations for the SQL Server managed connector, which is available for Azure Logic Apps, Power Automate, and Power Apps. The SQL Server built-in connector is available only for Standard logic app workflows in Azure Logic Apps.
- For more information about this connector in Power Apps, see Connect to SQL Server from Power Apps.
- For more information about the SQL Server built-in connector and managed connector in Azure Logic Apps, see Connect to SQL databases from workflows in Azure Logic Apps.
Authentication
If you want to create non-Azure Active Directory (Azure AD) connections, use the connection string authentication option. Make sure that you provide the same Server name and Database name values as defined in your connection.
Azure AD authentication
Known issues and limitations with Azure AD authentication
Due to current authentication pipeline limitations, Azure AD guest users aren't supported for Azure AD connections to SQL Server. To resolve this problem, use SQL Server authentication or Windows authentication instead.
Enable Azure AD authentication for SQL Server
Set up and provision your account as a SQL Azure Active Directory (Azure AD) member. For more information, see Configure and manage Azure AD authentication with Azure SQL.
On your SQL server, set up your Azure Active Directory (Azure AD) administrator.
- In the Azure portal, go to the SQL server where you want to enable Azure AD authentication.
- On your SQL server resource menu, under Settings, select Azure Active Directory.
- On the Azure Active Directory pane toolbar, select Set admin.
- Find and select an Azure AD user account in the current tenant to make them a server administrator. Save your changes.
In your Azure AD tenant, create and register an Azure AD application for accessing your SQL database. For more information, see SQL Server - Set up Azure AD authentication.
In the Azure portal, go to your Azure AD tenant where you want to create the Azure AD application.
On the tenant menu, under Manage, select App registrations > New registration.
On the Register an application pane, provide a name for the application. Remember this name so you can later create a user and grant role access to your SQL database.
When you're done, select Register, which creates the Azure AD application.
On the App registrations pane, select your new Azure AD application. On the application menu, select Certificates & secrets > New client secret.
Provide a description for the secret, and select an expiration.
Sign in to your SQL server and database with the credentials for the Azure AD user account that you set up as a server administrator.
In your SQL database, create a contained user to represent the Azure AD application. This step requires Microsoft SQL Server Management Studio (SSMS).
The following steps assign the roles of
db_datareaderanddb_datawriterto the Azure AD application with the nameexample-Azure-AD-application-name. When you run the specified commands, remember to replaceexample-Azure-AD-application-namewith the name for your Azure AD application, and assign the required roles for your needs.Start SSMS and connect to the SQL server where the Azure AD application will access.
In Authentication, use Active Directory - Universal with MFA support and the Azure AD account that you set up as the server administrator.
In the Object Explorer, expand the Databases folder.
Find your database, open the database shortcut menu, and select New query.
In the query window, execute the following command to link the Azure AD application to a new user in the database:
CREATE USER [example-Azure-AD-app-name] FROM EXTERNAL PROVIDERClear the query window. Then, run the following command to assign the
db_datareaderrole to the Azure AD application:ALTER ROLE db_datareader ADD MEMBER [example-Azure-AD-application-name]Repeat the previous step to assign the
db_datawriterrole to the Azure AD application:ALTER ROLE db_datawriter ADD MEMBER [example-Azure-AD-application-name]
Managed identity authentication
Currently, only Azure Logic Apps supports managed identity authentication for the SQL Server connector.
Set up managed identity on your logic app
In the Azure portal, go to your logic app resource.
Enable either the system-assigned identity or user-assigned identity. If you don't have a user-assigned identity, you have to first create that identity. For more information, see Authenticate access to Azure resources with managed identities in Azure Logic Apps.
If you selected user-assigned identity, make sure that you added that identity to your logic app resource.
- In the Azure portal, browse to the logic app resource where you want to use the managed identity.
- On the logic app navigation menu, under the Settings, select Identity.
- On the User assigned tab, select Add.
- Select the managed identity to use, and select Add.
Set up SQL server for managed identity authentication
On your SQL server, set up your Azure AD administrator.
- In the Azure portal, browse to the SQL server where you want to enable Azure AD authentication.
- On your SQL server resource menu, under Settings, select Azure Active Directory.
- On the Azure Active Directory pane toolbar, select Set admin.
- Find and select an Azure AD user account in the current tenant to make them a server administrator. Save your changes.
In your SQL database, create a contained user to represent the managed identity. This step requires Microsoft SQL Server Management Studio (SSMS).
The following steps assign the role of
db_datareaderto a managed identity with the nameexample-managed-identity-name. When you run the specified commands, remember to replaceexample-managed-identity-namewith the name for your managed identity, and assign the correct role for your needs. For example,db_datareadercan't execute write operations.Start SSMS and connect to the SQL server where the managed identity will access.
In Authentication, use Active Directory - Universal with MFA support and the Azure AD account that you set up as the server administrator.
In the Object Explorer, expand the Databases folder.
Find your database, open the database shortcut menu, and select New query.
In the query window, execute the following command to link the managed identity to a new user in the database:
CREATE USER [example-managed-identity-name] FROM EXTERNAL PROVIDERClear the query window. Then, run the following command to assign a role to the manage identity:
ALTER ROLE db_datareader ADD MEMBER [example-managed-identity-name]Note
If you assign roles to the managed identity at the database level, the identity won't have the required scopes to list all databases. When you select from the Database name list in a SQL trigger or action, you'll get an error like the following example:
Couldn't retrieve values. Error code: 'Unauthorized', Message: 'Credentials are missing or not valid'. Inner exception: The credentials provided for the SQL source are invalidTo resolve this problem, you have the following options:
- Assign a server-level role that can list all databases to the managed identity.
- In the SQL trigger or action, enter the database name using the Enter custom value option.
For more information, review the following documentation:
- Authenticate access to Azure resources using managed identities in Azure Logic Apps
- Tutorial: Use a Windows VM system-assigned managed identity to access Azure SQL
- Configure and manage Azure AD authentication with Azure SQL
- Database-level roles
- Server-level roles
Known issues and limitations with actions
| Short description | Operations | 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
Note
Turning off and turning on a flow won't reset the trigger state.
| Short description | Operations | 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) 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
Insert and update to a table won't work if you defined a SQL server-side trigger on the table.
To resolve this problem, you have the following options:
- Use a stored procedure or native query.
- Remove the trigger from your SQL table.
The following limitations apply to using the SQL connector with an on-premises SQL server:
- The minimum supported version for on-premises SQL Server is SQL Server 2005.
- The request size limit is 2 MB through on-premises SQL Server.
- The response size limit is 8 MB through on-premises SQL Server.
The following limitations apply to invoking a stored procedure on an on-premises SQL server:
- Output values for
OUTPUTparameters aren't returned. You can still specify input values forOUTPUTparameters. - Return value isn't available.
- Only the first result set is returned.
- Dynamics schemas aren't supported for result sets.
- Output values for
The following data types can't be used as query option predicates:
datedatetimedatetime2smalldatetime
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). For more information, see Azure SQL security overview.
Multiple result sets support is currently limited for SQL native query execution. Only result sets, up to the first empty one result set, are returned.
To resolve this problem, use stored procedures.
After SQL queries and stored procedures execute, the result set schema should contain unique non-empty column names.
For any operation inside the stored procedure that returns some set of results, such as any inner
SELECTstatement, the result set schema should contain unique non-empty column names.You can't invoke stored procedures from Power Apps.
To resolve this problem, you can create a flow in Power Automate and invoke the flow from Power Apps.
If the execution time exceeds 110 seconds for a SQL query or stored procedure, actions will time out.
In Azure Logic Apps, to work around this limitation and learn how to execute long-running stored procedures, see Long-running SQL stored procedures in Logic Apps.
Only Azure Logic Apps supports using a service principal to connect to SQL Server.
In Azure Logic Apps, you can access the private endpoint on SQL Server only from within an integration service environment (ISE). For more information, see Access to Azure virtual networks.
Special throttling limits
In different cases, the SQL connector has different throttling limits. In the following table, Native operations include SQL stored procedure and SQL Query. CRUD stands for create, read, update, and delete operations.
| Service | Environment | Operations | Name | Calls | Renewal period (seconds) |
|---|---|---|---|---|---|
| Logic Apps & Power Automate | Shared Environment | Native | API calls per connection | 500 | 10 |
| Logic Apps & Power Automate | Shared Environment | CRUD | API calls per connection | 100 | 10 |
| Logic Apps & Power Automate | Shared Environment | Native | Concurrent calls per connection | 200 | - |
| Logic Apps & Power Automate | Shared Environment | CRUD | Concurrent calls per connection | 125 | - |
| Power Apps | Shared Environment | All | API calls per user | 300 | 30 |
| Power Apps | Shared Environment | All | Concurrent calls per connection | 125 | - |
Note
Concurrent calls are the number of in-progress requests that a particular connection can make. For example, with Azure Logic Apps native calls, a single connection can make 200 concurrent calls. Any subsequent calls return a 429 error if all 200 calls are still executing. No time limit exists because the calls can take an arbitrary amount of time. However, the point of the calls limit is to prevent too many long-running queries from a single connection and negatively affecting other connections.
For example, if a connection makes 200 calls at the same time, and each call takes 50 seconds to execute, all other calls will fail with a 429 error for the next 50 seconds. After one or more queries finish execution, and results are returned, any subsequent calls are processed.
Power Apps data type mappings
| SQL Server | Power Apps |
|---|---|
bigint, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real |
Number |
char, nchar, varchar, nvarchar, text, ntext |
Text |
bit |
Boolean |
date, datetime or datetime2 or smalldatetime, datetimeoffset, time |
DateTime |
uniqueidentifier |
Guid |
Note
If a SQL Server data type doesn't appear in the previous table, that data type isn't supported.
Unsupported data types include the following examples: binary(), varbinary(), image, cursor,
rowversion, hierarchyid, sql_variant xml, Spatial Geometry Types, Spatial Geography Types, and table.
Power Apps functions and operations delegable to SQL Server
You can delegate the following Power Apps operations, for a given data type, to SQL Server for processing, rather than processing locally within Power Apps. You can also delegate expressions joined with And, Or, and Not to SQL Server.
| Operation or function | Number | Text | Boolean | DateTime | Guid | Notes |
|---|---|---|---|---|---|---|
Filter |
Yes | Yes | Yes | No, see notes. | Yes | Direct date filters don't work for SQL Server. However, you can create a calculated column that will work. For example, you can create ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])), and then filter on the calculated number column. |
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, see notes. | - | - | - | Supported for ("string value" in \<column>), but not for (\<column> in "string value"). |
+ |
Yes | - | - | No | - | |
- |
Yes | - | - | No | - | |
* |
Yes | - | - | No | - | |
/ |
Yes | - | - | No | - | |
StartsWith |
- | Yes, see notes. | - | - | - | Supported for (StartsWith(\<column>, "string value")), but not for (StartsWith("string value", \<column>)). |
EndsWith |
- | Yes, see notes. | - | - | - | 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") returns false, by design. A char(10) column has 10 characters. |
IsBlank |
No | No | No | No | No | An expression such as Filter('[dbo].[MyOrders]', !IsBlank(CustomerId)) won't delegate to the server. However, you can use an expression such as Filter('[dbo].[MyOrders]', CustomerId <> Blank()), which does delegate to the server and is sementically close. The difference is that the second expression won't treat the empty string ("") as empty. Although the expressions aren't equivalent, the latter might work for your purposes. You can't use this method for the Guid data type. |
Len (length) |
- | Yes, see notes. | - | - | - | Power Apps delegates the Len function, but the behavior might not work as you expect. In SQL Server, a char(10) column with value "hello" always has a length of 10. However, Power Apps treats that string as having a length of 5, which could might discrepancies and confusion. Don't use char/nchar on SQL server, but use varchar/nvarchar instead. |
Sum |
Yes | - | - | - | - | |
Average |
Yes | - | - | - | - | |
Min |
Yes | - | - | No | - | |
Max |
Yes | - | - | No | - |
Formal parameters in operations
Execute a SQL query [DEPRECATED] and Execute a SQL query (V2) actions allow usage of formal parameters. They're defined as key-value pairs, where key is the parameter name and value is the SQL data type, such as INT or NVARCHAR(255). After you define the name and data type, a new input field for the value is shown.
Creating a connection
The connector supports the following authentication types:
| Azure AD Integrated | Use Azure Active Directory to access your SQL database. | All regions except US Government (GCC) | Not shareable |
| Azure AD Integrated (Azure Commercial) | Use Azure Active Directory to access your SQL database in Azure Commercial. | US Government (GCC) only | Not shareable |
| Azure AD Integrated (Azure Government) | Use Azure Active Directory to access your SQL database in Azure Government. | US Government (GCC) only | Not shareable |
| SQL Server Authentication | Use an account name and password to access your SQL database. | All regions | Shareable |
| Windows Authentication | Use an on-premise Active Directory account name and password to access your SQL database. | All regions | Shareable |
| Windows Authentication (Non Shared) | Use an on-premise Active Directory account name and password to access your SQL database. | All regions | Not shareable |
| Default [DEPRECATED] | This option is only for older connections without an explicit authentication type, and is only provided for backward compatibility. | All regions | Not shareable |
Azure AD Integrated
Auth ID: oauth
Applicable: All regions except US Government (GCC)
Use Azure Active Directory to access your SQL database.
This is not shareable connection. If the power app is shared with another user, another user will be prompted to create new connection explicitly.
Azure AD Integrated (Azure Commercial)
Auth ID: oauthCom
Applicable: US Government (GCC) only
Use Azure Active Directory to access your SQL database in Azure Commercial.
This is not shareable connection. If the power app is shared with another user, another user will be prompted to create new connection explicitly.
Azure AD Integrated (Azure Government)
Auth ID: oauth
Applicable: US Government (GCC) only
Use Azure Active Directory to access your SQL database in Azure Government.
This is not shareable connection. If the power app is shared with another user, another user will be prompted to create new connection explicitly.
SQL Server Authentication
Auth ID: sqlAuthentication
Applicable: All regions
Use an account name and password to access your SQL database.
This is shareable connection. If the power app is shared with another user, connection is shared as well. For more information, please see the Connectors overview for canvas apps - Power Apps | Microsoft Docs
| Name | Type | Description | Required |
|---|---|---|---|
| SQL server name | string | Specify the name of the SQL server (server[:port]) | True |
| SQL database name | string | SQL database name | True |
| Username | securestring | Username credential | True |
| Password | securestring | Password credential | True |
| Gateway | gatewaySetting | On-prem gateway (see https://docs.microsoft.com/data-integration/gateway for more details |
Windows Authentication
Auth ID: windowsAuthentication
Applicable: All regions
Use an on-premise Active Directory account name and password to access your SQL database.
This is shareable connection. If the power app is shared with another user, connection is shared as well. For more information, please see the Connectors overview for canvas apps - Power Apps | Microsoft Docs
| Name | Type | Description | Required |
|---|---|---|---|
| SQL server name | string | Specify the name of the SQL server (server[:port]) | True |
| SQL database name | string | SQL database name | True |
| Username | securestring | Username credential | True |
| Password | securestring | Password credential | True |
| Gateway | gatewaySetting | On-prem gateway (see https://docs.microsoft.com/data-integration/gateway for more details | True |
Windows Authentication (Non Shared)
Auth ID: windowsAuthenticationNonShared
Applicable: All regions
Use an on-premise Active Directory account name and password to access your SQL database.
This is not shareable connection. If the power app is shared with another user, another user will be prompted to create new connection explicitly.
| Name | Type | Description | Required |
|---|---|---|---|
| Username | securestring | Username credential | True |
| Password | securestring | Password credential | True |
| Gateway | gatewaySetting | On-prem gateway (see https://docs.microsoft.com/data-integration/gateway for more details | True |
Default [DEPRECATED]
Applicable: All regions
This option is only for older connections without an explicit authentication type, and is only provided for backward compatibility.
This is not shareable connection. If the power app is shared with another user, another user will be prompted to create new connection explicitly.
| Name | Type | Description | Required |
|---|---|---|---|
| SQL server name | string | SQL server name | True |
| SQL database name | string | SQL database name | True |
| Authentication Type | string | Authentication type to connect to your database | |
| Username | securestring | Username credential | True |
| Password | securestring | Password credential | True |
| Gateway | gatewaySetting | On-prem gateway (see https://docs.microsoft.com/data-integration/gateway for more details |
Throttling Limits
| Name | Calls | Renewal Period |
|---|---|---|
| API calls per connection | 100 | 10 seconds |
Actions
| Delete row (V2) |
This operation deletes a row from a table. |
| Delete row [DEPRECATED] |
This action has been deprecated. Please use Delete row (V2) instead.
|
| Execute a SQL query (V2) |
Execute a SQL query (V2) |
| Execute a SQL query [DEPRECATED] |
This action has been deprecated. Please use Execute a SQL query (V2) instead.
|
| Execute stored procedure |
This operation runs a stored procedure. |
| Execute stored procedure (V2) |
This operation runs a stored procedure. |
| Get row (V2) |
This operation gets a row from a table. |
| Get row [DEPRECATED] |
This action has been deprecated. Please use Get row (V2) instead.
|
| Get rows (V2) |
This operation gets rows from a table. |
| Get rows [DEPRECATED] |
This action has been deprecated. Please use Get rows (V2) instead.
|
| Get tables (V2) |
This operation gets tables from a database. |
| Insert row (V2) |
This operation inserts a new row into a table. |
| Insert row [DEPRECATED] |
This action has been deprecated. Please use Insert row (V2) instead.
|
| Lists tables exposed by the connection [DEPRECATED] |
This action has been deprecated. Please use Get tables (V2) instead.
|
| Transform data using Power Query |
Transform data using Power Query. Note: This operation does not work when connecting using SQL Azure AD. |
| Update row (V2) |
This operation updates an existing row in a table. |
| Update row [DEPRECATED] |
This action has been deprecated. Please use Update row (V2) instead.
|
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 |
Delete row [DEPRECATED]
This action has been deprecated. Please use Delete row (V2) instead.
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 |
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
Execute a SQL query [DEPRECATED]
This action has been deprecated. Please use Execute a SQL query (V2) instead.
Execute a SQL query
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Query
|
query | True | dynamic |
query body |
Returns
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
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
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
Get row [DEPRECATED]
This action has been deprecated. Please use Get row (V2) instead.
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
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
Get rows [DEPRECATED]
This action has been deprecated. Please use Get rows (V2) instead.
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
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 (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
Insert row [DEPRECATED]
This action has been deprecated. Please use Insert row (V2) instead.
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
Lists tables exposed by the connection [DEPRECATED]
This action has been deprecated. Please use Get tables (V2) instead.
Lists tables exposed by the connection
Returns
Represents a list of tables.
- Body
- TablesList
Transform data using Power Query
Transform data using Power Query. Note: This operation does not work when connecting using SQL Azure AD.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
query
|
query | mquery |
Query Text |
Returns
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
Update row [DEPRECATED]
This action has been deprecated. Please use Update row (V2) instead.
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
Triggers
| When an item is created (V2) |
Triggers a flow when an item is created in SQL |
| When an item is created [DEPRECATED] |
This action has been deprecated. Please use When an item is created (V2) instead.
|
| When an item is modified (V2) |
Triggers a flow when an item is modified/inserted in SQL |
| When an item is modified [DEPRECATED] |
This action has been deprecated. Please use When an item is modified (V2) instead.
|
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
When an item is created [DEPRECATED]
This action has been deprecated. Please use When an item is created (V2) instead.
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
When an item is modified (V2)
Triggers a flow when an item is modified/inserted 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
When an item is modified [DEPRECATED]
This action has been deprecated. Please use When an item is modified (V2) instead.
Triggers a flow when an item is modified/inserted 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
Definitions
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. |
TablesList
Represents a list of tables.
| Name | Path | Type | Description |
|---|---|---|---|
|
value
|
value | array of Table |
List of Tables |