Problem with Dependencies and the error "Cannot find the remote service"

Hello all.

We are a small taskforce currently working on a system of which utilizes the SQL notification service. For this we are using dependencies provided by SqlDependency. The dependency is set up as follows:

//Where dbo.MTable is the table and guid is the unique key of the records. We //want to listen to individual records and not the entire table

SqlCommand listenCommand = new SqlCommand( "SELECT ID FROM dbo.MTable WHERE ID = " + "@.guid", connection );

listenCommand.Parameters.AddWithValue( "@.guid", guid );

depend = new SqlDependency( listenCommand, null, 0 );

depend.OnChange += onChangeEventHandler;

If you’re not so bold as to look in the database server log, and you do not care about log growth of cataclysmic proportions which will inevitably crash the server, it will work fine. The problem is, as hinted above, that the server log is filled with error messages.

Error Code: 8490

Description: “Cannot find the remote service : because it does not exist.”

Here is an example of before mentioned messages:

The query notification dialog on conversation handle '{F53C37DE-E66A-DA11-90A1-00105AC00126}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-cc3933a7-f97f-45b4-822f-f326f9f6e373&apos; because it does not exist.</Description></Error>'.

We have realized that listening on an entire table does not provoke this error. Furthermore, experiments have shown that the error only appears after we have shut down our system.

On shutdown, we call the following method:

SqlDependency.Stop( connection.ConnectionString );

This seems to stop the database from sending out notifications when we listen to an entire table, but not when we listen to individual records. Are we using the service in an incorrect manner or is there an error in the Stop method?

We have discovered a hotfix, but it is not exactly a beautiful solution:

SqlDependency.Start( connection.ConnectionString );

SqlCommand listenCommand = new SqlCommand( "SELECT * FROM MTable", connection );

depend = new SqlDependency( listenCommand );

SqlDependency.Stop( m_ConnectionString );

We have found that by ‘restarting’ the SqlDependency and giving it a new dependency where it listens to the entire table and THEN stopping it, the error does not appear. Our theory is that the new start and dependency overwrites the old ones.

I believe you have posted this issue to the wrong forum. SqlDependency is not a part of the Notification Services, despite the similar name. You might try a SQL 2005 forum instead.

Problem with Dependencies and Default Values

Dear Anyone,

Im having trouble with RS2005 with regards to dependencies of report parameters and the default value. Below is my scenario:

1st parameter:

List of Letters from A to Z

Multiple Select

2nd Parameter:

List of Names starting with the last name

Uses 1 data set for list of values and default values

Multiple Select

Steps:

Select 2 letters from the 1st parameter. This action would refresh the list of values of the 2nd parameter.

Inspect the 2nd parameter. You will notice that all values are selected.

Select another letter from the first parameter. This action would refresh the list of values of the 2nd parameter.

Inspect the 2nd parameter. You will notice that not all is selected despite the fact that the data set being used to populate the report parameter is the same as with the default values

Is the behavior of the report parameters really like this? If so, how can I make it in a way that whenever I select a new letter from the first parameter and when the 2nd parameter gets refreshed because of the depencies, all values will still be selected despite the number of times I add more values to the first parameter?

Thanks,

Joseph

Is there a resolution to this issue? How do we overcome this behavior? I am running into the same issue in my reports.

apex|||Bumping up this thread. Any resolution to this problem will be very helpful.

Problem with Dependencies and Default Values

Dear Anyone,

Im having trouble with RS2005 with regards to dependencies of report parameters and the default value. Below is my scenario:

1st parameter:

List of Letters from A to Z

Multiple Select

2nd Parameter:

List of Names starting with the last name

Uses 1 data set for list of values and default values

Multiple Select

Steps:

Select 2 letters from the 1st parameter. This action would refresh the list of values of the 2nd parameter.

Inspect the 2nd parameter. You will notice that all values are selected.

Select another letter from the first parameter. This action would refresh the list of values of the 2nd parameter.

Inspect the 2nd parameter. You will notice that not all is selected despite the fact that the data set being used to populate the report parameter is the same as with the default values

Is the behavior of the report parameters really like this? If so, how can I make it in a way that whenever I select a new letter from the first parameter and when the 2nd parameter gets refreshed because of the depencies, all values will still be selected despite the number of times I add more values to the first parameter?

Thanks,

Joseph

Is there a resolution to this issue? How do we overcome this behavior? I am running into the same issue in my reports.

apex|||Bumping up this thread. Any resolution to this problem will be very helpful.