Compatibility Levels

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

From: Richard J (RichardJ_at_discussions.microsoft.com)
Date: 09/01/04 

Date: Wed, 1 Sep 2004 08:55:04 -0700

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: Why not Access...?
    ... It well depends how the database is used, such as the data access work load, ... When using Jet database, either VB front-end or Access front/back ... On the other hand, a database server, like SQL Server, gives you a lot more ... > 9i just can't work on these machines, and my clients are having these ...
    (microsoft.public.sqlserver.server)
  • Re: Is SQL required for 15 users
    ... their own front-end mdb and the back end should be at least one mdb. ... database designer that was hired to fix this problem recommended that we ... Two clients are using Office 2000, ... recommend I roll-in a SQL server? ...
    (microsoft.public.sqlserver.server)
  • Re: Why not Access...?
    ... The first point is, yes, Jet, not Access, is a file based, Pseudo-RDBMS. ... Jet database will corrupt and become ... MS Jet/Access, MSDE, and SQL Server 2005 Express Edition. ... > I am developing a desktop application using VB as front-end for some clients> where the a large number of regular entries are done but there is a rare need> to delete old customers. ...
    (microsoft.public.sqlserver.server)
  • Replicate to MS Access via the Internet
    ... Can I use SQL Server’s inbuilt replication to replicate between a SQL Server ... database and a number of MS Access 97, 2000 and 2002 databases over the ... The clients modify their local ...
    (microsoft.public.access.replication)
  • Replicate to MS Access via the Internet
    ... Can I use SQL Server’s inbuilt replication to replicate between a SQL Server ... database and a number of MS Access 97, 2000 and 2002 databases over the ... The clients modify their local ...
    (microsoft.public.sqlserver.replication)