CODEWITHSUNDEEP
c#sql-serverservice-broker
Shahid Iqbal
Shahid Iqbal

Reputation: 2135

Error : When Using SqlDependency Without Providing an Options Value

I am working on query notification. I have created Queue, Service and Route in my Database. I am accessing my Database on Network by IP Address, so i created a Route in Sql Server like this..

ALTER ROUTE [NotificationMessagesRoute]   
WITH  SERVICE_NAME  = N'MyNotificationService' ,  
ADDRESS  = N'TCP://myIPAddress:PortNo' --

on FrontEnd it have used SqlDependency like this..

SqlDependency dependency = new SqlDependency(command), "Service=MyNotificationService;local database=Test", int.MaxValue);

Its working Perfectly, if i am using Local Connection String and also Route in Sql Server as Local, but if i access DB on my organizations local Network, it do not show me notification on new Insertion.

I have also used SqlDependency object as below, but it gives me below Error.

 SqlDependency dependency = new SqlDependency(command);

Error : When using SqlDependency without Providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.

Plz Help...

Upvotes: 5

Views: 16305

Answers (3)

Happy Life
Happy Life

Reputation: 51

You need to set ENABLE_BROKER for the database by running the query below:

ALTER DATABASE [your_database_name] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Upvotes: 5

sankara pandian
sankara pandian

Reputation: 385

Add start the dependency in Startup class

System.Data.SqlClient.SqlDependency.Start(ConnectionString);

Upvotes: 7

Remus Rusanu
Remus Rusanu

Reputation: 294307

SqlDependency works only locally, because it goes ahead and deploys target service, queue and activated procedure for you. If you want to deploy in a remote scenario you will need use the more basic SqlNotificationRequest. You will have to deploy the required destination service yourself, in app. See Using SqlNotificationRequest to Subscribe to Query Notifications.

I hope you do understand that a remote route expects at its destination another SQL Server instance, not your app, and you must properly set up endpoint and dialog security, see Service Broker Communication Protocols. It is very unlikely that you need a remote route for your scenario.

Upvotes: 1

Related Questions