How to change the compatibility level to 9.0

I have a SQL 2005 server that has more than 90 databases, and I need to change the compatibality level to 9.0. Is there an easy way to do this in SQL? any help is greatly appreciated.

Emad

I don't know if it can be called easy, but the following script should do the work, by using the stored procedure sp_dbcmptlevel , to update all databases whose compatibility_level is not equal to 90.

Hope this Helps!!!

Roberto Hernandez-Pou
http://community.rhpconsulting.net

DECLARE DATABASES_80 CURSOR
READ_ONLY
FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != '90'

DECLARE @.DATABASENAME NVARCHAR(255)
DECLARE @.COUNTER INT

-- INITIALIZE VARIABLES
SET @.COUNTER = 1

-- OPEN CURSOR
OPEN DATABASES_80

FETCH NEXT FROM DATABASES_80 INTO @.DATABASENAME
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN

-- CHANGE DATABASE COMPATIBILITY
EXECUTE sp_dbcmptlevel @.DATABASENAME , '90'

-- PRINT
PRINT '(' + RIGHT(('000' + CAST(@.COUNTER AS NVARCHAR(255))),3) + ') - CHANGED DATABASE COMPATIBILITY LEVEL FOR: ' + @.DATABASENAME

-- INCREASE COUNTER
SET @.COUNTER = @.COUNTER + 1

END

FETCH NEXT FROM DATABASES_80 INTO @.DATABASENAME
END

CLOSE DATABASES_80
DEALLOCATE DATABASES_80

GO

sql

how to change server option for isolaiton level?

I'm having problem with locking in update and select.
my update takes long and select from clients happens all the time.
I know with NOLOCK in select or set transaction isolation level, I can
read data uncommited.
I want to know if there is an option I can change on server or
database
to change isolation level for all incoming connections.

thanks for your help in advance.AFAIK, you can't set READ UNCOMMITTED at the server level. This is
probably a good thing since one should read uncommitted data only in
specific situations where all off the ramifications have been fully
evaluated and understood.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"neo" <second714@.hotmail.com> wrote in message
news:155f8e7d.0311071340.6727ff6d@.posting.google.c om...
> I'm having problem with locking in update and select.
> my update takes long and select from clients happens all the time.
> I know with NOLOCK in select or set transaction isolation level, I can
> read data uncommited.
> I want to know if there is an option I can change on server or
> database
> to change isolation level for all incoming connections.
> thanks for your help in advance.|||Hi

To add to Dan's comment...

Are you wanting to make the database read only?

John

"neo" <second714@.hotmail.com> wrote in message
news:155f8e7d.0311071340.6727ff6d@.posting.google.c om...
> I'm having problem with locking in update and select.
> my update takes long and select from clients happens all the time.
> I know with NOLOCK in select or set transaction isolation level, I can
> read data uncommited.
> I want to know if there is an option I can change on server or
> database
> to change isolation level for all incoming connections.
> thanks for your help in advance.|||Thanks to Dan/John.

No I just wanted to allow dirty-read, not read-only db.

I thought there was an option in SQL to set db to allow read uncommited.
Maybe for SQL 7?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<boieas$93o$1@.titan.btinternet.com>...
> Hi
> To add to Dan's comment...
> Are you wanting to make the database read only?
> John
> "neo" <second714@.hotmail.com> wrote in message
> news:155f8e7d.0311071340.6727ff6d@.posting.google.c om...
> > I'm having problem with locking in update and select.
> > my update takes long and select from clients happens all the time.
> > I know with NOLOCK in select or set transaction isolation level, I can
> > read data uncommited.
> > I want to know if there is an option I can change on server or
> > database
> > to change isolation level for all incoming connections.
> > thanks for your help in advance.|||Hi

This is not something you would want do as a default, and when you do
require it, then it has to be a concious decission to allow this;
therefore not having it at server level or as a default for the
connection is probably a good thing. You can always set it for the
connection/session.

John

second714@.hotmail.com (neo) wrote in message news:<155f8e7d.0311101122.ca78281@.posting.google.com>...
> Thanks to Dan/John.
> No I just wanted to allow dirty-read, not read-only db.
> I thought there was an option in SQL to set db to allow read uncommited.
> Maybe for SQL 7?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<boieas$93o$1@.titan.btinternet.com>...
> > Hi
> > To add to Dan's comment...
> > Are you wanting to make the database read only?
> > John
> > "neo" <second714@.hotmail.com> wrote in message
> > news:155f8e7d.0311071340.6727ff6d@.posting.google.c om...
> > > I'm having problem with locking in update and select.
> > > my update takes long and select from clients happens all the time.
> > > I know with NOLOCK in select or set transaction isolation level, I can
> > > read data uncommited.
> > > I want to know if there is an option I can change on server or
> > > database
> > > to change isolation level for all incoming connections.
> > > > thanks for your help in advance.|||Thanks,
one more thing,
what about Query analyzer?
is there any way not to run "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
everytime in Query Analyzer?|||Hi

As far as I know, you can't do this, configurable options appear on
the tools/options dialogues and that is not one of them. If it was you
could use a specific configuration file. The only thing I can suggest
it to put it in a template.

John

second714@.hotmail.com (neo) wrote in message news:<155f8e7d.0311111702.4609998d@.posting.google.com>...
> Thanks,
> one more thing,
> what about Query analyzer?
> is there any way not to run "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
> everytime in Query Analyzer?