Lesson Learned #97: Which is the server collation in Azure SQL Database?

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, I worked on a case that our customer reported that importing a bacpac they got a duplicate object name, however, if they import the bacpac in a SQL Server OnPremise the process is completed without issues but Azure SQL Database they faced the following error:  Import fails 'Error SQL72014: Procedure ExampleFunction, Line 3 There is already an object named 'ExampleFunction' in the database'.

 

I'm asking why this difference and following I would like to share with you my lessons learned here:

 

  • I changed the extension of the bacpac file to zip and I found that, indeed, we have two procedures with same name but one is written Examplefunction and another one is ExampleFunction.
  • In the model.xml of this bacpac file I found that the collation of the database is LATIN1_GENERAL_BIN that is case sensitive and after asking to our customer we found that the server configuration iss indeed LATIN1_GENERAL_BIN so, this is the reason, that our customer is not getting any error having two procedures with same name but written different.
  • In fact, running the query select * from sys.columns, I found that the system table sysschobjs that is managing the name is LATIN1_GENERAL_BIN.

 

database1.jpg

 

  • But, what is happening with Azure SQL Database?
    • First, I checked the collation of Azure SQL Database and I found that the collation of the database is indeed, LATIN1_GENERAL_BIN.
    • Second, I checked the collation of Azure SQL Server and I found that the collation of the server is SQL_Latin1_General_CP1_CI_AS that is case insensitive and for this error our customer is facing this error. Unfortunately, this definition is by design and nowadays it is not possible to change.

 

database2.jpg

 

Enjoy!

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.