Need to Change Compatibility Level of Lots of Databases

Question

Need to Change Compatibility Level of Lots of Databases

lmarkum

SSCertifiable

Points: 7985
More actions
September 29, 2017 at 12:55 pm

#402548

I have a SQL Server 2014 instance with quite a few databases on it. I want to change the compatibility level of the user databases. I'm trying to write a cursor to do this but I'm getting syntax errors with the variable inside the BEGIN/END. What am I doing wrong?
DECLARE @DBName VARCHAR(100)
DECLARE CompatCursor CURSOR FOR
SELECT NAME FROM master.dbo.sysdatabases WHERE NAME NOT IN('master', 'tempdb', 'model', 'msdb')
OPEN CompatCursor FETCH NEXT FROM CompatCursor INTO @DBName
WHILE @@FETCH_STATUS = 0 BEGIN
ALTER DATABASE @DBname SET SINGLE_USER
ALTER DATABASE @DBName SET Compatibility_level = 120
ALTER DATABASE @DBname SET MULTI_USER
FETCH NEXT FROM CompatCursor INTO @DBName
END
CLOSE CompatCursor DEALLOCATE CompatCursor

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

I have a slightly different approach for you to consider. Try running this - it generates all of the T-SQL for you, which you can cut, paste and execute:
SELECT d.name , tsql = CONCAT( 'ALTER DATABASE [' , d.name , '] SET SINGLE_USER; ALTER DATABASE [' , d.name , '] SET Compatibility_level = 120; ALTER DATABASE [' , d.name , '] SET MULTI_USER;' ) FROM sys.databases d WHERE d.database_id > 4;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Just a bit of an FYI, I've not found it necessary to set the SINGLE USER mode in 2012 or 2016.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLPirate SSCrazy Points: 2880 More actions · Answer 3

Your syntax error is because you're using a variable for the database name, you'd need some dynamic SQL to make it work.
If you still want to stick with a cursor, this should work for you. You could also put all the SET statements into the @SQL variable at the same time and run it together rather than as separate executions.

DECLARE @DBName VARCHAR(100) ,@SQL nvarchar(200)

DECLARE CompatCursor CURSOR FOR

SELECT NAME FROM master.dbo.sysdatabases WHERE NAME NOT IN('master', 'tempdb', 'model', 'msdb')

OPEN CompatCursor FETCH NEXT FROM CompatCursor INTO @DBName

WHILE @@FETCH_STATUS = 0 BEGIN

SET @SQL = 'ALTER DATABASE [' + @DBName + '] ' + 'SET SINGLE_USER WITH ROLLBACK IMMEDIATE' EXECUTE( @SQL ) SET @SQL = 'ALTER DATABASE [' + @DBName + '] ' + 'SET Compatibility_level = 120' EXECUTE( @SQL )

SET @SQL = 'ALTER DATABASE [' + @DBName + '] ' + 'SET MULTI_USER' EXECUTE( @SQL )

FETCH NEXT FROM CompatCursor INTO @DBName

END

CLOSE CompatCursor DEALLOCATE CompatCursor

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

I've still not run into a problem with NOT setting the single user mode. Be VERY aware that will kill ALL connections but the current one and, if something goes wrong and the current window loses connection, one of the web servers will grab that single connection in a virtual heartbeat and you'll have one hell of a time getting back to the multi user mode.

And, yes... I know what the Microsoft recommendation is and why. If you decide to do it that way, you need to at least have a plan on how to get the single connection back if something goes haywire.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lmarkum SSCertifiable Points: 7985 More actions · Answer 5

Phil, Thanks for that query. I've used that approach before on other things, I just didn't think of it this time.

lmarkum SSCertifiable Points: 7985 More actions · Answer 6

SQL Pirate, That was the answer to my specific question, so thanks.

Jeff, I've frequently had to use the SET SINGLE_USER WITH ROLLBACK IMMEDIATE phrase but I'm not sure if that was on 2012 and above. Good warning about losing that one session while running the query.