SQL Notification on a 2005 DB restored from a 2000db RRS feed

  • Question

  • Hello all,


    I need some help with SQL Notifications.  I am upgrading an app that uses 2000 to 2005 and want to implement SQL Notifications.  I have learned that I need to create the DB in 2005 and then restore the 2000 over it(permissions issue). 


    Let me start by detailing the situations.


    1.  Initially the Notification would just keep firing giving a "Set Options" error. 


    I learned that Arithabort, ansi_warnings, etc needed to be set properly.  Doing this manually didn't fix the problem.  Manually setting the compatibility to 90 (2005) would fix this problem, but cause #2


    2.  Critical Error received.


    Once the "Set Options" issue is resolved I started receiving a critical error stating: "A server error occurred on the current command.  The results, if any, should be discarded."  This occurs on any update, insert, delete for the table in which the notification is set on.





    Creating a blank DB intially sets the compatibility at 90 (normal), but restoring the 2000 DB resets it back to 80.


    Thanks in advance for any help.  Mostly I am looking for possible causes for this issue.  Does anyone have a cut and dry comparison of compatibility between 2000-2005, ie what from 2000 won't work in 2005.


    Thanks guys








    Friday, June 22, 2007 1:54 PM

All replies

  • No body knows anything about this????
    Tuesday, June 26, 2007 10:30 PM
  • Hi -

    Upgrading a SSNS 2000 instance to SSNS2005 is relatively straightforward as long as you address a handful of issues going into it.

    I've documented this process in the "SQL Server 2005 Upgrade Technical Reference Guide" document available from the Microsoft web site. http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en

    I've also described it in this Simple-Talk article.


    Tuesday, July 3, 2007 1:32 PM