0

I have used query notification on my c# application successfully using local database. However, changing the connection string to my actual database (not local) results in an error. Note that both databases are identical + queues and services etc. are identical on both databases. All I did was swap the connection strings ==> resulting in failure to create queue subscription. If there is something I am missing regarding Local vs Remote DB dependencies please advise.

The part of Code Raising the Exception: 

private void Handle_OnChange(object sender, SqlNotificationEventArgs e)
    {

        if (e.Type != SqlNotificationType.Change)
            throw new ApplicationException("Failed to create queue notification subscription!");
Cogent
  • 404
  • 7
  • 16
  • 1
    "results in an error" ... can you provide more information about the error? – Adrian Wragg May 12 '14 at 08:47
  • most likely an authentication or network issue if everything else is identical - most programmers here aren't psychics so really need to know the actual error message rather than just "results in an error" to help you :) – Steve May 12 '14 at 08:50
  • I have Implemented Jeremiah Gowdy's SQLWatcher Class. http://jgowdy.blogspot.com/2010/01/sqldependency-query-notification-with.html. The error Message originates form this code : if (e.Type != SqlNotificationType.Change) throw new ApplicationException("Failed to create queue notification subscription!"); //This code is is part of the OnChange event handler for the WatcherClass. – Cogent May 12 '14 at 08:59
  • So what have you tried to resolve the error? What is `e.Type` when the exception happens? – usr May 12 '14 at 09:53
  • e.type is either Subscription, Notification, or Other. OnChange is raised when notification event occurs. However, when i changed my connectionstring it is passing Subscription Type, hence, raising the error. – Cogent May 12 '14 at 09:59
  • There must be a misunderstanding. I'm interested to see what `e` and `e.Type` contain at runtime (unexpectedly). Maybe these objects can tell you what the error is. See http://msdn.microsoft.com/en-us/library/z0fkxc6y(v=vs.110).aspx – usr May 12 '14 at 10:13
  • i have kind of the same issue, every thing work fine locally, but if the data server and web server are separate, it won't work – Hassan Faghihi Jan 23 '16 at 08:14

1 Answers1

1

After spending the whole day trying to resolve the issue, I figured out what caused it. After switching through all SqlNotificationEventArgs.Info raised on the OnChange Event, I noticed an SqlNotificationInfo.Options response, which indicated that SQL server “Options Set” was not correct since it was running in SQL server 2000(80) compatibility level! Options Set should have been as follows:

ANSI_NULLS ON
ANSI_PADDING ON
ANSI_WARNINGS ON
CONCAT_NULL_YIELDS_NULL ON
QUOTED_IDENTIFIER ON
NUMERIC_ROUNDABORT OFF
ARITHABORT ON

Sqldependency worked as expected after setting these options.

Cogent
  • 404
  • 7
  • 16