Email reports from SQL Azure

For one of my projects I wanted to do some daily checks and send the results by email to me and some colleagues.
Of course this can be done by creating a worker role, but there is another (easier) way, by using Windows Azure Mobile Services. This way you don’t have to deploy anything, just create a stored procedure in SQL Azure and configure the Mobile Service.
This is how to do it (assuming you have a Windows Azure account and an SQL azure database):

1. Create a mobile service from the Windows Azure management portal.

Pick a unique name for the mobile service, select your database (or create one),select a region (preferrably where your database is located) and set javascript as backend.

Next you have to select the database and provide your credentials.

You can ignore the warning about the region (sometimes the mobile service isn’t available in the same region as the database).

2. After creation completed (this can take up to a minute or more), select your newly created mobile service and go to the ‘Scheduler’ tab. Create the schedule, name it and select ‘On demand’ (for testing purposes).

3. Select the newly created schedule and select the script tab.

This is where we will call the database and send the email, but before we create this script, we need to configure SendGrid (for sending the email). Sendgrid is an Azure App service you can configure from the management portal and one of the advantages is the node.js module is available directly from your javascript.

You can do this by creating a new item in the management portal and choose the store.

Go to the App Service and choose ‘SendGrid Email Delivery.Simplified. SMTP & Web API’

Pick a plan, choose a name and region and finish your purchase.

Now you should select the SendGrid app service from the Add-ons page.

Select the Connection Info and write down the username and password. We will need this in the next step.

4. Go back to the schedule from your mobile service and select the ‘script’ tab again.

Paste the following code in the script.

function DailyReport() {
    var SendGrid = require('sendgrid').SendGrid;
    executeStoredProc();
    
    function sendEmail(item) {
        var sendgrid = new SendGrid('azure_*****************@azure.com', '************');       
        sendgrid.send({
            to: ['changeme@changeme.com'],
            from: 'changeme@changeme.com',
            subject: 'Daily Report',
            html: item
        }, function(success, message) {
            // If the email failed to send, log it as an error so we can investigate
            if (!success) {
                console.error(message);
            }
        });
    }
    
    function executeStoredProc() {
        console.log("Executing Daily Report...");
        mssql.query('EXEC dbo.DailyReport', {
            success: function(results) {
            console.log("Finished executing Daily Report.");
            console.log(results[0]);
            sendEmail(results[0].MailBody);
            }
        });
    }
}

Fill in your SendGrid username and password (from step 3) on line 6 and modify the email addresses, subject etc. on line 8-10.
Check https://github.com/sendgrid/sendgrid-nodejs for more infromation about the SendGrid node.js module.

5. Last is creating the DailyReport stored procedure and setting the permissions. In this example I will create the email body in the stored procedure as html.

CREATE PROCEDURE [dbo].[DailyReport]
AS
BEGIN
	DECLARE @mailbody NVARCHAR(MAX)
	SELECT	@mailbody = 'This is a test'
	SELECT @mailbody AS MailBody
END
GO
GRANT EXECUTE ON [dbo].[DailyReport] TO [*********LoginUser]
GO

When your created the mobile service there was a login created in the database you selected. This is the user that will execute the stored procedure and you should give it execute permissions. So change *********LoginUser on line 13 to this user. You can get the username by connecting to your SQL azure database from the SQL Server management studio, select the database, go to security and then to users.

Now you should be ready to test it.

Go to schedule again and press the run once button.

When everything is working, you can define the schedule you want and enable the schedule.

Good luck.