Error : When Using SqlDependency Without Providing an Options Value

c# sql-server service-broker
Shahid Iqbal picture Shahid Iqbal · Oct 3, 2013 · Viewed 12.8k times · Source

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

Answer

sankara pandian picture sankara pandian · Sep 26, 2016

Add start the dependency in Startup class

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

Related questions

How to use SqlCacheDependency?

I need to implement SqlCacheDependency for a table which will depend on this query: SELECT Nickname FROM dbo.[User]. I have created a method for this purpose: private IEnumerable<string> GetNicknamesFromCache() { const String cacheValueName = "Nicknames"; var result = HttpRuntime.…

Read More
How to execute a stored procedure within C# program

I want to execute this stored procedure from a C# program. I have written the following stored procedure in a SqlServer query window and saved it as stored1: use master go create procedure dbo.test as DECLARE @command as varchar(1000), @…

Read More
How can I set an SQL Server connection string?

I'm developing a simple C# application, and I'd like to know this: When I connect my application to SQL Server on my PC, I know the connection string (server name, password, etc.), but when I connect it to another PC, …

Read More