The conversation handle "1FBDE14B-0A07-EB11-B81C-3CECEF20BFDC" is not found

Question

I have used SQL Service broker and SQL Table Dependency and started SQL table dependency in a table for notifications on table data change. I have given all the permission to the database listed in the SQL table Dependency document. After some time, maybe in idle state, it is giving status as "Waiting for notification.

I am getting this error.

When I change in the table (inserting new record), status is not changing (From waiting for notification) and gives error as

ErrorSystem.Data.SqlClient.SqlException (0x80131904): The conversation handle "1FBDE14B-0A07-EB11-B81C-3CECEF20BFDC" is not found.
    An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

Here is my Code:

_sqlTableDependencyOrders = new SqlTableDependency<OrderTrigger>(DBConnection.GetOnlineConnectionString(), "Order");
            _sqlTableDependencyOrders.OnChanged += OnDatabaseEventChanged;
            _sqlTableDependencyOrders.Start();

Answer 1

The error message indicates that the SQL Service Broker conversation handle could not be identified. This can happen for a variety of reasons, and fixing this issue may need several procedures. Here are some steps you may take to help identify and perhaps address the issue:

Make sure SQL Service Broker is turned on: First, ensure that SQL Service Broker is enabled on the database containing the table you are monitoring. This can be verified using the SQL query:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

If it's not enabled, you can enable it using:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Examine the Conversation Endpoints: Check that the chat endpoints are properly set and operational. The following SQL query may be used to check the endpoints:

SELECT * FROM sys.conversation_endpoints;

If there are any difficulties or inactive endpoints, you may need to evaluate the Service Broker setup and maybe rebuild the endpoint.

Service Broker Is Up and Running: Check that the SQL Service Broker is active. You can use the following to determine its current status:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

If it's not running, you can start it using:

ALTER DATABASE YourDatabaseName SET NEW_BROKER;