Unable to synch db objs differing only by CREATE case
Bogdan Iosif
Posts: 6
Hi,
I am using SQL Toolkit 5.2 to perform synch between two dbs hosted in case sensitive servers.
I have some stored procs and functions in the two dbs that differ only by the CREATE statement case, like this:
Reference db: "create procedure [dbo].[uspLoan_ContractComputeCommission] ....."
Target db: "CREATE procedure [dbo].[uspLoan_ContractComputeCommission] ....."
The DDL synch engine will correctly detect that the two objs are different and will issue an "ALTER procedure" statement. This however is incorrect because "ALTER procedure" will not modify the CREATE statement case.
The consequence is that every time I try to synch the two databases I get a few slightly different objects, I run the synch scripts and the databases are still detected as being different.
Any thoughts on how to avoid this problem? The case sensitivity option is mandatory so deactivating it can't be a solution.
Thank you,
Bogdan Iosif
I am using SQL Toolkit 5.2 to perform synch between two dbs hosted in case sensitive servers.
I have some stored procs and functions in the two dbs that differ only by the CREATE statement case, like this:
Reference db: "create procedure [dbo].[uspLoan_ContractComputeCommission] ....."
Target db: "CREATE procedure [dbo].[uspLoan_ContractComputeCommission] ....."
The DDL synch engine will correctly detect that the two objs are different and will issue an "ALTER procedure" statement. This however is incorrect because "ALTER procedure" will not modify the CREATE statement case.
The consequence is that every time I try to synch the two databases I get a few slightly different objects, I run the synch scripts and the databases are still detected as being different.
Any thoughts on how to avoid this problem? The case sensitivity option is mandatory so deactivating it can't be a solution.
Thank you,
Bogdan Iosif
Comments
The only way I could see this happening is if you use the 'treat items as case-sensitive' option. By default, SQL Compare will not treat CREATE PROCEDURE differently from create PRCEDURE.
I'm afraid it's an all-or-nothing proposition with stored procedure code in SQL Compare. You either have to compare the code case-sensitively or not. There is no option to ignore parts of the code.
Thank you for your help.
I understand the fact that there is no option for partial code comparison using case sensitivity/insensitivity and I never expected this behaviour.
Please, let me explain one more time the problem I encountered.
Step 1. In SQL Compare, select two dbs for comparison & synch. They have some different objects, amongst these being a few that I will call FunkyDbObjs. The reason for the name is this: The objects themselves are identical but the SQL script for the objects is different only by the case of the CREATE statement. SQL Server stores in the syscomments table this object definition for any object currently in the server.
For example:
In the REFERENCEDB's syscomments table, the definition for a stored procedure is:
"CREATE procedure [dbo].[uspLoan_ContractComputeCommission] ....."
In the TARGETDB's syscomments table, the definition for the same stored procedure is:
"creATE procedure [dbo].[uspLoan_ContractComputeCommission] ....."
The two stored procedures are otherwise identical.
Step 2. In SQL Compare, check the case-sensitivity option for comparison, with every other option remaining at it's default value.
Step 3. Compare the two databases. SQL Compare will correctly identify that the FunkyDbObjs are different in the two databases, based on their different by case, creATE/CREATE statements, extracted from the syscomments definition tables.
Step 4. Run the synch wizard. SQL Compare generates the synchronization script for the FunkyDbObjs. This script will contain ALTER PROCEDURE statements to modify the FunkyDbObjs in the target db.
-- Here comes the SQL Compare problem --
When ALTER PROCEDURE is executed, only the body of the FunkyDbObjs will be refreshed in the TARGETDB's syscomments table, leaving the creATE statement with the old case. For me, this will raise an endless loop problem because the creATE statement was not updated in the TARGETDB so that it would match the case from the REFERENCEDB, leaving the FunkyDbObjs definition case-wise different, in the syscomments table. This will lead to the objects being detected as different by SQL Compare during the next comparison, even though they have just been synchronized!
Of course, this problem will only arise when synchronizing case sensitive databases, using the 'treat items as case-sensitive' option. I only have this kind of databases so for me this problem is very real.
I hope everything is clearer now.
Thank you,
Bogdan
The root of the problem as it seems to me is that SQL Compare should drop and create the procedures rather than altering them. I'll file a suggestion for that to get done.
Eddie Davis
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com