RECEIVE in transactions

3 May 2007 8:55 PM

Ola Hallengren

Hello!

I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.

CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @AuditMessage TABLE (EventData xml);

RECEIVE    CAST(message_body AS XML)
FROM AuditQueue
INTO @AuditMessage

INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECT    CAST(EventData.query('data(//PostTime)') AS varchar(23)),
        CAST(EventData.query('data(//EventType)') AS sysname),
        CAST(EventData.query('data(//LoginName)') AS sysname),
        CAST(EventData.query('data(//UserName)') AS sysname),
        CAST(EventData.query('data(//ServerName)') AS sysname),
        CAST(EventData.query('data(//DatabaseName)') AS sysname),
        CAST(EventData.query('data(//ObjectType)') AS sysname),
        CAST(EventData.query('data(//SchemaName)') AS sysname),
        CAST(EventData.query('data(//ObjectName)') AS sysname),
        EventData
FROM @AuditMessage

It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?

Best regards

Ola Hallengren

4 May 2007 6:37 AM

Gilberto Zampatti

the books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti

Show quoteHide quote

"Ola Hallengren" wrote:

> Hello!
>
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
>
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @AuditMessage TABLE (EventData xml);
>
> RECEIVE    CAST(message_body AS XML)
> FROM AuditQueue
> INTO @AuditMessage
>
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT    CAST(EventData.query('data(//PostTime)') AS varchar(23)),
>         CAST(EventData.query('data(//EventType)') AS sysname),
>         CAST(EventData.query('data(//LoginName)') AS sysname),
>         CAST(EventData.query('data(//UserName)') AS sysname),
>         CAST(EventData.query('data(//ServerName)') AS sysname),
>         CAST(EventData.query('data(//DatabaseName)') AS sysname),
>         CAST(EventData.query('data(//ObjectType)') AS sysname),
>         CAST(EventData.query('data(//SchemaName)') AS sysname),
>         CAST(EventData.query('data(//ObjectName)') AS sysname),
>         EventData
> FROM @AuditMessage
>
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
>
> Best regards
>
> Ola Hallengren

4 May 2007 6:54 AM

Tibor Karaszi

> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.

In what way doesn't it work? Error messages? Something else?

> Is it
> not possible to use RECEIVE in transactions?

Yes, but you want to be careful. Say you determine that you can't process this message so you
rollback. Since messages are received in order, you will just read the same message next time.
Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
topic is handled in chapter 8.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quoteHide quote

"Ola Hallengren" <OlaHalleng***@discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@microsoft.com...
> Hello!
>
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
>
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @AuditMessage TABLE (EventData xml);
>
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @AuditMessage
>
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @AuditMessage
>
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
>
> Best regards
>
> Ola Hallengren

4 May 2007 1:35 PM

Ola Hallengren

I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.

About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.

Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.

I have ordered the Roger Walters book today.

Thanks for you help.

/Ola

Show quoteHide quote

"Tibor Karaszi" wrote:

> > I would like to have the
> > RECEIVE and the INSERT as a transaction, but I can't get it working.
>
> In what way doesn't it work? Error messages? Something else?
>
>
> > Is it
> > not possible to use RECEIVE in transactions?
>
> Yes, but you want to be careful. Say you determine that you can't process this message so you
> rollback. Since messages are received in order, you will just read the same message next time.
> Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Ola Hallengren" <OlaHalleng***@discussions.microsoft.com> wrote in message
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@microsoft.com...
> > Hello!
> >
> > I have an an event notifications audit solution that I'm working on. I am
> > using this activation procedure.
> >
> > CREATE PROCEDURE dbo.AuditReceive
> > AS
> > DECLARE @AuditMessage TABLE (EventData xml);
> >
> > RECEIVE CAST(message_body AS XML)
> > FROM AuditQueue
> > INTO @AuditMessage
> >
> > INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> > DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> > SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> > CAST(EventData.query('data(//EventType)') AS sysname),
> > CAST(EventData.query('data(//LoginName)') AS sysname),
> > CAST(EventData.query('data(//UserName)') AS sysname),
> > CAST(EventData.query('data(//ServerName)') AS sysname),
> > CAST(EventData.query('data(//DatabaseName)') AS sysname),
> > CAST(EventData.query('data(//ObjectType)') AS sysname),
> > CAST(EventData.query('data(//SchemaName)') AS sysname),
> > CAST(EventData.query('data(//ObjectName)') AS sysname),
> > EventData
> > FROM @AuditMessage
> >
> > It's working nicely, but there is one weakness. I would like to have the
> > RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> > not possible to use RECEIVE in transactions?
> >
> > Best regards
> >
> > Ola Hallengren
>
>