Problème de collation entre 2 DB

[Kropernic]

Bonjour à tous !

Je viens vers vous car je suis un peu perdu...

Commençons par planter le décor. Il y a une première DB donc la collation est French_CI_AS et une deuxième DB dont la collation est Latin1_General_CI_AS.

Lorsque je veux faire une requête qui lie ces deux DB, j'obtiens une erreur avec le message suivant :

Cannot resolve the collation conflinct between "French_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Enfin, une des deux DB vient du serveur local d'un de mes collègues. J'imagine que lors de l'installation de son serveur, il a du choisir french comme collation du serveur et que maintenant, ça entre en conflit (youpie'-_-).

J'ai donc modifié la collation de la table en French pour la mettre en Latin mais il semble que les colonnes des tables restent en French malgré tout et donc mon erreur persiste.

Ma question est donc double :

- Existe un moyen de convertir vers la bonne collation dans la requête ?
- Est-il possible de créer une requête qui va aller changer la collation de toutes les colonnes de toutes les tables de la DB en French pour y mettre Latin1 ? (je n'ai jamais préciser de collation lorsque j'ai écrit un ordre create table... du coup, je suis perplexe).

Merci d'avance pour l'aide précieuse que vous pourrez m'apporter.

Griftou.

[aieeeuuuuu]

Bonjour

A première vue, le choix des collations parait annondin, c'est par la suite que les choses se corse.
néanmoins, dans une requete, vous pouvez spécifier la collation à utiliser

par exemple dans le cas d'une simple vérification d'égalité, vous pouvez faire :

Code SQL :

1
2
3
4
5
6
 
SELECT *
FROM DB1.UneTable T1
INNER JOIN DB2.UneAutreTable T2
    ON T1.ID = T2.ID
WHERE T1.UneColonne = T2.UneAutreColonne COLLATE French_CI_AS

[Kropernic]

ok, ça a l'air de fonctionner. NICE !!


Par contre, j'dois avoir un problème avec les outer join... Va falloir que je retourne jeter un oeil sur les articles de sqlpro...

[Kropernic]

Par contre pour le futur, histoire de ne pas devoir mettre collate à chaque fois (car ce french ne devrait pas être), comment puis-je changer la collation partout en une fois ?

[elsuket]

Bonjour,

Vous pouvez le spécifier :

- à l'installation de SQL Server et ce n'est pas modifiable par la suite
- à la création de chaque base de données

Dans le deuxième cas :

- Si vous le faites avec l'UI de SQL Server Management Studio, lorsque vous êtes dans l'assistant, vous pouvez spécifier la collation dans la page Options

- Soit en requête, avec la clause COLLATE

@++

[Kropernic]

Envoyé par elsuket

Bonjour,

Vous pouvez le spécifier :

- à l'installation de SQL Server et ce n'est pas modifiable par la suite
- à la création de chaque base de données

Dans le deuxième cas :

- Si vous le faites avec l'UI de SQL Server Management Studio, lorsque vous êtes dans l'assistant, vous pouvez spécifier la collation dans la page Options

- Soit en requête, avec la clause COLLATE

@++

Ca a l'air tout con et pourtant, j'ai l'impression que quelque chose m'échappe...

Je suis management studio. J'ai changé la collation de la DB. Cela a bien modifié la collation des tables qu'elle contient mais pas des colonnes se trouvant dans les tables.

Est-ce normal ?

[elsuket]

Pardonnez-moi pour la documentation de COLLATE, qui est ici.

La collation peut être spécifiée :

- au niveau de l'instance
- au niveau de la base de données
- au niveau des colonnes

Mais pas pour une table.
A la création d'une table, si la collation n'est pas spécifiée pour les colonnes de type chaîne de caractères, alors la collation de la base de données est utilisée.
De la même façon, à la création d'une base de données, si la collation n'est pas spécifiée, alors la collation de l'instance SQL Server est utilisée.

Donc vous devez passer en revue toutes les colonnes de toutes les tables de votre base de données pour voir lesquelles n'utilisent pas la collation que vous souhaitez avoir, puis la changer.
Notez que cela aura un impact lourd, et qu'il est donc fortement conseillé de tester cela avant de le faire en production (restaurez une sauvegarde sous un autre nom de base de données pour ce faire).

Vous pouvez vous générer le code avec le script suivant :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
DECLARE @sql varchar(max)
	, @old_collation sysname = 'ancienneCollation'
	, @new_collation sysname = 'nouvelleCollation'
 
;WITH
	CTE AS
	(
		SELECT		S.name + '.' + T.name AS table_name
				, C.name AS column_name
				, TY.name AS data_type_name
				, CASE WHEN TY.name IN ('nchar', 'nvarchar') THEN C.max_length / 2 ELSE C.max_length END AS max_length
				, C.is_nullable
		FROM		sys.schemas AS S
		INNER JOIN	sys.TABLES AS T
					ON S.schema_id = T.schema_id
		INNER JOIN	sys.COLUMNS AS C
					ON T.object_id = C.object_id
		INNER JOIN	sys.types AS TY
					ON TY.system_type_id = C.system_type_id
					AND TY.system_type_id = TY.user_type_id
		WHERE		C.collation_name = @old_collation
		AND		C.is_computed = 0
	)
SELECT	@sql = CASE 
			WHEN @sql IS NULL THEN 'ALTER TABLE ' + C.table_name 
						+ ' ALTER COLUMN ' + C.column_name + ' ' + C.data_type_name
						+ '(' + CASE WHEN C.max_length = - 1 THEN 'max' ELSE CAST(C.max_length AS varchar(10)) END + ')'
						+ ' COLLATE ' + @new_collation
						+ CASE WHEN C.is_nullable = 0 THEN ' NOT NULL' ELSE '' END
			ELSE @sql + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
				+ 'ALTER TABLE ' + C.table_name 
						+ ' ALTER COLUMN ' + C.column_name + ' ' + C.data_type_name 
						+ '(' + CASE WHEN C.max_length = - 1 THEN 'max' ELSE CAST(C.max_length AS varchar(10)) END + ')'
						+ ' COLLATE ' + @new_collation
						+ CASE WHEN C.is_nullable = 0 THEN ' NOT NULL' ELSE '' END
		END
 
FROM	CTE AS C
 
PRINT @sql

@++

[Kropernic]

Woaw !!

Un tout grand merci pour ce scipt !!!

Je file cela à mon collègue pour qu'il teste sur sa machine de suite.

Merci !!!

[elsuket]

Avec plaisir

Vérifiez bien les instruction avant d'exécuter le script généré
J'ai exclu les colonnes calculées, mais il est possible que j'aie oublié d'autres choses.
Comme je te l'ai dit, exécutez le script sur une base de données qui n'est pas une base de données de production, ni une base de données que vous utilisez régulièrement pour faire des tests !

@++

[Kropernic]

Oui oui bien sûr. Cela va de soi !

[Kropernic]

Voilà, je viens de tester.

Et ça ne marche pas .

Voici les détails.

J'ai donc c/c votre code dans une nouvelle requête et j'ai remplacé les 3 premières lignes comme suit :

Code :

1
2
3
DECLARE @sql varchar(max)
	, @old_collation sysname = 'French_CI_AS'
	, @new_collation sysname = 'Latin1_General_CI_AS'

Ensuite je tente de vérifier le code pour voir si je peux l'exécuter (CTRL+F5) et j'obtiens les messages suivants :

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 21
Must declare the scalar variable "@old_collation".
Msg 137, Level 15, State 2, Line 28
Must declare the scalar variable "@new_collation".

Les variables sont pourtant bien déclarées dans les 2e et 3e ligne du code. Je ne comprends pas...

[iberserk]

Vous êtes en SQL SERVER 2005...


Vous ne pouvez donner de valeur par défaut directement (seulement depuis le 2008):

Code :

1
2
3
4
5
6
 
DECLARE @sql varchar(max)
DECLARE @old_collation sysname 
DECLARE @new_collation sysname 
SET @new_collation= 'Latin1_General_CI_AS'
SET @old_collation= 'French_CI_AS'

[Kropernic]

Jamais je n'aurai trouvé ce genre de détails...

Je sens que je vais demander un upgrade de sql server à mon chef ^^

Ca "compile", ça tourne .

Par contre, est-il possible que cela "n'aille pas jusqu'au bout" ?

Je me pose cette question car la dernière ligne est :

ALTER TABLE dbo.TbFo_Group

Je suis un novice en SQL mais j'ai quand même l'impression que cet ordre est incomplet...

[elsuket]

J'ai compris : c'est le PRINT qui est limité à 4000 caractères.

Voici le batch corrigé pour tout afficher :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
SET NOCOUNT ON
GO
 
DECLARE @sql varchar(max)	
	, @old_collation sysname 
	, @new_collation sysname
	, @indice int
 
SELECT	@new_collation = 'Latin1_General_CI_AS'
	, @old_collation = 'French_CI_AS'
	, @indice = 1
 
;WITH
	CTE AS
	(
		SELECT		S.name + '.' + T.name AS table_name
				, C.name AS column_name
				, TY.name AS data_type_name
				, CASE WHEN TY.name IN ('nchar', 'nvarchar') THEN C.max_length / 2 ELSE C.max_length END AS max_length
				, C.is_nullable
		FROM		sys.schemas AS S
		INNER JOIN	sys.TABLES AS T
					ON S.schema_id = T.schema_id
		INNER JOIN	sys.COLUMNS AS C
					ON T.object_id = C.object_id
		INNER JOIN	sys.types AS TY
					ON TY.system_type_id = C.system_type_id
					AND TY.system_type_id = TY.user_type_id
		WHERE		C.collation_name = @old_collation
		AND		C.is_computed = 0
	)
SELECT	IDENTITY(int, 1, 1) AS indice
	, 'ALTER TABLE ' + table_name 
	+ ' ALTER COLUMN ' + column_name + ' ' + data_type_name
	+ '(' + CASE WHEN max_length = - 1 THEN 'max' ELSE CAST(max_length AS varchar(10)) END + ')'
	+ ' COLLATE ' + @new_collation
	+ CASE WHEN is_nullable = 0 THEN ' NOT NULL' ELSE '' END
	+ CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) AS sql_stmt
INTO	#SQL_STMT
FROM	CTE
 
WHILE @indice <= SCOPE_IDENTITY()
BEGIN
	SELECT	@sql = sql_stmt
	FROM	#SQL_STMT
	WHERE	indice = @indice
 
	PRINT	@sql
	-- EXEC (@sql)
 
	SET @indice = @indice + 1
END
GO
 
DROP TABLE #SQL_STMT

Dé-commentez le EXEC (@sql) pour exécuter l'instruction.

@++

[Kropernic]

Je vais tester cela de suite.

Sinon une question déjà.

Pour affecter une valeur à une variable, quelle différence entre utilisé l'instruction SET et l'instruction SELECT comme vous le faites ici ?

EDIT :

ET voilà les première ligne de ce qui est visible dans l'onglet message

ALTER TABLE dbo.TbSieges ALTER COLUMN Siege char(3) COLLATE Latin1_General_CI_AS NOT NULL
GO
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
ALTER TABLE dbo.TbFormations ALTER COLUMN ObjectifFr text(16) COLLATE Latin1_General_CI_AS
GO
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
ALTER TABLE dbo.TbFormations ALTER COLUMN ObjectifNl text(16) COLLATE Latin1_General_CI_AS
GO
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
ALTER TABLE dbo.TbFo_Participants ALTER COLUMN Datemodif char(10) COLLATE Latin1_General_CI_AS
GO
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.

Je n'ai aucune idée de la faute de syntaxe qu'il pourrait y avoir :-/
Faut-il un point-virgule à la fin de chaque ordre ALTER ?

[elsuket]

Pour affecter une valeur à une variable, quelle différence entre utilisé l'instruction SET et l'instruction SELECT comme vous le faites ici ?

Aucune, si la valeur ne provient pas d'une requête.

ET voilà les première ligne de ce qui est visible dans l'onglet message

GO n'est pas une instruction T-SQL, c'est une instruction qui permet de forcer l'ordre d'exécution à SSMS (ou à l'utilitaire SQLCMD).

Je viens de faire un test sur mon PC et tout se passe normalement.
C'est une question qui va peut-être vous paraître stupide, mais est-ce que vous avez copié le code généré par le script que je vous donné (qui apparaît dans la console SSMS) dans une nouvelle fenêtre de requête ?

@++

[elsuket]

Effectivement l'EXEC n'aime pas le GO

Supprimez-le, puis générez vous le code.
Ensuite copiez tout ce code dans une nouvelle fenêtre de requête, et exécutez-le.

Désolé, je n'ai pas testé ce cas-là ...

@++

[Kropernic]

Il n'est pas nécessaire de vous excuxer !! C'est déjà bien gentil de vous occuper de mon cas.

J'ai donc commenté l'instruction exec, fait touner le code pour générer le code et coller le code généré dans une nouvelle query pour l'exécuter.

Cela semble avoir fonctionner correctement mis à part pour quelques colonnes que je traiterai manuellement.

Pour info, voici les messages après exécution

Msg 5074, Level 16, State 1, Line 1
The object 'PK_TbSieges' is dependent on column 'Siege'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Siege failed because one or more objects access this column.
Msg 2716, Level 16, State 1, Line 1
Column, parameter, or variable #0: Cannot specify a column width on data type text.
Msg 2716, Level 16, State 1, Line 1
Column, parameter, or variable #0: Cannot specify a column width on data type text.
Msg 5074, Level 16, State 1, Line 1
The index 'IX_TbFonctionsPers' is dependent on column 'CodeFonction'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN CodeFonction failed because one or more objects access this column.

Par contre, une question me taraude. Comment être sûr que, dans le code généré, il y a bien tout ce qu'il faut. Vu qu'apparemment il est limité à 4000 caractères, comment savoir si j'ai atteint la limite et que ça s'arrête justement juste après un go et que du coup, ça parait complet ?

Bien à vous,

Griftou.

[elsuket]

Par contre, une question me taraude. Comment être sûr que, dans le code généré, il y a bien tout ce qu'il faut. Vu qu'apparemment il est limité à 4000 caractères, comment savoir si j'ai atteint la limite et que ça s'arrête justement juste après un go et que du coup, ça parait complet ?

C'est le cas dans le premier script que je vous ai donnée, parce que je cumulais les instructions sql dans la même variable.

Le second script corrige ce problème puisque la valeur de la variable est écrasée par l'instruction SQL qui est calculée à chaque fois pour une colonne dont la collation est à changer.
Il vous faut donc n'utiliser que le second script, sans l'instruction EXEC, ce qui donne :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
SET NOCOUNT ON
GO
 
DECLARE @sql varchar(max)	
	, @old_collation sysname 
	, @new_collation sysname
	, @indice int
 
SELECT	@new_collation = 'Latin1_General_CI_AS'
	, @old_collation = 'French_CI_AS'
	, @indice = 1
 
;WITH
	CTE AS
	(
		SELECT		S.name + '.' + T.name AS table_name
				, C.name AS column_name
				, TY.name AS data_type_name
				, CASE WHEN TY.name IN ('nchar', 'nvarchar') THEN C.max_length / 2 ELSE C.max_length END AS max_length
				, C.is_nullable
		FROM		sys.schemas AS S
		INNER JOIN	sys.TABLES AS T
					ON S.schema_id = T.schema_id
		INNER JOIN	sys.COLUMNS AS C
					ON T.object_id = C.object_id
		INNER JOIN	sys.types AS TY
					ON TY.system_type_id = C.system_type_id
					AND TY.system_type_id = TY.user_type_id
		WHERE		C.collation_name = @old_collation
		AND		C.is_computed = 0
	)
SELECT	IDENTITY(int, 1, 1) AS indice
	, 'ALTER TABLE ' + table_name 
	+ ' ALTER COLUMN ' + column_name + ' ' + data_type_name
	+ '(' + CASE WHEN max_length = - 1 THEN 'max' ELSE CAST(max_length AS varchar(10)) END + ')'
	+ ' COLLATE ' + @new_collation
	+ CASE WHEN is_nullable = 0 THEN ' NOT NULL' ELSE '' END
	+ CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) AS sql_stmt
INTO	#SQL_STMT
FROM	CTE
 
WHILE @indice <= SCOPE_IDENTITY()
BEGIN
	SELECT	@sql = sql_stmt
	FROM	#SQL_STMT
	WHERE	indice = @indice
 
	PRINT	@sql
 
	SET @indice = @indice + 1
END
GO
 
DROP TABLE #SQL_STMT

Cannot specify a column width on data type text.

Pour les colonnes de type text (tout comme ntext et image), ce type de données est obsolète depuis SQL Server 2005.
Si vous êtes sont sous SQL Server 2005 ou 2008, je vous conseille vivement de changer le type de ces colonnes :

- pour text, à varchar(max)
- pour ntext, à nvarchar(max)
- pour image, à varbinary(max)

@++

[Kropernic]

Je changerai le type de ces colonnes.

Par contre pour les autres, comme par exemple la colonne siege de la table tbSieges qui en est la clé primaire, comment puis changer la collation ?

Je pensais le faire manuellement en allant dans la table en mode design mais il n'y a pas de propriété pour faire cela.

Le seul endroit où je peux voir la collation est en faisant un clic droit et en allant dans Properties puis dans Extended Properties. Là je peux voir la collation mais impossible de la changer...

Faut-il que je supprime la clé primaire pour refaire tourner le script à nouveau ou y a-t-il une méthode "moins lourde" pour réaliser cela ?

Griftou.