将数据库导出到bacpac文件时，Azure SQL会为系统视图列设置错误的排序规则。 [英] Azure SQL sets wrong collation for system view columns when exporting database to bacpac file.

问题描述

所以我昨天问了一个关于在sys.object视图上加入的视图的Collat​​ion冲突的问题。 数据库是从Azure导出的，我发现两个sys.object列的排序规则设置已经设置为
而不是默认的数据库排序规则。 通过在视图中显式使用COLLATE语句使sys.object列兼容，我使视图工作。

因为我终于知道我的问题的解决方案，所以我去了Azure我导出的数据库，并尝试使用这些COLLATE语句更新相同的视图。 但是，当我尝试保存视图时，COLLATE语句已被删除。 
我无法导出bacpac文件，当我尝试在本地实例中导入时，该文件只会导致错误。 如何阻止Azure为系统视图设置错误的排序规则？ 这似乎真的不应该是一个问题，
但它是。

解决方案

你好，

所有Azure SQL基础结构上的默认排序规则是SQL_Latin1_General_CP1_CI_AS。您无法更改服务器和数据库
排序规则，但您可以在列级别设置不同的排序规则。

您能否提供sys对象的名称，在该对象中找到除默认值之外的其他排序规则？我想验证我的Azure SQL数据库上有这种情况。我已导出许多数据库没有问题，但目标SQL
服务器实例将在大多数情况下恢复，默认情况下由SQL Server安装程序分配排序规则。

< p style ="line-height：normal; margin-bottom：0pt">

哪个排序规则正在使用您的SQL Server实例？

希望这会有所帮助。

Re gards，

Alberto Morillo

SQLCoffee.com

So I asked a question yesterday about a Collation conflict on a view that was being joined on a sys.object view.  The database was exported from Azure, and I came to discover that the collation setting for two sys.object columns had been set to something other than the default database collation.  By using the COLLATE statement explicitly in the view to make the sys.object columns compatible, I made the view work.

Because I finally knew the solution to my problem, I went to the Azure Database from which I export, and attempted to update the same view with these COLLATE statements.  However, when I tried to save the view, the COLLATE statements were removed.  I cannot export a bacpac file that will only result in a error when I attempt to import it in a local instance.  How do I prevent Azure from setting the wrong collation for system views?  This seems like something that really shouldn't be a problem, yet it is.

解决方案

Hello,

The default collation on all Azure SQL infrastructure is SQL_Latin1_General_CP1_CI_AS. You cannot change server and database collation, but you can set different collation at the column level.

Could you please give the name of the sys object where you found a different collation other than the default? I would like to verify that I have that scenario on my Azure SQL Databases. I have exported many databases with no issues but the destination SQL Server instance where will be restored most of the times have the collation assigned by default by SQL Server setup.

Which collation is using your SQL Server instance?

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

这篇关于将数据库导出到bacpac文件时，Azure SQL会为系统视图列设置错误的排序规则。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！