Dumb question about compatibility level

Hi everyone,

This is my first psot so I am hoping for success..

I had been asked to web enable a sql 2000 database. We upgraded to sql 2005 and I imported the data. All went well until a tried to run a CTE ( common table ) routine and it kept on coming back with an error. On searching the web it turns out that the compatibility level must be set to 2005.

When I tried to set the compatibility level the only two options are sql 7 and sql 2000.

I thought it might be due to the fact I had restored from a 2000 database so I tried to create a new database in SSMS but again only sql 7 and 2000 options were available from the drop down box..

What am I doing wrong ?

Am I not running in sql 2005 at all ?

Any help would be appreciated

PeterAnother note..

I tried using

sp_dbcmptlevel DairyManager , 90

and it kept on coming back with :-

'Valid values of database compatibility level are 60, 65, 70, or 80.'

if I tried

sp_dbcmptlevel DairyManager , 80
that worked fine..

Me thinks something is not quite correct here!

Please can someone help as I need to sort this out before any further development|||

Try running the following on your SQL Server instance:

SELECT @.@.VERSION

All being well then something like the following should be returned:

Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86) May 8 2006 22:41:28 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

If the version returned is 'Microsoft SQL Server 2000 etc...', and if you performed a side-by-side upgrade when you installed the 2005 instance, then you are probably inadvertedly using the old 2000 instance instead of the new 2005 instance - this would account for the problems you are experiencing.

Chris

|||

This is on SQL 2005 Server? That will be interesting

We restored our SQL2000 database files onto a newly installed SQL2005 Server, change compatibility modes in SSMS to 9.0 (from 8.0) no problem

Helps to list your environment, SQL version, SP1/SP2, blah blah...

|||

It really sounds like you are running SQL Server 2000. If you did an in place upgrade from 2000 to 2005, the default instance gets upgraded to 2005. If you installed a named instance of 2005 on the same machine where 2000 was running, maybe you are accidently connecting the the default instance that is still running SQL Server 2000.

Running @.@.VERSION will settle the question. Also the version number of the SQL instance you are connected to will show up in SSMS. If you are in a query window, it will say something like 9.0 SP1 down at the bottom, or in Object Explorer it will say SQL Server 9.0.3042 (for 2005 SP2)

Dug ourselves into a hole

We just upgraded from 6.5 to 2000. The original database lived on two device
s and as a result the upgrade process created an mdf and ndf files for the p
rimary filegroup.
Here is the current allocation
db_data1.mdf 9MB
db_data2.ndf 3600MB
Logically, we would use alter database to empty the file contents of the mdf
file. SQL lets us to this. However, when we ask it to delete the file after
it is emptied, it informs us that we cannot delete the primary file.
The only solution is to empty the db_data2.ndf and then remove it.
Is this the best solution? And is this process of moving data from one file
to another highly reliable?I tend to use the primary file - the .MDF file - just for system objects and
place al of my application objects in a separate file. Looks like you're
there already. :-)_
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Gil Darves" <gil.darves@.phtech> wrote in message
news:90CC2143-8DC0-4C9F-A8BC-37D3FE369899@.microsoft.com...
We just upgraded from 6.5 to 2000. The original database lived on two
devices and as a result the upgrade process created an mdf and ndf files for
the primary filegroup.
Here is the current allocation
db_data1.mdf 9MB
db_data2.ndf 3600MB
Logically, we would use alter database to empty the file contents of the mdf
file. SQL lets us to this. However, when we ask it to delete the file after
it is emptied, it informs us that we cannot delete the primary file.
The only solution is to empty the db_data2.ndf and then remove it.
Is this the best solution? And is this process of moving data from one file
to another highly reliable?|||Thank you Thomas.
Yes, I understand about splitting objects but this pre-supposes the secondar
y file is on another filegroup. It is not; it's still the primary filegroup.
So I guess I am still back to square one and I am currently testing the proc
ess of moving the data from the ndf back to the mdf. Looks like it is a very
slow process and with 36 GB will take over 4 hours to accomplish. So I am c
urious, can you perform thi
s action if there is moderate activity on the db? We are thinking of doing t
his over the weekend.
Thanks again!
Gilles de Montreal|||Oh, OK. I'd still go with two filegroups. The big problem here is the
migration, which you can do in stages - as long as the users don't need
access during the migration. First, create a new, single-file filegroup.
Make sure it has lots of space. Then, for each table to be moved:
1. Drop all nonclustered indexes.
2. Drop the clustered index.
3. Create the clustered index on the new filegroup.
4. Create the nonclustered indexes.
Start with the largest table. It could be that you have to do this in a
series of outages. Ideally, you should take a backup of production and try
this out on a similar server.
You can still go the other way and refill the primary file. Consider the
following:
use Northwind
go
alter database Northwind
add file
(
name = Second
, filename = 'E:\temp\Second.ndf'
) to filegroup [PRIMARY]
go
sp_helpfile
go
dbcc shrinkfile (1, emptyfile)
go
dbcc shrinkfile (3, emptyfile)
go
alter database Test
remove file Second
This pushes all of the data back into the primary file. (I'd expand it
first.)
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Gil Darves" <anonymous@.discussions.microsoft.com> wrote in message
news:30EA8F1E-A019-4E6A-BEDB-AF2A6C57B6DB@.microsoft.com...
Thank you Thomas.
Yes, I understand about splitting objects but this pre-supposes the
secondary file is on another filegroup. It is not; it's still the primary
filegroup.
So I guess I am still back to square one and I am currently testing the
process of moving the data from the ndf back to the mdf. Looks like it is a
very slow process and with 36 GB will take over 4 hours to accomplish. So I
am curious, can you perform this action if there is moderate activity on the
db? We are thinking of doing this over the weekend.
Thanks again!
Gilles de Montreal

Dug ourselves into a hole

We just upgraded from 6.5 to 2000. The original database lived on two devices and as a result the upgrade process created an mdf and ndf files for the primary filegroup
Here is the current allocatio
db_data1.mdf 9M
db_data2.ndf 3600M
Logically, we would use alter database to empty the file contents of the mdf file. SQL lets us to this. However, when we ask it to delete the file after it is emptied, it informs us that we cannot delete the primary file
The only solution is to empty the db_data2.ndf and then remove it
Is this the best solution? And is this process of moving data from one file to another highly reliable?This is a multi-part message in MIME format.
--=_NextPart_000_04D3_01C3DB85.8613E550
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
I tend to use the primary file - the .MDF file - just for system objects and
place al of my application objects in a separate file. Looks like you're
there already. :-)_
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Gil Darves" <gil.darves@.phtech> wrote in message
news:90CC2143-8DC0-4C9F-A8BC-37D3FE369899@.microsoft.com...
We just upgraded from 6.5 to 2000. The original database lived on two
devices and as a result the upgrade process created an mdf and ndf files for
the primary filegroup.
Here is the current allocation
db_data1.mdf 9MB
db_data2.ndf 3600MB
Logically, we would use alter database to empty the file contents of the mdf
file. SQL lets us to this. However, when we ask it to delete the file after
it is emptied, it informs us that we cannot delete the primary file.
The only solution is to empty the db_data2.ndf and then remove it.
Is this the best solution? And is this process of moving data from one file
to another highly reliable?
--=_NextPart_000_04D3_01C3DB85.8613E550
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I tend to use the primary file - the =.MDF file - just for system objects and place al of my application objects in a =separate file. Looks like you're there already. :-)_
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Gil Darves" wrote in =message news:90C=C2143-8DC0-4C9F-A8BC-37D3FE369899@.microsoft.com...We just upgraded from 6.5 to 2000. The original database lived on two =devices and as a result the upgrade process created an mdf and ndf files for the =primary filegroup.Here is the current allocationdb_data1.mdf 9MBdb_data2.ndf 3600MBLogically, we would use alter database =to empty the file contents of the mdf file. SQL lets us to this. However, =when we ask it to delete the file after it is emptied, it informs us that we =cannot delete the primary file.The only solution is to empty the =db_data2.ndf and then remove it.Is this the best solution? And is this =process of moving data from one file to another highly reliable?

---

--=_NextPart_000_04D3_01C3DB85.8613E550--|||Thank you Thomas
Yes, I understand about splitting objects but this pre-supposes the secondary file is on another filegroup. It is not; it's still the primary filegroup
So I guess I am still back to square one and I am currently testing the process of moving the data from the ndf back to the mdf. Looks like it is a very slow process and with 36 GB will take over 4 hours to accomplish. So I am curious, can you perform this action if there is moderate activity on the db? We are thinking of doing this over the weekend
Thanks again
Gilles de Montreal|||This is a multi-part message in MIME format.
--=_NextPart_000_0049_01C3DB94.68C0E2A0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Oh, OK. I'd still go with two filegroups. The big problem here is the
migration, which you can do in stages - as long as the users don't need
access during the migration. First, create a new, single-file filegroup.
Make sure it has lots of space. Then, for each table to be moved:
1. Drop all nonclustered indexes.
2. Drop the clustered index.
3. Create the clustered index on the new filegroup.
4. Create the nonclustered indexes.
Start with the largest table. It could be that you have to do this in a
series of outages. Ideally, you should take a backup of production and try
this out on a similar server.
You can still go the other way and refill the primary file. Consider the
following:
use Northwind
go
alter database Northwind
add file
(
name = Second
, filename = 'E:\temp\Second.ndf'
) to filegroup [PRIMARY]
go
sp_helpfile
go
dbcc shrinkfile (1, emptyfile)
go
dbcc shrinkfile (3, emptyfile)
go
alter database Test
remove file Second
This pushes all of the data back into the primary file. (I'd expand it
first.)
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Gil Darves" <anonymous@.discussions.microsoft.com> wrote in message
news:30EA8F1E-A019-4E6A-BEDB-AF2A6C57B6DB@.microsoft.com...
Thank you Thomas.
Yes, I understand about splitting objects but this pre-supposes the
secondary file is on another filegroup. It is not; it's still the primary
filegroup.
So I guess I am still back to square one and I am currently testing the
process of moving the data from the ndf back to the mdf. Looks like it is a
very slow process and with 36 GB will take over 4 hours to accomplish. So I
am curious, can you perform this action if there is moderate activity on the
db? We are thinking of doing this over the weekend.
Thanks again!
Gilles de Montreal
--=_NextPart_000_0049_01C3DB94.68C0E2A0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Oh, OK. I'd still go with two filegroups. The big problem here is the migration, which you can =do in stages - as long as the users don't need access during the =migration. First, create a new, single-file filegroup. Make sure it has lots =of space. Then, for each table to =be moved:
1. Drop all =nonclustered indexes.
2. Drop the =clustered index.
3. Create =the clustered index on the new filegroup.
4. Create the =nonclustered indexes.
Start with the largest table. It =could be that you have to do this in a series of outages. Ideally, you =should take a backup of production and try this out on a similar =server.
You can still go the other way and =refill the primary file. Consider the following:
use Northwind
go
alter database Northwindadd =file( name =3D Second, filename =3D ='E:\temp\Second.ndf') to filegroup [PRIMARY]go
sp_helpfilego
dbcc shrinkfile (1, emptyfile)go
dbcc shrinkfile (3, emptyfile)go
alter database Testremove =file Second
This pushes all of the data back into =the primary file. (I'd expand it first.)
HTH
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Gil Darves" wrote in message news:30E=A8F1E-A019-4E6A-BEDB-AF2A6C57B6DB@.microsoft.com...Thank you Thomas.Yes, I understand about splitting objects but this pre-supposes the secondary file is on another filegroup. It is not; it's =still the primary filegroup.So I guess I am still back to square one =and I am currently testing the process of moving the data from the ndf back to =the mdf. Looks like it is a very slow process and with 36 GB will take over 4 =hours to accomplish. So I am curious, can you perform this action if there is =moderate activity on the db? We are thinking of doing this over the weekend.Thanks again!Gilles de Montreal

---

--=_NextPart_000_0049_01C3DB94.68C0E2A0--