Bug in UDT change script
jstangroome
Posts: 26
Hello,
I have two copies of a database. I make changes in one and when I have done some testing and am happy with them I use SQL Compare 5.3 to replicate the changes to the other copy.
I have a table 'Master' with a column 'Code' defined as a UDT 'MyType' varchar(16). I also have a scalar UDF called 'BuildCode' which takes some inputs and returns a 'MyType'. Lastly I have created a CHECK constraint on Master.Code to ensure it equals BuildCode(Master.OtherField).
I recently changed the REFERENCE permissions on the MyType UDT and I am trying to synchronise the two databases. SQL Compare has generated a change script to do the following:
1. Drop the UDF 'BuildCode'
2. Alter the type of Master.Code to varchar(16) instead of 'MyType'.
3. Drop UDT 'MyType'
4. Recreate UDT 'MyType'
5. Alter the type of Master.Code back to 'MyType'
6. Recreate the UDF 'BuildCode'
7. Alter permissions on 'MyType'
Unfortunately, the script fails at step 1 because BuildCode is referenced by Master.Code's CHECK constraint. I think SQL Compare should have dropped the CHECK Constraint before step 1 and recreated it after step 6.
Can you confirm whether my assumptions are right and lete me known if there are any known solutions?
Thanks,
- Jason
I have two copies of a database. I make changes in one and when I have done some testing and am happy with them I use SQL Compare 5.3 to replicate the changes to the other copy.
I have a table 'Master' with a column 'Code' defined as a UDT 'MyType' varchar(16). I also have a scalar UDF called 'BuildCode' which takes some inputs and returns a 'MyType'. Lastly I have created a CHECK constraint on Master.Code to ensure it equals BuildCode(Master.OtherField).
I recently changed the REFERENCE permissions on the MyType UDT and I am trying to synchronise the two databases. SQL Compare has generated a change script to do the following:
1. Drop the UDF 'BuildCode'
2. Alter the type of Master.Code to varchar(16) instead of 'MyType'.
3. Drop UDT 'MyType'
4. Recreate UDT 'MyType'
5. Alter the type of Master.Code back to 'MyType'
6. Recreate the UDF 'BuildCode'
7. Alter permissions on 'MyType'
Unfortunately, the script fails at step 1 because BuildCode is referenced by Master.Code's CHECK constraint. I think SQL Compare should have dropped the CHECK Constraint before step 1 and recreated it after step 6.
Can you confirm whether my assumptions are right and lete me known if there are any known solutions?
Thanks,
- Jason
