Problems with case sensitivity varchar comparisons
david.duffett
Posts: 30
For a while now the scripts that have been generated comparing data between my dev and test databases have always included the following:
-- Drop constraints from [dbo].[tbTask_Class_Setting]
ALTER TABLE [dbo].[tbTask_Class_Setting] DROP CONSTRAINT [FK_tbTask_Class_Setting_tbTask_Class]
-- Update row in [dbo].[tbTask_Class_Setting]
-- Add constraints to [dbo].[tbTask_Class_Setting]
ALTER TABLE [dbo].[tbTask_Class_Setting] ADD CONSTRAINT [FK_tbTask_Class_Setting_tbTask_Class] FOREIGN KEY ([task_class_id]) REFERENCES [dbo].[tbTask_Class] ([task_class_id]) ON DELETE CASCADE
Notice that there is no statement under the "-- Update row" comment?
Running the comparison in the SQL Data Compare application shows that there is a varchar field where the value is the same except for the letter casing. It seems SQL Data Compare picks this up as a change, but then fails to script the actual update!
-- Drop constraints from [dbo].[tbTask_Class_Setting]
ALTER TABLE [dbo].[tbTask_Class_Setting] DROP CONSTRAINT [FK_tbTask_Class_Setting_tbTask_Class]
-- Update row in [dbo].[tbTask_Class_Setting]
-- Add constraints to [dbo].[tbTask_Class_Setting]
ALTER TABLE [dbo].[tbTask_Class_Setting] ADD CONSTRAINT [FK_tbTask_Class_Setting_tbTask_Class] FOREIGN KEY ([task_class_id]) REFERENCES [dbo].[tbTask_Class] ([task_class_id]) ON DELETE CASCADE
Notice that there is no statement under the "-- Update row" comment?
Running the comparison in the SQL Data Compare application shows that there is a varchar field where the value is the same except for the letter casing. It seems SQL Data Compare picks this up as a change, but then fails to script the actual update!
Comments
But it's good to know what is actually happening there, thanks for pointing that out.