I implemented the event notification fo rlogin as in Frederick's article.

It work fine.

Now I want to only insert rows for certain loginname's.

I made another stored procedure with an IF statement in the stored proc that only inserts if the loginname is not in a list that I provide. If it is, I roll back and break.

I alter queue with status = off for the old stored proc, then alter queue with status = on fo rthe new proc.

What happens is I get one row that stays in the queue when I let someone log in that should be INSERTED into the table and no further INSERTS occur.

This is the new stored proc:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[proc_log_user_logins_new]

AS

SET NOCOUNT ON;

DECLARE @message_body XML,

@message_type_name NVARCHAR(256),

@dialog UNIQUEIDENTIFIER ;

--Endless loop

WHILE (1 = 1)

BEGIN

BEGIN TRANSACTION ;

-- Receive the next available message

WAITFOR (

RECEIVE TOP(1)

@message_type_name=message_type_name,

@message_body=message_body,

@dialog = conversation_handle

FROM log_user_logins_queue

), TIMEOUT 2000

--Rollback and exit if no messages were found

IF (@@ROWCOUNT = 0)

BEGIN

ROLLBACK TRANSACTION ;

BREAK ;

END ;

--End conversation of end dialog message

IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')

BEGIN

PRINT 'End Dialog received for dialog # ' + cast(@dialog as nvarchar(40)) ;

END CONVERSATION @dialog ;

END ;

ELSE

IF CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100))

NOT IN ('m58467','ITSERVICES\M10077','patrol_ssuser','patrol_ssadmin')

BEGIN

ROLLBACK TRANSACTION ;

BREAK ;

END ;

BEGIN

INSERT INTO log_user_logins (

EventTime,

EventType,

LoginName,

HostName,

NTUserName,

NTDomainName,

Success,

FullLog)

VALUES

(

CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),

CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@message_body.query('/EVENT_INSTANCE/HostName/text()') AS VARCHAR(100)),

CAST(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') AS VARCHAR(100)),

CAST(@message_body.query('/EVENT_INSTANCE/NTDomainName/text()') AS VARCHAR(100)),

CAST(CAST(@message_body.query('/EVENT_INSTANCE/Success/text()') AS VARCHAR(64)) AS INTEGER),

@message_body)

END

COMMIT TRANSACTION

END

Any help would be greatly appreciated.

I am not sure how to properly code rows I receive , but do not want to insert or keep them in the queue.

The row in the queue:

select * from log_user_logins_queue

1013D19B6C5A-C927-DF11-9A25-001A64C552F2D29B6C5A-C927-DF11-9A25-001A64C552F26log_user_logins_service65539http://schemas.microsoft.com/SQL/Notifications/PostEventNotification2http://schemas.microsoft.com/SQL/Notifications/EventNotification4X0xFFFE3C004500560045004E0054005F0049004E005300540041004E00430045003E003C004500760065006E00740054007900700065003E00410055004400490054005F004C004F00470049004E003C002F004500760065006E00740054007900700065003E003C0050006F0073007400540069006D0065003E0032003000310030002D00300033002D00300034005400310032003A00330032003A00310032002E003100380030003C002F0050006F0073007400540069006D0065003E003C0053005000490044003E00360032003C002F0053005000490044003E003C00540065007800740044006100740061003E002D002D0020006E006500740077006F0072006B002000700072006F0074006F0063006F006C003A0020005400430050002F0049005000260023007800300044003B000A007300650074002000710075006F007400650064005F006900640065006E0074006900660069006500720020006F006E00260023007800300044003B000A00730065007400200061007200690074006800610062006F007200740020006F0066006600260023007800300044003B000A0073006500740020006E0075006D0065007200690063005F0072006F0075006E006400610062006F007200740020006F0066006600260023007800300044003B000A00730065007400200061006E00730069005F007700610072006E0069006E006700730020006F006E00260023007800300044003B000A00730065007400200061006E00730069005F00700061006400640069006E00670020006F006E00260023007800300044003B000A00730065007400200061006E00730069005F006E0075006C006C00730020006F006E00260023007800300044003B000A00730065007400200063006F006E006300610074005F006E0075006C006C005F007900690065006C00640073005F006E0075006C006C0020006F006E00260023007800300044003B000A00730065007400200063007500720073006F0072005F0063006C006F00730065005F006F006E005F0063006F006D006D006900740020006F0066006600260023007800300044003B000A00730065007400200069006D0070006C0069006300690074005F007400720061006E00730061006300740069006F006E00730020006F0066006600260023007800300044003B000A0073006500740020006C0061006E00670075006100670065002000750073005F0065006E0067006C00690073006800260023007800300044003B000A007300650074002000640061007400650066006F0072006D006100740020006D0064007900260023007800300044003B000A00730065007400200064006100740065006600690072007300740020003700260023007800300044003B000A0073006500740020007400720061006E00730061006300740069006F006E002000690073006F006C006100740069006F006E0020006C006500760065006C0020007200650061006400200063006F006D006D0069007400740065006400260023007800300044003B000A003C002F00540065007800740044006100740061003E003C00420069006E0061007200790044006100740061003E0049004100410041004B0044006A003000410051004100410041004100410041003C002F00420069006E0061007200790044006100740061003E003C0044006100740061006200610073006500490044003E0036003C002F0044006100740061006200610073006500490044003E003C004E00540055007300650072004E0061006D0065003E007300670036003100350038003C002F004E00540055007300650072004E0061006D0065003E003C004E00540044006F006D00610069006E004E0061006D0065003E00490053004F003C002F004E00540044006F006D00610069006E004E0061006D0065003E003C0048006F00730074004E0061006D0065003E004E004A004300440054004C00300031005300470036003100350038003C002F0048006F00730074004E0061006D0065003E003C0043006C00690065006E007400500072006F006300650073007300490044003E0032003900340034003C002F0043006C00690065006E007400500072006F006300650073007300490044003E003C004100700070006C00690063006100740069006F006E004E0061006D0065003E004D006900630072006F0073006F00660074002000530051004C00200053006500720076006500720020004D0061006E006100670065006D0065006E0074002000530074007500640069006F0020002D002000510075006500720079003C002F004100700070006C00690063006100740069006F006E004E0061006D0065003E003C004C006F00670069006E004E0061006D0065003E00490053004F005C007300670036003100350038003C002F004C006F00670069006E004E0061006D0065003E003C0053007400610072007400540069006D0065003E0032003000310030002D00300033002D00300034005400310032003A00330032003A00310032002E003100370037003C002F0053007400610072007400540069006D0065003E003C004500760065006E00740053007500620043006C006100730073003E0031003C002F004500760065006E00740053007500620043006C006100730073003E003C0053007500630063006500730073003E0031003C002F0053007500630063006500730073003E003C0049006E007400650067006500720044006100740061003E0034003000390036003C002F0049006E007400650067006500720044006100740061003E003C005300650072007600650072004E0061006D0065003E004300410048005900570052003100430041005000490054004C00300036003C002F005300650072007600650072004E0061006D0065003E003C00440061007400610062006100730065004E0061006D0065002F003E003C004C006F00670069006E005300690064003E0041005100550041004100410041004100410041005500560041004100410041005700540048004C0064003400390051003400330043007A005A005100310058003200690051004100410041003D003D003C002F004C006F00670069006E005300690064003E003C005200650071007500650073007400490044003E0030003C002F005200650071007500650073007400490044003E003C004500760065006E007400530065007100750065006E00630065003E003200370036003000320035003C002F004500760065006E007400530065007100750065006E00630065003E003C0049007300530079007300740065006D002F003E003C00530065007300730069006F006E004C006F00670069006E004E0061006D0065002F003E003C002F004500560045004E0054005F0049004E005300540041004E00430045003E00

I think the problem is what is stated in books online for receive statement:

"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."

my retention is the default (off).