Post reply

Change Compitibility Level

  • May 7, 2009 at 5:50 am

    #129865

    Hai All,

    I unable to change the compitibility level from 80 to 90 in SQL SERVER 2005., in my database its default compitibility level is 80.

    I write the below query in query analyzer.

    ALTER DATABASE PUBS SET COMPATIBILITY_LEVEL, 90

    Error: Incorrect syntax near 'COMPATIBILITY_LEVEL'

    EXEC sp_dbcmptlevel PUBS, 90

    Error: Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

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

    So please anybody help me......

    Thanks &Regards

    Mansoor

    ----------------------------------------------------------------------------------------------------
    Mistakes are common.....and i am not an exception....
    catch me if i am wrong....

    MAK.................!

  • May 7, 2009 at 6:07 am

    #990257

    Use sp_dbcmptlevel

    EXEC dbo.sp_dbcmptlevel @dbname=N'PUBS', @new_cmptlevel=90

    [font="Comic Sans MS"]+++BLADE+++[/font]:cool:

  • May 7, 2009 at 6:09 am

    #990259

    Sorry..............the reply above was totally by mistake

    [font="Comic Sans MS"]+++BLADE+++[/font]:cool:

  • May 7, 2009 at 6:11 am

    #990265

    Are you sure that you are on SQL 2005? Are you trying to change this in SQL 2000 by any chance?

  • May 7, 2009 at 6:14 am

    #990268

    OH thats correct krishna............i'm also having a doubt whether he is trying to do it on sql2000

    [font="Comic Sans MS"]+++BLADE+++[/font]:cool:

  • May 7, 2009 at 6:15 am

    #990270

    And the error resembles so

    [font="Comic Sans MS"]+++BLADE+++[/font]:cool:

  • May 8, 2009 at 12:31 am

    #990863

    Dear All,

    Thanks for giving response.

    I am sure that i m using SQL SERVER 2005, and i tryed to executed the scripts also in SQL SERVER 2005.

    Ref: You can see the below attachments..

    Thanks &Regards

    Mansoor

    ----------------------------------------------------------------------------------------------------
    Mistakes are common.....and i am not an exception....
    catch me if i am wrong....

    MAK.................!

  • May 8, 2009 at 12:43 am

    #990869

    Mansoor (5/8/2009)

    Dear All,

    Thanks for giving response.

    I am sure that i m using SQL SERVER 2005, and i tryed to executed the scripts also in SQL SERVER 2005.

    Ref: You can see the below attachments..

    Thanks &Regards

    Mansoor

    Hi Mansoor,

    According to the attachment (second one) you are using SSMS but connected to SQL Server 2000.

    Notice the server name in the bottom "XNSSRVRIS002 (8.0 SP3)".

    8.0 means SQL Server 2000.

    Regards,

    Suresh

  • May 8, 2009 at 12:47 am

    #990870

    Actually the BMP files don’t prove that it is SQL Server 2005. We can see that the client tool is SQL Server Management studio, but that doesn’t prove that the server is also SQL Server 2005 (you can use SSMS to connect to SQL Server 2000). According to the error that you get it does seem that the server is SQL Server 2000 and not SQL Server 2005. Can you run select @@version to see for sure the server’s version?

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • May 8, 2009 at 5:57 am

    #990992

    Mansoor,

    The database you are tying to connect in an sql2000 DB server.......Then how can you change the compatibility level to 90........

    For your clarification use this ........."select @@version"

    [font="Comic Sans MS"]+++BLADE+++[/font]:cool:

  • May 8, 2009 at 7:43 am

    #991076

    There is atually an easy way to resolve this in SMS the user can go the databases section right click on Pubs and go to properties, then options and change the compatibility level there. I have Northwind it was 80 and I just go to options and change it to 90.

    Kind regards,
    Gift Peddie

  • May 10, 2009 at 11:52 pm

    #991899

    Dear Blade

    Thanks for giving reply.once i check select @@version its showing sql2000 server.

    Can we change the database PUBS from Sql2000 to sql2005.then how ?

    +++Mansoor +++

    ----------------------------------------------------------------------------------------------------
    Mistakes are common.....and i am not an exception....
    catch me if i am wrong....

    MAK.................!

  • May 11, 2009 at 12:43 am

    #991911

    Mansoor (5/10/2009)

    i check select @@version its showing sql2000 server.

    Can we change the database PUBS from Sql2000 to sql2005.then how ?

    No. Not without upgrading to SQL Server 2005.

  • September 23, 2010 at 2:52 pm

    #1226613

    Blade you the man. Tried to do an ad hoc update which is not allowed without a single mode Séance which can't be done.

    I had to change like 50 of these on an instance I'm upgrading then you came along.

    select 'EXEC dbo.sp_dbcmptlevel @dbname=N''' +name + ''', @new_cmptlevel=100'

    from master..sysdatabases

    where cmptlevel = 80

  • September 24, 2010 at 12:36 am

    #1226727

    Hi

    You All,

    Why you are not using the SSMS as Gift Peddie said. Its Easy.

    Ali
    MCTS SQL Server2k8

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply