Resend failed emails from Database Mail Stored Procedure

Today at work we had an issue with our mail servers that is still being looked at. The problem has caused any email sent from SQL Server Database mail to an external address to fail to send. I therefore needed a way to resend these emails once the problem was fixed. I googled ,found and amended a stored procedure which does the trick. I include it below for reference. Note you can pass in a test email address (i.e. your own) to test the resend without actually sending the unsent emails to the actual recipients.

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[sysmail_resend_timeout]    Script Date: 09/18/2012 16:48:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sysmail_resend_timeout]
	@Test bit,
	@TestRecipientEmail varchar(100)
AS
    BEGIN
        SET NOCOUNT ON

        DECLARE SYSMAIL_LOG_RESEND_CURSOR CURSOR READ_ONLY
        FOR
             SELECT DISTINCT
                    l.mailitem_id ,
                    p.name ,
                    m.recipients ,
                    m.subject ,
                    m.body_format ,
                    m.body,
                    m.file_attachments,
                    m.attachment_encoding,
                    m.copy_recipients,
                    m.blind_copy_recipients,
                    m.from_address
             FROM    msdb.dbo.sysmail_log l WITH ( NOLOCK )
                    JOIN msdb.dbo.sysmail_mailitems m WITH ( NOLOCK ) ON m.mailitem_id = l.mailitem_id
                    JOIN msdb.dbo.sysmail_profile p WITH ( NOLOCK ) ON p.profile_id = m.profile_id
            WHERE   l.event_type = 3
                    AND m.sent_status = 2
					AND l.mailitem_id > 6814
            ORDER BY l.mailitem_id

        OPEN SYSMAIL_LOG_RESEND_CURSOR

        WHILE ( 1 = 1 )
            BEGIN
                DECLARE @mailitem_id INT ,
                    @profile_name NVARCHAR(128) ,
                    @recipients VARCHAR(MAX) ,
                    @subject NVARCHAR(255) ,
                    @body_format VARCHAR(20) ,
                    @body NVARCHAR(MAX),
                    @file_attachments NVARCHAR(MAX),
                    @attachment_encoding VARCHAR(20),
                    @copy_recipients VARCHAR(MAX),
                    @blind_copy_recipients VARCHAR(MAX),
                    @from_address VARCHAR(MAX)

                FETCH NEXT FROM SYSMAIL_LOG_RESEND_CURSOR INTO @mailitem_id, @profile_name, @recipients, @subject, @body_format, @body,
                 @file_attachments,
                    @attachment_encoding,
                    @copy_recipients,
                    @blind_copy_recipients,
                    @from_address

                IF NOT @@FETCH_STATUS = 0
                    BEGIN
                        BREAK
                    END

                PRINT 'SENDING MAIL: ' +  CONVERT(VARCHAR, GETDATE(), 121) + CHAR(9) + CONVERT(VARCHAR, @mailitem_id) + CHAR(9) + @recipients

				IF ISNULL(@Test,0) = 1
				BEGIN
					SET @recipients = @TestRecipientEmail
					SET @copy_recipients = @recipients
					SET @blind_copy_recipients = @recipients
				END

					EXEC  msdb.dbo.sp_send_dbmail
						@profile_name = @profile_name,
						@recipients=@recipients,
						@copy_recipients = @copy_recipients,
						@blind_copy_recipients = @blind_copy_recipients,
						@subject=@subject,
						@body=@body,
						@body_format=@body_format,
						@from_address=@from_address,
						@reply_to=@from_address,
						@file_attachments=@file_attachments

				IF ISNULL(@Test,0) = 0
				BEGIN
					UPDATE  msdb.dbo.sysmail_mailitems
					SET     sent_status = 3
					WHERE   mailitem_id = @mailitem_id
				END
            END

        CLOSE SYSMAIL_LOG_RESEND_CURSOR

        DEALLOCATE SYSMAIL_LOG_RESEND_CURSOR

    END

GO

Advertisements

Sencha Touch 2 example of syncing localStorage store with remote JSONP proxy store

 

Please read my new post on syncing. https://lalexgraham.wordpress.com/2013/06/14/using-net-mvc-and-sencha-touch-syncing-localstorage-with-remote-storage-with-ext-ux-offlinesyncstore/

 

I have been playing around with Sencha Touch 2 for a long time now but have not used localStorage much. I recently posted about how I store data remotely and used it in Sencha Touch . But continually using remote data is not ideal and unnecessary when dealing with largely static data. So I wanted a way to store the data locally and only refresh it when something changed. To make it even simpler to begin with I just wanted to sync a localStorage store with the data from the remote JSONP proxy.

I googled around and found several posts, this being the most useful. But nothing I found demo’d how to get data from somewhere and sync it. My code at this github repository is my attempt at doing this, and below is a walk-through of that code. As always I’d welcome any feedback on my method.

EDIT. Please look at comemnts from pentool and kevin.novientis below which give some pointers on making this all work even better.

To begin with I created a boilerplate Sencha Touch 2 (ST2) app using the sencha sdk and named it “test”. I then added just one view which would list each item’s ‘title’ coming back from the store:

Ext.define('test.view.ListMarkers', {
  	extend: 'Ext.dataview.DataView',
    	xtype: 'listMarkersCard',
	config: {
        	itemTpl: '{title}'
    	}
});

I then created two stores, one for the remote data and one for the localStorage. The latter will sync with the other once it has loaded. The ‘remote’ store is just pulling data from a node.js app in turn calling a mongodb database on a local url that I have previously set-up (http:localhost:5000). More info on how to do this side here. The data is a list of markers intended for a map, but for this demo I am just listing their titles on the card I set-up previously. Once the store has loaded, the app will then use the localStorage data. The remote store was saved to app\store\Markers.js:

Ext.define('test.store.Markers', {
	extend: "Ext.data.Store",
	requires: [
        'Ext.data.proxy.JsonP'
    ],
	config: {
		storeId: 'markerStore',
		model: "test.model.Marker",
		proxy: {
			type: 'jsonp',
			callbackKey: 'callback',
        	url: 'http://localhost:5000/markers',
			reader: {
			    type: 'json',
			    rootProperty: 'data',
			    successProperty: 'success'
			}
  	 	}
	}
});

so this is calling a url to retrieve JSONP data.

The localStorage store is a lot simpler and is saved to app\store\MarkersLocalStorage.js

Ext.define('test.store.MarkersLocalStorage', {
	extend: "Ext.data.Store",
	config: {
		storeId: 'markerStoreLocalStorage',
		model: "test.model.Marker"
		//autoLoad: true
	}
});

Notice that both stores use the same Model, saved in app\model\Marker.js:

Ext.define("test.model.Marker", {
	extend: "Ext.data.Model",
	config: {
		fields: [
			{name: "markerID", type:"string"},
	     {name: "title", type: "string"},
	 	 {name: "lng", type: "string"},
	 	 {name: "lat", type: "string"},
	 	 {name: "icon", type: "string"},
	 	 {name: "description", type: "string"}
		],
		proxy: {
            type: 'localstorage',
            id  : 'proxyMarkers'
        }
     }
});

Finally, the app\controller\Main.js file that syncs the localStorage with the remote data when it shows the listMarkersCard:

Ext.define('test.controller.Main', {
    extend: 'Ext.app.Controller',
	config: {
		refs: {listMarkersCard:'listMarkersCard'},
		control: {
			listMarkersCard : {
				show: 'loadMarkers'
			}
		}
	},
	loadMarkers: function() {
		//set up refs to the two stores
		var markerStore = Ext.getStore('markerStore');
		var markerStoreLocalStorage= Ext.getStore('markerStoreLocalStorage');

		//load the localStorage store
		markerStoreLocalStorage.load();

		// check if localStorage contains data
		if ((markerStoreLocalStorage.getCount()) == 0) {
			// nothing found so  we need to load the data from external source
			console.log('localStorage data not found');
			//hand off to onMarkerStoreLoad function (below)
			markerStore.on({
			    load: 'onMarkerStoreLoad',
			    scope: this
			});
			//call load to trigger above
			markerStore.load();
		} else {
			// we are ok, just print some debug
			console.log('localStorage data found');
			console.log('localStorage count:' + markerStoreLocalStorage.getCount());
		}
		//finally set the list's store to localStorage
		this.getListMarkersCard().setStore(markerStoreLocalStorage);

	},
	onMarkerStoreLoad: function() {
		//set up refs
		var markerStoreLocalStorage= Ext.getStore('markerStoreLocalStorage');
		var markerStore = Ext.getStore('markerStore');
        	//loop through each data item and add to localStorage
		markerStore.each(function(item){
			markerStoreLocalStorage.add(item);
		});
		markerStoreLocalStorage.sync();
   	 }
});

Hopefully the comments describe what is happening but basically the data in the remote store is looped over and loaded into the localStorage. The listMarkersCard store is then set to the markerStoreLocalStorage store.

So that’s it in a nutshell. The localStorage loads up and the app uses that instead of calling on a url each time the list card loads. The functionality could be updated to refresh the localStorage only when something changes on the remote data or like with the original Sencha Touch 1 example only when the device is online. If anyone out there has some good methods or strategies on how to manage offline (localStorage) and online data using Sencha Touch then I’d love to hear them.