connectors-create-api-sqlazure.md

title	description	author	manager	ms.author	ms.date	ms.topic	ms.service	services	ms.reviewer	ms.suite	tags
Connect to SQL Server or Azure SQL Database - Azure Logic Apps | Microsoft Docs	How to access and manage SQL databases on premises or in the cloud by automating workflows with Azure Logic Apps	ecfan	jeconnoc	estfan	05/15/2018	article	logic-apps	logic-apps	klam, LADocs	integration	connectors

Connect to SQL Server or Azure SQL Database from Azure Logic Apps

This article shows how you can access data in your SQL database from inside a logic app with the SQL Server connector. That way, you can automate tasks, processes, and workflows that manage your SQL data and resources by creating logic apps. The connector works for both SQL Server on premises and for Azure SQL Database in the cloud.

You can create logic apps that run when triggered by events in your SQL database or in other systems, such as Dynamics CRM Online. Your logic apps can also get, insert, and delete data along with executing SQL queries and stored procedures. For example, you can build a logic app that automatically checks for new records in Dynamics CRM Online, adds items to your SQL database for any new records, and then sends email alerts.

If you don't have an Azure subscription, sign up for a free Azure account. If you're new to logic apps, review What is Azure Logic Apps and Quickstart: Create your first logic app. For connector-specific technical information, see the SQL Server connector reference.

Prerequisites

• The logic app where you need access to your SQL database. To start your logic app with a SQL trigger, you need a blank logic app.

• An Azure SQL database or a SQL Server database

  Your tables must have data so that your logic app can return results when calling operations. If you create an Azure SQL Database, you can use sample databases, which are included.

• Your SQL server name, database name, your user name, and your password. You need these credentials so that you can authorize your logic to access your SQL server.

  • For Azure SQL Database, you can find these details in the connection string, or in the Azure portal under the SQL Database properties:

    "Server=tcp:<yourServerName>.database.windows.net,1433;Initial Catalog=<yourDatabaseName>;Persist Security Info=False;User ID=<yourUserName>;Password=<yourPassword>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

  • For SQL Server, you can find these details in the connection string:

    "Server=<yourServerAddress>;Database=<yourDatabaseName>;User Id=<yourUserName>;Password=<yourPassword>;"

• Before you can connect logic apps to on-premises systems, such as SQL Server, you must set up an on-premises data gateway. That way, you can select the gateway when you create the SQL connection for your logic app.

Add SQL trigger

In Azure Logic Apps, every logic app must start with a trigger, which fires when a specific event happens or when a specific condition is met. Each time the trigger fires, the Logic Apps engine creates a logic app instance and starts running your app's workflow.

1. In the Azure portal or Visual Studio, create a blank logic app, which opens Logic Apps Designer. This example uses the Azure portal.

2. In the search box, enter "sql server" as your filter. From the triggers list, select the SQL trigger that you want.

   For this example, select this trigger: SQL Server - When an item is created

   

3. If you're prompted for connection details, create your SQL connection now. Or, if your connection already exists, select the Table name that you want from the list.

   

4. Set the Interval and Frequency properties, which specify how often your logic app checks the table.

   This example only checks the selected table, nothing else. To do something more interesting, add actions that perform the tasks you want.

   For example, to view the new item in the table, you might add other actions, such as create a file that has fields from the table, and then send email alerts. To learn about other actions for this connector or other connectors, see Logic Apps connectors.

5. When you're done, on the designer toolbar, choose Save.

   This step automatically enables and publishes your logic app live in Azure.

Add SQL action

In Azure Logic Apps, an action is a step in your workflow that follows a trigger or another action. In this example, the logic app starts with the Recurrence trigger, and calls an action that gets a row from a SQL database.

1. In the Azure portal or Visual Studio, open your logic app in Logic Apps Designer. This example uses the Azure portal.

2. In the Logic App Designer, under the trigger or action, choose New step > Add an action.

   

   To add an action between existing steps, move your mouse over the connecting arrow. Choose the plus sign (+) that appears, and then choose Add an action.

3. In the search box, enter "sql server" as your filter. From the actions list, select any SQL action that you want.

   For this example, select this action, which gets a single record: SQL Server - Get row

   

4. If you're prompted for connection details, create your SQL connection now. Or, if your connection exists, select a Table name, and enter the Row ID for the record that you want.

   

   This example returns only one row from the selected table, nothing else. To view the data in this row, you might add other actions that create a file with fields from the row for later review, and store that file in a cloud storage account. To learn about other actions in this connector or other connectors, see Logic Apps connectors.

5. When you're done, on the designer toolbar, choose Save.

Connect to your database

[!INCLUDE Create connection general intro]

[!INCLUDE Create a connection to SQL Server or Azure SQL Database]

Process data in bulk

When you work with result sets so large that the connector doesn't return all the results at the same time, or you want better control over the size and structure for your result sets, you can use pagination, which helps you manage those results as smaller sets.

[!INCLUDE Set up pagination for results exceeding default page size]

Create a stored procedure

When getting or inserting multiple rows, your logic app can iterate through these items by using an until loop within these limits. But, sometimes your logic app has to work with record sets so large, such as thousands or millions of rows, that you want to minimize the costs for calls to the database.

Instead, you can create a stored procedure that runs in your SQL instance and uses the SELECT - ORDER BY statement to organize the results the way you want. This solution gives you more control over the size and structure of your results. Your logic app calls the stored procedure by using the SQL Server connector's Execute stored procedure action.

For solution details, see these articles:

• SQL Pagination for bulk data transfer with Logic Apps

• SELECT - ORDER BY Clause

Connector-specific details

For technical information about this connector's triggers, actions, and limits, see the connector's reference details.

Next steps

• Learn about other Logic Apps connectors