Wednesday, May 11, 2016

SQL Server "Asynchronous" Stored Procedure (insert only)

So you have a SQL server stored procedure for logging, and its running a little slower than you like. There is no output, you just need to make sure the log message is put into the system. It would be really nice if there were a way to say "run this, and the client doesn't need to wait for a response", but unfortunately that's not a simple built-in feature. So how do you do it, with a minimum of headache?

Much of the credit for this goes to http://rusanu.com/2009/08/05/asynchronous-procedure-execution/. That post solves a slightly different problem - you want to execute a slow stored procedure that has no inputs, and check back later for the output -- without leaving a SQL connection open. But it was the basis of my solution here.

First, we setup a trivial example -- the destination table for the log message, and the stored procedure, which is slow but eventually does the insert. We will want to see what happens if the stored procedure fails, so we'll have a simple check to force an error.

CREATE TABLE [LogDestination]([LogValue] [varchar](max));
GO

CREATE PROCEDURE [usp_SlowProcedure]
  @message VARCHAR(MAX) AS
BEGIN
WAITFOR DELAY '0:00:00.5' -- wait 1/2 second
IF @message LIKE '%ERROR%'
  raiserror(@message, 16, 10);
ELSE
  INSERT INTO [LogDestination]([LogValue]) VALUES(@message);
END
GO

The solution will involve creating a QUEUE and a SERVICE, so you need to make sure your database has the Service Broker turned on. You can do that with this command:

ALTER DATABASE [MyDatabase] SET ENABLE_BROKER;

It sometimes seems to run forever -- it won't want to finish if there are any connections open on the database. You can force those to be closed with this option:

ALTER DATABASE [MyDatabase] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Now to start creating the new objects. For reasons of circular dependencies, you have to CREATE one of these first, and then ALTER it later. That's just the breaks. Let's put down the fundamental parts:

CREATE PROCEDURE [usp_AsyncExecActivated] AS
  PRINT 'placeholder'
GO

CREATE QUEUE [AsyncExecQueue]
 WITH ACTIVATION(STATUS = ON
                ,PROCEDURE_NAME = [usp_AsyncExecActivated]
                ,MAX_QUEUE_READERS = 1
                ,EXECUTE AS OWNER)
GO

CREATE SERVICE [AsyncExecService] ON QUEUE [AsyncExecQueue] ([DEFAULT]);
GO

CREATE PROCEDURE [usp_SlowProcedureAsync]
   @message VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @h UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @h
        FROM SERVICE [AsyncExecService]
        TO SERVICE 'AsyncExecService'
        WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @h (@message);
END CONVERSATION @h;
END
GO

Now we have:
  • [AsyncExecQueue] - the queue to hold the messages on
  • [AsyncExecService] - because only a service can write to a queue
  • [usp_AsyncExecActivated] - stored procedure that will run in the background whenever the queue is written to
  • [usp_SlowProcedureAsync] - to wrap all this up with, hiding the mess from the users. It has the same signature as [usp_SlowProcedure], but will return immediately.
One more consideration -- since this is completely asynchronous, the only errors the client can get are if the database is offline, out of space, etc. But if [usp_SlowProcedure] has an error, we want a table to just shove that input into, without leaving it on the queue. Or I guess you could omit this part, and the code that writes to it.

CREATE TABLE [AsyncErrored]([LogValue] [varbinary](max), [ErrorTime] DATETIME2 DEFAULT SYSDATETIME());
GO

Now we ALTER the background stored procedure, which is still just a placeholder, to actually do the processing.

ALTER PROCEDURE usp_AsyncExecActivated
AS
BEGIN

SET NOCOUNT ON;

DECLARE @h UNIQUEIDENTIFIER = NULL
      , @messageTypeName SYSNAME = NULL
      , @messageBody VARBINARY(MAX) = NULL;

RECEIVE TOP(1)
      @h = [conversation_handle]
    , @messageTypeName = [message_type_name]
    , @messageBody = [message_body]
FROM [AsyncExecQueue];

WHILE (@h IS NOT NULL)
  BEGIN

    BEGIN TRY
      DECLARE @message VARCHAR(MAX) = CAST(@messageBody AS VARCHAR(MAX));
      EXECUTE [usp_SlowProcedure] @MESSAGE
    END TRY
    BEGIN CATCH
      INSERT INTO [AsyncErrored]([LogValue]) VALUES(@messageBody);
    END CATCH
    END CONVERSATION @h WITH CLEANUP;
    SET @h = NULL;
    RECEIVE TOP(1)
          @h = [conversation_handle]
        , @messageTypeName = [message_type_name]
        , @messageBody = [message_body]
    FROM [AsyncExecQueue];

  END -- WHILE

END -- PROCEDURE usp_AsyncExecActivated

GO

That's all there is to it. But what good is a bunch of tables without running some tests to see it in action?

DECLARE @countQueue VARCHAR(10), @countDestination VARCHAR(10), @countErrored VARCHAR(10)

PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ' Test 1: invoke [usp_SlowProcedureAsync] one time with simple input.'
EXECUTE [usp_SlowProcedureAsync] 'Test input message';
SET @countQueue = (SELECT COUNT(1) FROM [AsyncExecQueue])
SET @countDestination = (SELECT COUNT(1) FROM [LogDestination])
SET @countErrored = (SELECT COUNT(1) FROM [AsyncErrored])
PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ' Queue=' + @countQueue + ' Destination=' + @countDestination + ' Errored=' + @countErrored
WHILE (@countQueue <> '0')
 BEGIN
  WAITFOR DELAY '0:00:00.2'
  SET @countQueue = (SELECT COUNT(1) FROM [AsyncExecQueue])
  SET @countDestination = (SELECT COUNT(1) FROM [LogDestination])
  SET @countErrored = (SELECT COUNT(1) FROM [AsyncErrored])
  PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ' Queue=' + @countQueue + ' Destination=' + @countDestination + ' Errored=' + @countErrored
 END

DELETE FROM [LogDestination];
DELETE FROM [AsyncErrored];
PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ' Test 2: invoke [usp_SlowProcedureAsync] five times, with fourth causing an error in [usp_SlowProcedure].'
EXECUTE [usp_SlowProcedureAsync] 'Test message 1';
EXECUTE [usp_SlowProcedureAsync] 'Test message 2';
EXECUTE [usp_SlowProcedureAsync] 'Test message 3';
EXECUTE [usp_SlowProcedureAsync] 'Test ERROR 4';
EXECUTE [usp_SlowProcedureAsync] 'Test message 5';
SET @countQueue = (SELECT COUNT(1) FROM [AsyncExecQueue])
SET @countDestination = (SELECT COUNT(1) FROM [LogDestination])
SET @countErrored = (SELECT COUNT(1) FROM [AsyncErrored])
PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ' Queue=' + @countQueue + ' Destination=' + @countDestination + ' Errored=' + @countErrored

WHILE (@countQueue <> '0')
 BEGIN
  WAITFOR DELAY '0:00:00.2'
  SET @countQueue = (SELECT COUNT(1) FROM [AsyncExecQueue])
  SET @countDestination = (SELECT COUNT(1) FROM [LogDestination])
  SET @countErrored = (SELECT COUNT(1) FROM [AsyncErrored])
  PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ' Queue=' + @countQueue + ' Destination=' + @countDestination + ' Errored=' + @countErrored

END

Here's some output I got from the above test run. You can see the 1/2 second pauses between each insert being processed.

15:59:16.62 Test 1: invoke [usp_SlowProcedureAsync] one time with simple input.
15:59:16.85 Queue=2 Destination=0 Errored=0
15:59:17.07 Queue=1 Destination=0 Errored=0
15:59:17.28 Queue=1 Destination=0 Errored=0
15:59:17.48 Queue=0 Destination=1 Errored=0

(1 row(s) affected)

(0 row(s) affected)
15:59:17.48 Test 2: invoke [usp_SlowProcedureAsync] five times, with fourth causing an error in [usp_SlowProcedure].
15:59:17.48 Queue=9 Destination=0 Errored=0
15:59:17.70 Queue=9 Destination=0 Errored=0
15:59:17.90 Queue=9 Destination=0 Errored=0
15:59:18.10 Queue=7 Destination=1 Errored=0
15:59:18.30 Queue=7 Destination=1 Errored=0
15:59:18.51 Queue=7 Destination=2 Errored=0
15:59:18.71 Queue=5 Destination=2 Errored=0
15:59:18.91 Queue=5 Destination=2 Errored=0
15:59:19.12 Queue=3 Destination=3 Errored=0
15:59:19.32 Queue=3 Destination=3 Errored=0
15:59:19.52 Queue=3 Destination=3 Errored=0
15:59:19.72 Queue=1 Destination=3 Errored=1
15:59:19.93 Queue=1 Destination=3 Errored=1

15:59:20.13 Queue=0 Destination=4 Errored=1

And if you run all of that SQL, and are done playing with it, here's the easy clean-up:

DROP TABLE [LogDestination]
DROP PROCEDURE [usp_SlowProcedure]
DROP TABLE [AsyncErrored]
DROP PROCEDURE [usp_AsyncExecActivated]
DROP SERVICE [AsyncExecService]
DROP PROCEDURE [usp_SlowProcedureAsync]

DROP QUEUE [AsyncExecQueue]

I hope someone eventually finds this useful; but if not, I will hopefully remember that I put this out here =-]

Wednesday, July 29, 2015

The below is a simple demonstration of some awesome javascript code.
It will compare two sets of text, generating the differences in a nice highlighted way, The output looks like:

...I will give you a complete account a full explanation of the system...



<style>
div { border: 1px solid #000; }
del { color: #f00; text-decoration: line-through; }
ins { color: #070; text-decoration: underline; }
</style>
Old: <textarea id="older" rows="10" cols="40">The below content is the translation of the "Lorem Ipsum" text.

But I must explain to you how all this mistaken idea of denouncing pleasure and praising pain was born and I will give you a complete account of the system, and expound the actual teachings of the great explorer of the truth, the master-builder of human happiness. No one rejects, dislikes, or avoids pleasure itself, because it is pleasure, but because those who do not know how to pursue pleasure rationally encounter consequences that are extremely painful. Nor again is there anyone who loves or pursues or desires to obtain pain of itself, because it is pain, but because occasionally circumstances occur in which toil and pain can procure him some great pleasure. To take a trivial example, which of us ever undertakes laborious physical exercise, except to obtain some advantage from it? But who has any right to find fault with a man who chooses to enjoy a pleasure that has no annoying consequences, or one who avoids a pain that produces no resultant pleasure?</textarea>
New: <textarea id="newer" rows="10" cols="40">The below content is the translation of the "Lorem Ipsum" text, run through google translate from English to Spanish to English.

But I must explain to you how was all this mistaken idea of ??denouncing pleasure and praising pain and I will give a full explanation of the system, and expound the actual teachings of the great explorer of the truth, the master-builder of human happiness. No one rejects, dislikes, or avoids pleasure itself, because it is pleasure, but because those who do not know how to pursue pleasure rationally encounter consequences that are extremely painful. Nor is there anyone who loves or pursues or wishes to obtain pain of itself, because it is pain, but because occasionally circumstances in which fatigue and pain you can purchase a great pleasure occur. To take a trivial example, which of us undertakes laborious physical exercise, except to obtain some advantage from it? But who has the right to criticize a man who chooses to enjoy a pleasure that has no annoying consequences, or one who avoids a pain that produces no resultant pleasure?</textarea>
<br/>
<button onclick="compare();">Compare</button>
<div style="white-space: pre-wrap;" id="differences" rows="10" cols="40" />
<script>
String.prototype.replaceAll = function (find, replace) { return this.replace(new RegExp(find, 'g'), replace); }
function compare() {
  document.getElementById('differences').innerHTML = diffString(document.getElementById('older').value, document.getElementById('newer').value);
}

function splitString(s) {
  return s.split(/\s+/);
}

function diffString(o, n) {
    var escapeEntities = function(s) { return s.replace(/&/g, "&amp;").replace(/</g, "&lt;").replace(/>/g, "&gt;").replace(/"/g, "&quot;"); }
    o = escapeEntities(o);
    n = escapeEntities(n);
    o = o.replace(/\s+$/, '');
    n = n.replace(/\s+$/, '');
    var out = diff(o == '' ? [] : splitString(o), n == '' ? [] : splitString(n));
    var str = '';
    var oSpace = o.match(/\s+/g);
    if (oSpace == null) {
        oSpace = ['\n'];
    } else {
        oSpace.push('\n');
    }
    var nSpace = n.match(/\s+/g);
    if (nSpace == null) {
        nSpace = ['\n'];
    } else {
        nSpace.push('\n');
    }
    if (out.n.length == 0) {
        for (var i = 0; i < out.o.length; i++) {
            str += '<del>' + out.o[i] + oSpace[i] + '</del>';
        }
    } else {
        if (out.n[0].text == null) {
            for (n = 0; n < out.o.length && out.o[n].text == null; n++) {
                str += '<del>' + out.o[n] + oSpace[n] + '</del>';
            }
        }
        for (var i = 0; i < out.n.length; i++) {
            if (out.n[i].text == null) {
                str += '<ins>' + out.n[i] + nSpace[i] + '</ins>';
            } else {
                var pre = '';
                for (n = out.n[i].row + 1; n < out.o.length && out.o[n].text == null; n++) {
                    pre += '<del>' + out.o[n] + oSpace[n] + '</del>';
                }
                str += ' ' + out.n[i].text + nSpace[i] + pre;
            }
        }
    }
    return str;
}

function diff(o, n) {
    var ns = new Object();
    var os = new Object();
    for (var i = 0; i < n.length; i++) {
        if (ns[n[i]] == null)
            ns[n[i]] = { rows: new Array(), o: null };
            ns[n[i]].rows.push(i);
    }
    for (var i = 0; i < o.length; i++) {
        if (os[o[i]] == null)
            os[o[i]] = { rows: new Array(), n: null };
            os[o[i]].rows.push(i);
    }
    for (var i in ns) {
      if (ns[i].rows.length == 1 && typeof (os[i]) != "undefined" && os[i].rows.length == 1) {
          n[ns[i].rows[0]] = { text: n[ns[i].rows[0]], row: os[i].rows[0] };
          o[os[i].rows[0]] = { text: o[os[i].rows[0]], row: ns[i].rows[0] };
      }
    }
    for (var i = 0; i < n.length - 1; i++) {
        if (n[i].text != null && n[i + 1].text == null && n[i].row + 1 < o.length && o[n[i].row + 1].text == null && n[i + 1] == o[n[i].row + 1]) {
            n[i + 1] = { text: n[i + 1], row: n[i].row + 1 };
            o[n[i].row + 1] = { text: o[n[i].row + 1], row: i + 1 };
        }
    }
    for (var i = n.length - 1; i > 0; i--) {
        if (n[i].text != null && n[i - 1].text == null && n[i].row > 0 && o[n[i].row - 1].text == null && n[i - 1] == o[n[i].row - 1]) {
            n[i - 1] = { text: n[i - 1], row: n[i].row - 1 };
            o[n[i].row - 1] = { text: o[n[i].row - 1], row: i - 1 };
        }
    }
    return { o: o, n: n };
}

</script>

Thursday, December 18, 2014

Coder's blog, earthdate 20111223.7

If I can formulate a set of symbols to visually convey all the elements of programming, it can be turned into a puzzle game. Levels/solutions can be saved as code files, in the preferred unicde format. If the game becomes popular, it becomes a forum for discussing the future of coding wth full symbols.