Incorrect data in sysdatabases compatability level column

Hello,
I've noticed that if you have model and tempdb to different compatability
levels and restart the sql instance, the value in sysdatabases (cmptlevel)
does not get changed correctly.
Here's how to reproduce:
1. set model compatability to one level, say 9.0
2. set tempdb compatability to diff. level, say 8.0
3. verify in SSMS db -> properties -> options
or select * from master..sysdatabases
4. run an old join query from tempdb to show it is actually in 8.0
compatability
-> select * from sysobjects o, syscolumn c where o.id *= c.id
if it runs, the compatability is set to 8.0
5. now restart your db instance
6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
8.0 compatability, but you get an error message when running the query which
means the database is in 9.0
Can anyone explain? or is it possible this is a bug?
thanksSorry, I forgot to tell you to run the test query below from the tempdb to
either produce an error message or not. The connection's current db
determines compatability level.
"paul" wrote:
> Hello,
>
> I've noticed that if you have model and tempdb to different compatability
> levels and restart the sql instance, the value in sysdatabases (cmptlevel)
> does not get changed correctly.
> Here's how to reproduce:
> 1. set model compatability to one level, say 9.0
> 2. set tempdb compatability to diff. level, say 8.0
> 3. verify in SSMS db -> properties -> options
> or select * from master..sysdatabases
> 4. run an old join query from tempdb to show it is actually in 8.0
> compatability
> -> select * from sysobjects o, syscolumn c where o.id *= c.id
> if it runs, the compatability is set to 8.0
> 5. now restart your db instance
> 6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
> 8.0 compatability, but you get an error message when running the query which
> means the database is in 9.0
> Can anyone explain? or is it possible this is a bug?
> thanks
>
>|||For what it is worth, I would not mess with the compatibilty level of
either tempdb or model. I know the idea is that you can set model to
match what you want from a new database, but as tempdb is effectively
recreated from model each time SQL Server starts it sounds like asking
for trouble.
Roy Harvey
Beacon Falls, CT
On Mon, 16 Jul 2007 11:14:08 -0700, paul
<paul@.discussions.microsoft.com> wrote:
>Hello,
>
>I've noticed that if you have model and tempdb to different compatability
>levels and restart the sql instance, the value in sysdatabases (cmptlevel)
>does not get changed correctly.
>Here's how to reproduce:
>1. set model compatability to one level, say 9.0
>2. set tempdb compatability to diff. level, say 8.0
>3. verify in SSMS db -> properties -> options
>or select * from master..sysdatabases
>4. run an old join query from tempdb to show it is actually in 8.0
>compatability
> -> select * from sysobjects o, syscolumn c where o.id *= c.id
> if it runs, the compatability is set to 8.0
>5. now restart your db instance
>6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
>8.0 compatability, but you get an error message when running the query which
>means the database is in 9.0
>Can anyone explain? or is it possible this is a bug?
>thanks
>