Information on the SQL Connector can be found at: Azure Website
The SQL Connector can be used as a trigger or an action in a logic app and supports data in JSON and XML formats. First you will have to provision the SQLConnector, then you will be able to use it in a logic app and perform additional configuration.
I don’t know what happens when you change the provisioning of the SQL Connector when it is already used in a logic app. Can you make a backwards compatible change the definition of the SQL Connector when it is in use?
To use the SQL connector as a trigger, enter the Data Available Query and Poll Data Query values. Data Available Query is executed on the schedule you enter and determines if any data is available. Since this query only returns a scalar number, it can be tuned and optimized for frequent execution.
Poll Data Query is only executed when the Data Available Query indicates that data is available. This statement executes within a transaction and is only committed when the extracted data is durably stored in your workflow. It is important to avoid infinitely re-extracting the same data. The transactional nature of this execution can be used to delete or update the data to ensure it isn’t collected the next time data is queried.
To use the SQL Connector as an action, enter the name of the Tables and/or Stored Procedures you entered when you created the SQL Connector. When adding the SQL Connector to a logic app, specify the action to be performed, ie. select from table or execute stored procedure.
Note 1: Certain restrictions apply to the SQL queries you can perform. You can’t for instance use SELECT TOP 1 in a Poll Data Query. My workaround is to use a stored procedure to perform the action.
Note 2: Only with a premium subscription, you can specify a polling interval of less than 1 minute.
Note 3: It’s not possible to execute a stored procedure without parameters through a SQLConnector action. A first workaround is explained in the following blogpost: microsoft-sql-connector-error-on-stored-procedure-without-input-parameters. I personally prefer to build a custom API App that performs the stored procedure via ADO.Net. Below is the controller that I used:
public class ProcessController : ApiController
{
HttpResponseMessage response;
int retVal;
// POST api/values
[ActionName(“ResolveProcess”)]
public HttpResponseMessage Post(string storedProcedure)
{
try
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“bvgo-appservices”].ConnectionString))
{
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedure;
conn.Open();
retVal = cmd.ExecuteNonQuery();
}
response = new HttpResponseMessage();
response.StatusCode = HttpStatusCode.Accepted;
return response;
}
catch (Exception ex)
{
response = new HttpResponseMessage();
response.StatusCode = HttpStatusCode.InternalServerError;
string errorMsg = ex.Message.Replace(“\r\n”, string.Empty);
errorMsg = errorMsg.Replace(“\n”, string.Empty);
response.ReasonPhrase = errorMsg;
return response;
}
}
}
When you want to change the package settings later on, browse to the SQL Connector API App, click the link under Host, choose All Settings and then Application Settings. Now you can see the entered configuration values again.
Finally, when you browse the API App you will see a button to download the XML Schemas. You need the SQL schemas to compose the mapping from the input schema to the SQL Statement schema.
Think * Share * Integrate 