not detecting any change in table when deployed on IIS 8 windows server

[umarsolehri]

not detecting any change in table when deployed on IIS 8 windows server, I made dependencies on two tables but none of them is working, but working perfectly on localhost

[christiandelbianco]

Hi. Deployment is done using a backup database?

[umarsolehri]

Backup database?

[umarsolehri]

I created database in sql server in windows server, everything is running fine but not detecting any change

[christiandelbianco]

Ok. After runnig your application, can you see SqlTableDependency database objects? For example a new trigger on your database table?

[christiandelbianco]

Also, which sqlserver version are you usung?

[umarsolehri]

private string ConnectionString { get { return System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; } }
private readonly static Lazy _instance = new Lazy(
() => new CommenHubService(GlobalHost.ConnectionManager.GetHubContext().Clients));

    private static SqlTableDependency<CommentModel> _tableDependency;
    public static CommenHubService Instance { get { try { return _instance.Value; } catch { return null; } } }
    private IHubConnectionContext<dynamic> Clients { get; set; }

    public CommenHubService(IHubConnectionContext<dynamic> clients)
    {
        Clients = clients;

        var mapper = new ModelToTableMapper<CommentModel>();
        mapper.AddMapping(e => e.ContentId, "ContentId");
        mapper.AddMapping(e => e.CommentId, "CommentId");
        mapper.AddMapping(e => e.Comment, "Comment");

        _tableDependency = new SqlTableDependency<CommentModel>(ConnectionString, "Comments", mapper);

        _tableDependency.OnChanged += SqlTableDependency_Changed;

        _tableDependency.Start();
    }

[umarsolehri]

sql 2014 enterprise

[christiandelbianco]

ok...your code is fine.
When you run your application on local host, i suppose you are using a different DB.
In this case, can you try to change the connection string of your local application, pointing to deployed DB?

I ask that, because can be that DB you are using was created from a old SQL Server instance (for example 2008). Even if now it is under SQL 2014 enterprise, it conserve settings that make it not compatible with SqlTableDependency.

[christiandelbianco]

If you run this SQL script on DB (the one used by deployed on IIS 8 windows server)

SELECT DatabaseProperty('the database name', 'version')
GO

what is the result you get?

[christiandelbianco]

Also, can you run these two scripts?

SELECT name, compatibility_level from sys.databases WHERE name = 'the database name'
GO

SELECT name, version from master.dbo.sysdatabases where name = 'the database name'
GO

[umarsolehri]

[christiandelbianco]

ok...last thing.
Can you make a backup of your database, removing all tables/stored procedures not involved in SqlTableDependency issue. Drop all record in monitored table. Then...can you send me?
Because i need as scenario to reproduce the issue you are facing. In all my tests I never had someting similar.

[umarsolehri]

thank you for your cooperation
I also searched a lot and my issue solved by just small changing in connection string
<add name="DefaultConnection" connectionString="Data Source=serverName;Initial Catalog=KCMSDb;User Id = username ; Password = password;Integrated Security=True" providerName="System.Data.SqlClient" />

to

<add name="DefaultConnection" connectionString="Data Source=serverName;Initial Catalog=KCMSDb;User Id = username ; Password = password;" providerName="System.Data.SqlClient" />

or in simple by just removing "Integrated Security=True" from my connection string

[gandarez]

It seems to not work with Integrated Authentication