Sql-server – SSIS Catalogue Inaccessible After Server Collation Change

collationsql serverssis

I changed the collation of a Microsoft SQL 2016 Standard server, by starting SQL server in single user admin mode and using the -q parameter to specify "Latin1_General_CI_AS" collation (previous setting was "SQL_Latin1_General_CP1_CI_AS"). Now when expanding the SSISDB Integration Services Catalogue, I get the below error.

Exception has been thrown by the target of an invocation. (mscorlib)

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot resolve collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in add operator occurring in SELECT statement column 1. (Microsoft SQL Server, Error: 451)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.4001&EvtSrc=MSSQLServer&EvtID=451&LinkId=20476

Everything else on the server seems to be OK, although the Agent XPs advanced setting got disabled, which I have corrected.

Is there a way to correct whatever is causing the collation conflict in the SSIS DB, or is the only solution to delete and recreate it?

Best Answer

Two things to consider:

  1. Changing the Collation of an Instance via the -q method changes the Collation for all Databases, not just the system DBs. ( This option is undocumented and hence, unsupported. Please see Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong? for a detailed description of what that option does, as well as the potential scope of impact )

  2. SSISDB, distributed by Microsoft, will have the same Collation for every installation since SSISDB is created from a backup file instead of a T-SQL script. There is an implication there that the Collation should probably not be changed. In addition, I found the following thread on the MSDN Forums -- SSISDB collation default: how to change? -- in which a Microsoft employee states:

    We do not support the changing of collation for SSISDB.

    To be fair, it is also possible that a T-SQL install script could specify the Collation for the SSISDB Database, which would then have the same implication of it probably being best to keep that Collation. But, while a T-SQL install script can go either way, a backup being restored will be the Collation that the Database was when it was backed-up (hence the problem that sometimes occurs when restoring something like MSDB from an Instance that had a different default Collation: Example #1 and Example #2).

With those two points in mind:

  • You can try to change the Database and columns back to SQL_Latin1_General_CP1_CI_AS via ALTER DATABASE [SSISDB] COLLATE SQL_Latin1_General_CP1_CI_AS and then several ALTER TABLE ... ALTER COLUMN statements.

    PRO: Existing data is not lost, with no need to export / import.

    CON: You could miss one or more columns and the effect(s) might be hard to detect. While there are very few differences between those two Collations, they do not behave exactly the same, and I am not sure what expectations are in the code based on expecting the SQL_Latin1_General_CP1_CI_AS Collation. I'm not sure that I would be comfortable with this, mainly due to lack of certainty.

  • You can drop and recreate SSISDB (i.e. delete and re-deploy the catalog). If you have existing packages defined in there, you can export the data to import after the DB is recreated.

    PRO: Know for sure that the issue is fixed.

    CON: Extra work in exporting / importing data (and getting all tables, and in the correct order due to FKs).

Related Question