4

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?

Improve this question
1
  • What might help in this case is to run SQL Profiler and capture the query.
    – Nick.Mc
    Commented Jan 16, 2017 at 13:45

3 Answers 3

Reset to default
3

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).

Improve this answer
5
  • 1
    Thank you for your answer, srutzky. After discussing with our dev team, we decided to just delete the catalogue, as they had very few packages and figured it would be easier to just re-deploy (the server was not quite in production yet anyway).
    – newguise
    Commented Jan 16, 2017 at 23:15
  • 1
    Also, although we didn't follow any of your suggestions, I've marked your answer as accepted as it may be useful for someone for whom deleting the catalogue is not an option.
    – newguise
    Commented Jan 16, 2017 at 23:22
  • @newguise Actually, if you dropped and recreated SSISDB, then you did follow my suggestion, as that is bullet-point #2 :-).
    – Solomon Rutzky
    Commented Jan 17, 2017 at 2:28
  • Ah, thanks, I see what you mean. I suppose because we deleted the catalogue rather than explicitly doing anything directly to the database, I didn't make the connection.
    – newguise
    Commented Jan 19, 2017 at 7:16
  • @newguise I just updated to add in the "catalog" terminology so that it wi hopefully be less confusing to others :)
    – Solomon Rutzky
    Commented Jan 19, 2017 at 14:36
0

Resetting the collation of the SQL Server, as you did, should have changed the system databases master,model, msdb, and tempdb. See the instructions at:

Rebuild System Databases

However, your error indicates that you are still using the SQL_Latin1_General_CP1_CI_AS collation.

If you expected that changing the system collation would change the user database collations, then that is likely your problem. The system collation change does not automatically cascade into user databases. (Which is reasonable for an environment handling many collations.)

Set or Change the Database Collation offers you a detail that may have been overlooked.

You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the ALTER TABLE clause to change the collation.

ALTER TABLE (Transact-SQL) You will need to alter the columns in the user databases in order to change them. You can alter a column to a particular collation using the ALTER TABLE ... ALTER COLUMN ... COLLATE clauses as described in ALTER TABLE. And, of course, you can cycle through the columns to change all their collations, if that is needed.

Note the caution on ALTER COLUMN:

If the COLLATE clause is not specified, changing the data type of a column will cause a collation change to the default collation of the database.

Improve this answer
3
  • Thanks for your answer, RLF. As per srutzky's answer, because I used the -q method, the change did propagate to the user databases too, including SSISDB. It also appeared that the collation was changed on the tables, although am not sure about individual columns, as I didn't check this before deleting the catalogue (which is the action we decided to take).
    – newguise
    Commented Jan 16, 2017 at 23:12
  • @newguise re: your statement of, "It also appeared that the collation was changed on the tables, although am not sure about individual columns": Tables do not have a Collation, only the columns do. And the error you reported in the question was due to a column collation being different now.
    – Solomon Rutzky
    Commented Jan 16, 2017 at 23:31
  • Thank you again srutzky, as you may be able to tell, I am not that familiar with MS SQL. I was looking at the collation value under the Extended Properties for a table. I assume that simply reflects the database collation setting.
    – newguise
    Commented Jan 17, 2017 at 0:57
0

Had the same issue with SSISDB after the collation change. Finally after reading the @Solomon's answer we did it in 5 steps:

  1. we backed up SSISDB
  2. stopped SQL Server
  3. issued "sqlservr -c -m -T4022 -T3659 -s"{instance_name}" -q"{new_collation_name}"
  4. started SQL Server
  5. restored SSISDB
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.