juillet
2010
Un article de zinzineti
Lorqu’on fait un Upgrade de Server SQL 2000 à SQL Server 2005 on constate que toutes les bases
systèmes ne passent pas au niveau de compatibilité SQL 2005! Seules les bases tempdb,model,msdb passent au niveau SQL 2005
la base master elle reste au niveau 80 !!!
Dans ce billet nous allons voir :
Comment modifier le COMPATIBILITY_LEVEL d’une base (master par exemple) sous SQL Server 2005
Comment modifier le COMPATIBILITY_LEVEL pour un groupe de bases de données sous SQL Server 2005
/*****************************************************************************************************************
— DESCRIPTION : Upgrade SQL 2000 –> SQL 2005 – problème de niveau de compatibilité (COMPATIBILITY_LEVEL)
— Rédacteur: Etienne ZINZINDOHOUE
— Contributeurs : David BAFFALEUF et David BARBARIN
*****************************************************************************************************************/
Quand j’exécute cette commande après l’Upgrade de l’instance SQL 2000 –> SQL Server 2005
SELECT name [Base]
,[compatibility_level] [Niveau compatibilité]
FROM sys.databases
WHERE name in ('master','tempdb','model','msdb')
–> En quoi le niveau de compatibilité est-il important ?
================================================================================================================
La copie d’écran ci-dessous montre une requête qui n’est pas compréhensible par le moteur SQL 2005 (disons Upgradé SQL 2005)!
–> Comment modifier le niveau de compatibilité de la base master sous SQL SERVER 2005
================================================================================================================
on peut penser que ALTER DATABASE [master] SET COMPATIBILITY_LEVEL = 90 allait régler le problème, mais non !
Remarque : Si on était sous SQL 2008 la commande ALTER DATABASE [maBase] SET COMPATIBILITY_LEVEL = 90
allait marché 
–> Solution :
================================================================================================================
Pour mettre la base master au niveau SQL Server 2005 il faut exécuter la commande
EXEC sp_dbcmptlevel [master], 90
–> Mettre à niveau (COMPATIBILITY_LEVEL) un groupe de bases de données utilisateur sous SQL Server 2005
================================================================================================================
USE master
GO
DECLARE @BaseEnCoursTraitement VARCHAR(50),@SQL VARCHAR (max), @DEL VARCHAR (max)
CREATE TABLE #LISTE_BASES (nom_base varchar (50))
INSERT INTO #LISTE_BASES
SELECT [name] AS [NomBase]
FROM sys.databases
--Liste des bases qu'on désire mettre à niveau SQL 2005
WHERE [name] in ('MaBase1','MaBase2','Mabase3')
ORDER BY [NomBase]
-- Tant qu'il y a des lignes dans la table temporaire
WHILE EXISTS ( SELECT nom_base
FROM #LISTE_BASES
)
BEGIN
--Se positionner sur la 1ère ligne de la table temporaire
SET @BaseEnCoursTraitement = (SELECT TOP 1 nom_base FROM #LISTE_BASES)
-- Appliquer le Niveau de compatibilité SQL 2005
EXEC sp_dbcmptlevel @BaseEnCoursTraitement , 90 ;
-- Supprimer la base traitée de la table temporaire
SET @DEL = 'DELETE FROM #LISTE_BASES WHERE nom_base = '''+ @BaseEnCoursTraitement + ''';'
EXEC (@DEL)
END
--Supprimer la table temporaire
DROP TABLE #LISTE_BASES
-- Afficher les bases et leur Niveau compatibilité
SELECT name [Base]
,[compatibility_level] [Niveau compatibilité]
FROM sys.databases
-- Liste des bases de données modifiées
WHERE [name] in ('MaBase1','MaBase2','Mabase3')
/*****************************************************************************************************************
— DESCRIPTION : Upgrade SQL 2000 –> SQL 2005 – problème de niveau de compatibilité (COMPATIBILITY_LEVEL)
— Rédacteur: Etienne ZINZINDOHOUE
— Contributeurs : David BAFFALEUF et David BARBARIN
*****************************************************************************************************************/
1 .) je suis d’accord qu’il faut mettre la base en mode SINGLE_USER avant le traitement. Donc le script peut être modifier comme suit :
— Mettre la base en SINGLE_USER
ALTER DATABASE BaseEnCoursTraitement SET SINGLE_USER
— Appliquer le Niveau de compatibilité SQL 2005
EXEC sp_dbcmptlevel @BaseEnCoursTraitement , 90 ;
— Remettre la base en MULTI_USER
ALTER DATABASE @BaseEnCoursTraitement SET MULTI_USER
2. ) Non SELECT TOP1 … DELETE n’a rien avoir avec un CURSEUR.
Pour être convaincu, tu peux comparer la durée d’exécution, le temps CPU, les pages logiques lues
du script ci-dessous avec un CURSOR et le script avec SELECT TOP 1 … DELETE
DECLARE @BaseEnCoursTraitement VARCHAR(50);
DECLARE baseATraiter_cursor CURSOR FOR
SELECT [name] AS [NomBase]
FROM sys.databases
WHERE [name] in (‘MaBase1′,’MaBase2′,’MaBase3′)
ORDER BY [NomBase]
OPEN baseATraiter_cursor
FETCH NEXT FROM baseATraiter_cursor INTO @BaseEnCoursTraitement
EXEC sp_dbcmptlevel @BaseEnCoursTraitement , 90 ;
WHILE @@FETCH_STATUS = 0
BEGIN
— le suivant
FETCH NEXT FROM baseATraiter_cursor INTO @BaseEnCoursTraitement
— Appliquer le Niveau de compatibilité SQL 2005
EXEC sp_dbcmptlevel @BaseEnCoursTraitement , 90 ;
END
CLOSE baseATraiter_cursor
DEALLOCATE baseATraiter_cursor
En tout cas merci pour tes multiples contributions très utiles et pratiques
A+
Ah ouais tu réinventes les curseurs là (select top 1… delete …).
Il est conseillé tout de même de passer la base en single_user avant de modifier le mode de compatibilité, parce que cela peut produire des résultats incohérents pour les sessions actives.
David B.