Interesting issue happening in a 80 to 90 converted DB
Let me layout the scenario. I'm writing a windows service that will monitor
a a SQL Database via SQL Query Notification Service. Nothing out of the
ordinary there. All that is working fine. Here where it gets interesting.
I'm running SQL Server 2005. Most of the database have been upgraded from
SQL 2000, so they start with the compatibility level set to 80. I took one
of these databases and flipped the compatibility level to 90 so I could get
the Notification Service running. Everything starts fine and the app starts
to monitor a table, but if I try to make a change to the data of the table
being monitored I get an access violation error. To make sure the issue
wasn't with my processes I created a fresh SQL 2005 Database with the same
structure as the SQL 2000 originated database. Inserted some data, started
the monitoring process, tried to make a data change, and everything worked
perfect. The change got committed and the monitoring process got a
notification that something happened.
What would be missing from the upgraded database that a fresh database would