CODEWITHSUNDEEP
c#sqlremote-serversqldependencyquery-notifications
Cogent
Cogent

Reputation: 402

Network SQL Database SQL Dependency on C# App Issue

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!");

Upvotes: 0

Views: 640

Answers (1)

Cogent
Cogent

Reputation: 402

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.

Upvotes: 1

Related Questions