How to Change Collation of an Azure SQL Managed Instance When There Are Dependency Errors
Published Feb 03 2024 11:31 PM 2,465 Views
Microsoft

In this article, I will explain how to change the collation for your Azure SQL Managed Instance when the following scenario is encountered:

 

You try to change the database collation with the following T-SQL:

 

 

 

USE [master]
GO
ALTER DATABASE [testdb] COLLATE SQL_Latin1_General_CP1_CS_AS
GO

 

 

 

And it fails with the following message:

 

 

 

Msg 5075, Level 16, State 1, Line 24

The column 'tablex.colx' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

 

 

           

Steps in brief:

 

  • Export the database to bacpac using SQLPackage.
  • Modify the collation by editing the model.xml file.
  • Import the database using SQLPackage and overriding the model.xml path.
  • Change the database name or modify app connection string to use the new database.

 

Considerations:

 

Steps in details:

 

  1. Start the maintenance window for your application.
  1. Export the database using SQLPackage:

 

sqlpackage.exe /Action:Export /ssn:tcp:<ServerName>.database.windows.net,1433 /sdn:<DatabaseName> /su:<UserName> /sp:<Password> /tf:<TargetFile>

 

 

1.png

  1. Rename the *.bacpac file to *.zip so you can open it with the file explorer:

6.png

 

  1. Copy the model.xml to a local folder “C:\Temp\model.xml”.
  1. Edit the “C:\Temp\model.xml” with the desired collation and save the file.

 

For example:

 <Property Name="Collation" Value="Thai_CI_AS" />

 

3.png

  1. Rename the file extension modified in step 3 from *.zip back to *.bacpac.
  2.  Run the import using sqlpackage.exe. Use the flag /ModelFilePath:C:\Temp\model.xml to override the model.xml in the .bacpac file.

 

sqlpackage.exe /Action:Import /tsn:<server>.database.windows.net /tdn:<database> /tu:<user> /tp:<password> /sf:"C:\Temp\database.bacpac" /ModelFilePath:<path>model.xml

 

4.png

 

  1. When the import operation is completed, change the database name, or modify the application connection string to use the new database name. You can also check the new collation setting in the database properties using SSMS:

5.png

  1. Stop the maintenance window for your application and run the workload. Ensure that the application is working as expected.

 

 

Co-Authors
Version history
Last update:
‎Feb 02 2024 06:48 AM
Updated by: