topic Re: Azure Sql with different collation in Data Management Questions

Azure Sql with different collation

ModyBuchbinder — Thu, 09 Jan 2020 14:31:34 GMT

Hi All

We would like to create a Geodatabase in Azure SQL

The problem is that we would like to have different collation then the default.

We cannot use Create Enterprise Geodatabase because you get the default collation.

We created a database with our collation and tried to use Enable Geodatabase

we got this error:

Failed to create stored procedures in the geodatabase. Error (-51)

DBMS error code: 468

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot resolve the collation conflict between "Hebrew_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation

Any ideas?

Thanks

Re: Azure Sql with different collation

ModyBuchbinder — Tue, 14 Jan 2020 07:28:36 GMT

It looks like the Sql in Azure have some kind of UTF8 collation so it support other Languages even when it set to Latin.

We used the default and we have no problem with Hebrew

Re: Azure Sql with different collation

ModyBuchbinder — Sun, 22 Mar 2020 09:15:05 GMT

After working with the geodatabase we found that Hebrew is displayed but query fail.

You can add an Upper case N to the string in the query to make it works but some other clients that does not give an option to "build" a query (such as Dashboard) fail.

If anybody have any idea it will be great!

Thanks

Re: Azure Sql with different collation

Roshan_MarkSingh_Matharu — Mon, 06 Apr 2020 21:04:42 GMT

Hello Mody Buchbinder,

Please try the following steps for a potential workaround:

Create a new database from SQL Server Management Studio under a SQL Server Instance with a Hebrew_CI_AS collation. Change the default Collation from (SQL_Latin1_General_CP1_CI_AS to Hebrew_CI_AS) within the database properties options in Management Studio > Once created, enable that database as an enterprise geodatabase from ArcGIS Pro or ArcMap running the "Enable Enterprise Geodatabase Geoprocessing Tool" > From SQL Server Management Studio restore the enabled geodatabase with Hebrew Collation from the SQL Server instance to an Azure SQL instance referencing workflow from the link below
https://stackoverflow.com/questions/33436344/restoring-sql-server-backup-to-azure-sql-database https://stackoverflow.com/questions/33436344/restoring-sql-server-backup-to-azure-sql-database
Based on the workflow from the link above, after successfully deploying the existing geodatabase from a SQL Server instance with the preferred collation (Hebrew_CI_AS) to an existing Azure instance > connect to it using direct connection via ArcCatalog from Pro or ArcMap.
(Make sure to remove the Windows Authentication user name from the Security settings of the SQL database from Management Studio before proceeding with the deployment to Azure SQL)
https://github.com/Microsoft/azuredatastudio/issues/3337 https://github.com/Microsoft/azuredatastudio/issues/3337

Hope this helps!!

Kind regards,

Mark

Re: Azure Sql with different collation

Roshan_MarkSingh_Matharu — Mon, 09 Dec 2024 15:19:59 GMT

This issue has been resolved after a defect was logged for it.