Re: Compatibility Levels

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/02/04 

Date: Wed, 1 Sep 2004 19:17:21 -0500

You ought to be able to run a script like the one below via OSQL:

EXEC sp_dbcmptlevel 'MyDatabase', 80
GO
ALTER TABLE MyTable
ALTER COLUMN MyColumn int NOT NULL
GO 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard J" <RichardJ@discussions.microsoft.com> wrote in message 
news:3AAF4748-059E-4033-8286-6D1596C03FFE@microsoft.com...
> Hi group,
>
> Pardon me if this is not the appropriate forum for this question.
>
> Our product uses a mix of SQL Server 6.5 and SQL Server 2000.  Some 
> clients
> use one or the other.  Some of these clients upgraded to SQL Server 2000. 
> In
> these instances, the compatibility level of the database remains at 65.
>
> We are now trying to update the database to include primary keys (don't 
> get
> me started on why it has taken so long), but running into problems because
> many of the fields that would be used were defined as NULL.
>
> And despite being on SQL Server 2000, the compatibility level of 65
> prohibits us from executing the ALTER COLUMN statement.
>
> I know I can use the sp_dbcmptlevel stored procedure to update the
> compatibility level, but cannot do so in a stored procedure or ISQL query
> process.  If I try, the command does not execute and I get a message about
> how sp_dbcmptlevel can only be run at the ad-hoc level.  I get a similar
> message if I try to update the database table directly, which doesn't 
> really
> surprise me.
>
> So, are we stuck to manually updating each compatibility level to 80 
> through
> either the Enterprise Manager or Query Analyzer?  Is there no way to 
> change
> it on the fly, so to speak?
>
> Sorry for the length, but thanks for reading this far and for any input 
> you
> can give.
> Richard

Relevant Pages

  • Re: SQL SERVER2005-DeveloperEdition
    ... When a SQL Server 2000 database is restored or attached to a SQL Server 2005 ... The database compatibility level ... corrected before the conversion will be permitted to complete. ...
    (microsoft.public.sqlserver.msde)
  • Re: Attach SQL Server 2008 database to SQL Server 2000 Server
    ... Compatibility level affects functionality, not the actual database structure ... I have created a database in SQL Server 2008, ... It says it cannot attach the database because it is a newer version ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 2005 dbo user issue
    ... we can resolve the issue by setting the ... compatibility level to "SQL Server 2005 ". ... Right-click the database in the Management Studio. ...
    (microsoft.public.sqlserver.security)
  • Re: How to set default cmptlevel in SQL2005
    ... I understand that you BACKUP DATABASE (formerly called DUMP DATABASE) from SQL Server 2005 and are trying to RESTORE DATABASE on MSDE2000. ... The compatibility level has nothing to do with the file format. ...
    (microsoft.public.sqlserver.server)
  • Re: Restoring database with different COLLATION
    ... > How I can modify the COLLATION of the database after restoring it? ... as SQL Server 2000 supports different collations and sort orders for each ... ALTER DATABASE db_name ... ALTER COLUMN VARCHAR ...
    (microsoft.public.sqlserver.msde)