none
SQL Dependency in SQL Server 2012

    Question

  • Hi,

    I have programs that use SQL dependency mechanism to  update UI. Works great on SQL Server 2008. When I run the same code on SQL Server 2012 which, I think, was configured similarly (at least when it comes to parameteres relevant to SQL Dependecy), data updates no longer work.

    Does anybody know is there anything new in SQL Server 2012 tha one needs to configure to have SQL dependencies working?

    Thanks in advance.

    Peter Battelino

    BTE Corporation


    PMBT

    Wednesday, April 25, 2012 11:51 AM

Answers

  • Joe,

    Thanks again for your response. I was bogged down on other things, but I have also figured out what the problem was. Let me write down a brief explanation so other people do not have to go through the grief I went through.

    in our software there is a table with 'reference' records. Those reference records are montored for changes using QN, whenever a reference record gets changed, another (different table and ) record gets read and updated from the server. Don't ask me why this indirection, it is not relevant here.

    What happened is this: Each reference record contains columns A (primary key), B, and C. The latter is the time, and gets changed as mentioned above.

    My (software) clients would set up QN using column A in above records. In SQL server 2008, notification would 'fire' every time column C was updated, even though column 'A' was being 'watched' by clients. Not so in SQL Server 2012.

    This was a bug in our code - we should have been 'watching' column C, instead of A, in our clients. Our code worked in 2008, but not in 2012. I modified our code to watch column C, now it is all working fine once again.

    Don't know why this is so, the only explanation I have is that Micorsoft folks must have tightened up the logic in 2012 QN code. Maybe not?

    If anybody is interested in more details on this - I'll be glad to elaborate.

    In any case, thanks for your time once again.


    PMBT

    • Marked as answer by PMBT Friday, May 11, 2012 2:40 PM
    Friday, May 11, 2012 2:40 PM

All replies

  • Hi PMBT,

    Troubleshooting SQL Dependencies: http://msdn.microsoft.com/en-us/library/cc879246.aspx.

     

    Thanks,
    Maggie

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Thursday, April 26, 2012 3:07 AM
  • Maggie,

    Thank you for your response. I am familiar with the page you sent me, unfortunately it is not what I am looking for. This is my fault, I should have been more precise - the trouble I am having with are SQL dependency notifications: you (Client) sign up for dependency notifications for a specific query, and get notified by the server (the query returns) when records asked for in the query change.

    I have programs that run on SQL Server 2008 (notifications work), but do not work on 2112. I should also point out that I am runnign EXPRESS version on the server. Documentation explicitly says that Service Broker (on which dependency notifications are built upon) is not supported in EXPRESS version (documentation says 'Clients only'), so I don't know if this is a problem. Documentation says the same for 2008 and 2112 versions.

    I have developer's version of 2008, and I have ordered developer's version of 2112. As soon as I install it I will run the test and see if this is an issue.

    My settings on both server versions are the same, unless I am missing something...

    Thanks again.

    Peter Battelino

    BTE Corporation


    PMBT

    Thursday, April 26, 2012 9:53 AM
  • I think you're referring to Query Notifications here (SQLDependency is just one of the ways you can leverage QN).

    If so, it's probably a settings problem. For starters, QN is supported on Express. The note about Service Broker simply means it won't process messages that did not come from or pass through some other edition of SQL Server.

    BOL has a page on troubleshooting QN. First thing I'd check is the list of SET options that have to be on for QN to work. They're all off by default. Next common place for things to break after migrating/upgrading to a new instance is permissions. QN requires specific permissions for the user to subscribe to notifications. If you use SQLDependency, the user that creates that notification needs explicit permissions granted to create service, queue and procedure. Lots of details on this in BOL.


    No great genius has ever existed without some touch of madness. - Aristotle

    Thursday, April 26, 2012 5:46 PM
  • Joe,

    Thank you for your response. Glad to hear QN continues to be supported on 2112. I am aware of all those 'tricky' settings that you are refering to, the ones that BOL referes to are turned on. In fact, my 2008 and 2112 settings are identical in that respect, and 2008 QN works, but 2112 does not. I'll look into permissions shortly (I'm in the middle of something else for another day or so).

    Peter


    PMBT

    Thursday, April 26, 2012 8:07 PM
  • Joe,

    Thanks again for your response. I was bogged down on other things, but I have also figured out what the problem was. Let me write down a brief explanation so other people do not have to go through the grief I went through.

    in our software there is a table with 'reference' records. Those reference records are montored for changes using QN, whenever a reference record gets changed, another (different table and ) record gets read and updated from the server. Don't ask me why this indirection, it is not relevant here.

    What happened is this: Each reference record contains columns A (primary key), B, and C. The latter is the time, and gets changed as mentioned above.

    My (software) clients would set up QN using column A in above records. In SQL server 2008, notification would 'fire' every time column C was updated, even though column 'A' was being 'watched' by clients. Not so in SQL Server 2012.

    This was a bug in our code - we should have been 'watching' column C, instead of A, in our clients. Our code worked in 2008, but not in 2012. I modified our code to watch column C, now it is all working fine once again.

    Don't know why this is so, the only explanation I have is that Micorsoft folks must have tightened up the logic in 2012 QN code. Maybe not?

    If anybody is interested in more details on this - I'll be glad to elaborate.

    In any case, thanks for your time once again.


    PMBT

    • Marked as answer by PMBT Friday, May 11, 2012 2:40 PM
    Friday, May 11, 2012 2:40 PM
  • Hi, 

    PMBT pealse tell me more about this way because I've the problem.

    Thank's in advence.

    Sunday, February 03, 2013 9:57 AM
  • Hi Najibnet,

    Gladly. What seems to be the nature of your problem?


    PMBT

    Sunday, February 03, 2013 2:18 PM
  • That was very useful to know. I would also like to ask if you know any changes about SqlDependency timeouts. I set the Dependency to timeout after 3 minutes and while SqlServer 2008 R2 process prerfectly the timeout , SqlServer 2012 SP1 does nothing.. Do you have any clue?

    Thursday, November 14, 2013 11:28 AM
  • Hi,

    No, unfortunately I do not. Will update this response if I found anything...


    PMBT

    Thursday, November 14, 2013 12:12 PM
  • There is a change that I found out. SqlServer 2012 triggers at timeout but with different argument at the handler. The SqlNotificationEventArgs, Source property remains Timeout but Info property is Error , while on SQLServer 2008 R2 Info property was Change.
    Thursday, November 14, 2013 12:15 PM