Thursday, July 12, 2012

Using SqlDependency without providing an options value Error

Using SqlDependency without providing an options value 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.
Using SqlDependency without providing an options value Exception.

SqlDependency is awesome.  It truly is.  Problem is it has a lot of unexpected behaviors to an SqlDependency noob. 

I got this 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" couple of hours ago and I decided to share.

This error occurs if you didn't started your SqlDependency or it got closed for some reason.  I usually start it this way in the Global.asax:
    
 protected void Application_Start(Object sender, EventArgs e)
 {
   var sConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
   SqlDependency.Start(sConn);
 
   // Cache systemwide data in the application object.
   ApplicationAdapter.InitApplicationCache();
 }

You must pay attention to errors though.  Since it might fall while running, always have a check on the exception type at the Application_Error, so you'll know to restart the SqlDependency in case it stops.

Hope it helped,
Elad Shalom,
CTO at ITweetLive.com