sql server数据库排序规则转换COLLATE

数据库排序规则转换COLLATE

   SELECT 
           [database_id],
           [DBName],
           [DBDescription],
           [CreateDate],
           [RecoveryModel],
           [DataFileSizeMB],
           [LogFileSizeMB],
           [FilePath],
           [FState],
           [FAG],
		   BackupFull,
		   BackupDiff,
		   BackupPath,
		   BackupStrategy,
		   PreBackupTime
    FROM [XXXX01\XX1].[XXDB1].[dbo].[V_Metadata_DataBase]
	UNION ALL
    --新PLM实例数据库,PLM排序规则不一样 需要转换一下
    SELECT 
           [database_id],
           [DBName] COLLATE Chinese_PRC_CI_AS,
           [DBDescription] COLLATE Chinese_PRC_CI_AS,
           [CreateDate],
           [RecoveryModel] COLLATE Chinese_PRC_CI_AS,
           [DataFileSizeMB],
           [LogFileSizeMB],
           [FilePath] COLLATE Chinese_PRC_CI_AS,
           [FState],
           [FAG],
		   BackupFull,
		   BackupDiff,
		   BackupPath COLLATE Chinese_PRC_CI_AS,
		   BackupStrategy COLLATE Chinese_PRC_CI_AS,
		   PreBackupTime
    FROM [XXXX02\XX2].[XXDB2].[dbo].[V_Metadata_DataBase]

两个排序规则不一样得数据库做关联查询时，需要用COLLATE转换，否则会报错。

其中仅字符需要转换，int和datetime类型不需要（暂时只用到这3个，其他类型什么情况待测试）