Post
Ask Question

Fetch The Values From Azure SQL Database Using Execute SQL Action In Nintex Workflow

Approach

Follow the steps given below to fetch the data from Azure SQL database (PAAS).

Step 1

Add Execute SQL action and open the configure Window.

 

Step 2

Open the right top down arrow and click Configuration.

In Configuration screen, you should provide the values given below.

  • Connection string.
  • Query - Here, you should give the query to execute in Execute SQL action.
  • Column to retrieve - Column name, which you want to fetch from SQL table.
  • Result Count - Output result count.
  • Retrieved column values - Create Collection variable to save the output.
  • Results in XML - Create a variable to save the output in XML format.

Connection string

In this section, you should give the Server name, user ID (if your user Id contains a domain name. You should add @sqlserver name in the end of the user ID), password and host name (this should be your Azure SQL Server).

Sample

  1. Server = tcp: servername.database.windows.net, 1433;  
  2. Initial Catalog = DCCSQLDatabase;  
  3. Persist Security Info = False;  
  4. User ID = username @domain.com @dccsqlserver;  
  5. Password = ** ** ** * ;  
  6. MultipleActiveResultSets = False;  
  7. Encrypt = True;  
  8. TrustServerCertificate = False;  
  9. Connection Timeout = 30;  

Once configured, all the things in Execute SQL action nedds to be saved. Publish the Workflow.

Azure Firewall Settings

Every organization uses Office 365 and restricts computers on your network from connecting to the Internet. You'll find the endpoints given below (IPv4 address ranges), which you should include in an organization outbound, which allows the lists to ensure the computers can successfully use Office 365.

For Office 365 Sharepoint Online, as a onetime activity add the IP specified in the popup to the firewall settings of Azure SQL Server Firewall settings page. Along with this, add 11 more IP ranges to Firewall settings page in Azure SQL Server, as shown below.

 
 
 

The above IP ranges may be updated, which is based on Microsoft updates. These updates needs to be periodically checked across in this site.

Once configured, Firewall settings in Azure SQL database, you can start the Workflow or an event, so the Workflow will fetch the values from Azure SQL database.

Note

For testing, you can add the log history action in your Workflow and verify the output of Execute SQL action.

Output Collection should be, as shown below.

 
Keynotes
  • Fetch the values from Azure SQL database, using Execute SQL action in Nintex Workflow.
  • Configure Azure SQL Server Firewall settings.