Tag: Azure SQL Database

Posted on

Iterate Over the Results of an Azure SQL Stored Procedure in an Azure Logic App

Introduction

If you need to quickly create a business workflow or other B2B integration, look no further then Azure Logic Apps. Azure Logic Apps is a cloud based workflow and integration service. With the multitude of built in connectors you can easily automate many common business workflows. The Azure SQL Database connector and execute stored procedure action were of interest to me when I needed to implement an automated email notification. The output of a stored procedure from a source system would provide me a list of users who would receive reminder emails. Azure Logic Apps provides the capability of iterating over results of an action, however iterating over the results of a stored procedure is not as straight forward as some of the other examples. In this post I am going to use the automating email notifications example to illustrate what I needed to do to iterate over the results of a stored procedure. Continue reading “Iterate Over the Results of an Azure SQL Stored Procedure in an Azure Logic App”

Posted on

Error 40197 Error Code 4815 Bulk Insert into Azure SQL Database

I have been working on a large scale ETL project, one of the data sources I regularly pull data from is Salesforce.  If anyone has worked with Salesforce they know it can be a free for all with object field changes.  This alone makes pulling data regularly from the Salesforce REST API difficult since there can be so much activity with the objects.  Not to mention the fact that the number of custom fields that can be added to a single object ranges into the hundreds.  With these factors in play it can be a daunting task to debug errors when they inevitably crop up.

During a recent run of my process I started to receive the following error:

Error 40197 The service has encountered an error processing your request. Please try again. Error code 4815. A severe error occurred on the current command. The results, if any, should be discarded.

Here is a list of error codes however it was not too helpful in my situation: SQL error codes

In my ETL process I am using Entity Framework and EntityFramework.BulkInsert-ef6, so I am doing bulk inserts into my Azure SQL Database.  Since I know there is a good chance there was a change with the object definition in Salesforce that could be the cause of this error that is where I started to investigate.  As it turns out one of the fields length was changed from 40 to 60, which means the original table I have created with a column size varchar(40) is going to have a problem.  In my case this error happened when the amount of data was larger then the field size definition in the table.  Hopefully this post will give someone else another troubleshooting avenue for this error.

Posted on

Resolving InvalidTemplate Error Running Azure Logic App Manually (Run Now)

Problem

Recently I have been testing out Azure Logic Apps as a possible tool for enterprise application data synchronization.  The first example I spiked out was to pull newly entered data from a table in an Azure SQL Database and push it into a Salesforce object.  This is a pretty common example and extremely easy to implement with an Azure Logic App.  My scenario included a Microsoft SQL Connector to my Azure SQL Database with a polling query, set to execute every hour.  I added a Salesforce Connector mapping the database fields to the custom object defined in Salesforce.  Very basic logic app, with a sql trigger and create action on the Salesforce Connector.  The next step was to test this out to see if it worked, I could have waited the hour execution interval, but I noticed a Run Now button on the main logic app blade.

image

Every time I clicked the Run Now button for the logic app I received the following error:

{"code":"InvalidTemplate","message":"Unable to process template language expressions in action 'salesforceconnector' inputs at line '1' and column '11': 'Template language expression cannot be evaluated: the property 'outputs' cannot be selected.'."}

Solution

The solution here is pretty simple, when you have a logic app with a trigger defined, DO NOT click the Run Now button.  If you would like to run the logic app manually you will notice the first tile that comes up has a check box to do just that.

image

I never even noticed this option until I ran into this issue.  Use this option if you need manual control over the logic app execution.

Note: As of this writing I do know the logic app designer is getting a major overhaul in the coming weeks.  I will do my best to update this post once those new bits become available.  Watch this to see a demo of what is coming.

Posted on

Querying Azure SQL Database from an Azure Data Lake Analytics U-SQL Script

Introduction

With the public preview of Azure Data Lake Analytics and U-SQL scripting language becoming available, doing big data processing has become simpler and less daunting for the non-hadooper.  Besides the simplicity, the ability to query external data sources such as Azure SQL Database, Azure SQL Data Warehouse and SQL running on Azure VM, from within a U-SQL script, creates a very compelling case to jump on board.  The focus of this post is my experience with getting that external connection up and running with Azure SQL Database serving as my external source.

Prerequisites

Continue reading “Querying Azure SQL Database from an Azure Data Lake Analytics U-SQL Script”