none
Sort_warning notification

    Question

  • I would like to call procedure(which is empty for test) when sort warning is happened. I have enabled service broker, created a queue to receive messages, created a service on the queue that references the event notifications contract, and finally created an event notification. But when sort warning happens(I can see it in profiler), there is no message in queue and SP is not invoked. Have I missed something?

    br, Simon

    CREATE QUEUE dbo.SortWarningsQueue
    WITH STATUS=ON,	
    ACTIVATION 
    			(
    				PROCEDURE_NAME = dbo.process_sort_warning_event,
    				MAX_QUEUE_READERS = 5,
    				EXECUTE AS OWNER
    			);
    
    CREATE SERVICE SortWarningsService
    ON QUEUE dbo.SortWarningsQueue
    (
        [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
    );
    GO
    
    --Create the database event notification
    CREATE EVENT NOTIFICATION NotifySort_Warnings
    ON SERVER
    FOR SORT_WARNINGS
    TO SERVICE 'SortWarningsService','current database';
    GO


    Tuesday, August 06, 2013 12:59 PM

Answers

  • An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    What that means? I'm sys.admin and also dbo exists as user on current database?

    The most likely cause is a bad database owner, which results in the dbo database user not being mapped to a valid account.  You should be able to fix this by changing the database owner to a login that exists.  For example:

    ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa;
    

    See http://www.dbdelta.com/database-owner-troubles/ for more info on database ownership.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by simonxy Friday, August 09, 2013 7:31 AM
    Friday, August 09, 2013 12:13 AM

All replies

  • I tried your script and the expected events were written to the queue.  Have you enabled Service Broker in the database?

    ALTER DATABASE MyDatabase
    	SET ENABLE_BROKER;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, August 07, 2013 2:27 AM
  • Yes, I have enabled service broker. In fact I'm using it for asynch stored procedure calls. 

    I thought that SORT_WARNING is not fired somehow, but also other SERVER OR DATABASE notifications doesn't work (ALTER_TABLE for example).

    It looks to me that only notifications doesn't work. Is there some other setting for notifications?

    Wednesday, August 07, 2013 7:17 AM
  • It looks to me that only notifications doesn't work. Is there some other setting for notifications?

    No special setting I can think of.  The initiator DB objects for event notifications are pre-defined. 

    Have you checked the SQL Server error log?  Perhaps the message is being consumed by the activated proc but a subsequent error occurred that prevented it from being processed.  For debugging, you could also turn on queue message retention.  As a sanity check, also check sys.server_event_notifications, sys.transmission_queue and sys.conversation_endpoints to make sure all looks healthy.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, August 07, 2013 10:13 AM
  • sys.server_event_notifications is there. sys.transmission_queue is empty.

    Also sys.conversation_endpoints is empty on this database.

    But there is error log:

    An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    What that means? I'm sys.admin and also dbo exists as user on current database?

    br, Simon

    Thursday, August 08, 2013 2:52 PM
  • An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    What that means? I'm sys.admin and also dbo exists as user on current database?

    The most likely cause is a bad database owner, which results in the dbo database user not being mapped to a valid account.  You should be able to fix this by changing the database owner to a login that exists.  For example:

    ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa;
    

    See http://www.dbdelta.com/database-owner-troubles/ for more info on database ownership.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by simonxy Friday, August 09, 2013 7:31 AM
    Friday, August 09, 2013 12:13 AM
  • Thank you. It works now.
    Bad database owner was the reason.

    br, Simon

    Friday, August 09, 2013 7:31 AM