Database actions

Article
08/29/2024

To connect to a database, use the Open SQL connection action. A connection string specifies all information necessary to connect to a database, such as the driver, the database, server names, and the username and password.

The following connection string connects to an Excel database:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myExcelFile.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

The following connection string connects to an Access database:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb; Persist Security Info=False;

Note

Power Automate for desktop is a 64-bit application, only 64-bit installed drivers are available for selection in the Open SQL connection action.

Configure a connection string manually

To manually build a connection string:

Select Build connections string to open the Data Link Properties dialog. The data link tool helps you compose the required connection string step by step.
Once you access the wizard, select the correct driver for the database under Provider.
Next, under the Connection tab, enter the remaining details such as the server name, the username, password, and database name. Select Test Connection to test that the connection string connects successfully.
Specify a connection timeout and other network settings in the Advanced tab.

Open SQL connection

Open a new connection to a database.

Input parameters

Argument	Optional	Accepts	Default Value	Description
Connection string	No	Text value		The connection string to use to connect to the database

Variables produced

Argument	Type	Description
SQLConnection	SQL connection	A handle for the new SQL connection

Exceptions

Exception	Description
Can't connect to data source	Indicates a problem connecting to the data source
Invalid connection string	Indicates that the specified connection string is invalid

Execute SQL statement

Connect to a database and execute a SQL statement.

To execute an SQL query, use the Execute SQL statement action. Begin by providing a valid connection to the respective database. Select the desired option in the Get connection by property. You can either provide an SQL connection variable (you can create one by using the Open SQL connection action) or by providing the Connection string. Then input the SQL query you want to execute in the SQL statement property. You can also modify the timeout seconds of the action or leave the default option (30 seconds).

Natural language to script powered by copilot (preview)

[This topic is prerelease documentation and is subject to change.]

Natural language to script is a new copilot capability added in Power Automate for desktop. It lets you quickly generate scripts used in the execute SQL statement action by providing a description in natural language.

Important

This is a preview feature.
Preview features aren’t meant for production use and may have restricted functionality. These features are available before an official release so that customers can get early access and provide feedback.

Availability by region

Currently, natural language to script in Power Automate for desktop is only available in environments located in the United States.

Availability by account type

Currently, natural language to script in Power Automate for desktop is only available for users with a work or school account.

Note

If your environment is in the region where this feature is available and you still can't experience the copilot in Power Automate for desktop experience, contact your tenant administrator. They might have turned off the copilot functionality.

How to generate scripts using copilot and natural language

To generate SQL code in the execute SQL statement select Generate script with Copilot.

Screenshot of the execute SQL statement action with the Generate script with Copilot button highlighted

The create prompt screen opens where you type your natural language prompt.

Screenshot of the execute SQL statement action with the prompt dialog open

To create a SQL script, write your prompt and select Generate. If you need to re-create it, you can change the prompt and select Regenerate. Otherwise, select Use this script to go back to the main action window, where you can modify your prompt and add any necessary variables.

Screenshot of the Generate Script with Copilot dialog open, that shows a generated SQL statement

Important

Make sure that you always review the content generated by the AI model.

Help us improve this feature

Send feedback by selecting the thumb up or thumb down icon underneath the AI-generated content. Once you do, a feedback dialog appears, which you can use to submit feedback to Microsoft. Animated gif that shows the copilot answering a user's question from the designer.

Note

If you can't see the dialog, your tenant admin might have turned it off. More information: Disabling the user feedback functionality

Disabling the user feedback functionality

As a tenant admin you can prevent your users from sending feedback to Microsoft by disabling the disableSurveyFeedback tenant setting. Find more information about viewing and setting tenant settings here:

Data subject rights requests on user feedback

Tenant administrators can view, export, and delete the feedback provided by their users by signing in to the Microsoft 365 admin center, and then selecting Health > Product feedback.