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.
- 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.
Enjoy!