Extended Properties on Foreign Keys missing

a.higginsa.higgins Posts: 62 Bronze 1
edited January 19, 2017 8:49AM in SQL Compare
When I make a deployment using SQL Compare to a parent table with foreign keys pointing at it, the foreign keys are often dropped and recreated.

Unfortunately, any extended properties on those foreign keys are not recreated as part of the deployment process, and I then have to issue a second deployment script to add the extended properties back in.

Can you please ensure that extended properties are recreated on any foreign keys that are dropped and recreated during a deployment?

Comments

  • Hi Andrew,

    Thanks for your post!

    Can you confirm the detailed version of SQL Compare you are using? (Help --> About SQL Compare)
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: [email protected]
  • a.higginsa.higgins Posts: 62 Bronze 1
    I am running Version 12.0.37.3561 Professional.
  • a.higginsa.higgins Posts: 62 Bronze 1
    A few more details:

    This morning I used SQL Compare to do a Production deployment changing a table called DimCourse. The table had to be rebuilt due to a change in column order caused by the addition of a new column.

    There are four different foreign keys that point to DimCourse as a referenced object. Before the deployment, all four of the foreign key constraints had two extended properties defined: MS_Description and FK_On_Delete_Action. QA and Prod showed no differences between those four tables.

    SQL Prompt generated a script which dropped the foreign keys from the other objects, dropped / rebuilt DimCourse, and then recreated the foreign keys on the other objects. However, the script did not also recreate the extended properties that had previously been defined on those foreign keys.


    After the deployment was complete, I reran SQL Compare and it found differences between the two environments, and wants me to use the following script to re-sync them:
    /*
    Run this script on:
    
            xxxxx    -  This database will be modified
    
    to synchronize it with:
    
            xxxxx
    
    You are recommended to back up your database before running this script
    
    Script created by SQL Compare version 12.0.37.3561 from Red Gate Software Ltd at 11/14/2016 8:59:02 AM
    
    */
    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    USE [AARDW]
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL Serializable
    GO
    BEGIN TRANSACTION
    GO
    IF @@ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Creating extended properties'
    GO
    EXEC sp_addextendedproperty N'FK_On_Delete_Action', N'Remove Row', 'SCHEMA', N'dbo', 'TABLE', N'BrgFacultyCourseQualification', 'CONSTRAINT', N'FK_FacultyCourseQualification_Course'
    GO
    IF @@ERROR <> 0 SET NOEXEC ON
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Foreign Key to DimCourse', 'SCHEMA', N'dbo', 'TABLE', N'BrgFacultyCourseQualification', 'CONSTRAINT', N'FK_FacultyCourseQualification_Course'
    GO
    IF @@ERROR <> 0 SET NOEXEC ON
    GO
    EXEC sp_addextendedproperty N'FK_On_Delete_Action', N'Remove Row', 'SCHEMA', N'dbo', 'TABLE', N'DimCourseSection', 'CONSTRAINT', N'FK_CourseSection_Course'
    GO
    IF @@ERROR <> 0 SET NOEXEC ON
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Foreign Key to DimCourse', 'SCHEMA', N'dbo', 'TABLE', N'DimCourseSection', 'CONSTRAINT', N'FK_CourseSection_Course'
    GO
    IF @@ERROR <> 0 SET NOEXEC ON
    GO
    EXEC sp_addextendedproperty N'FK_On_Delete_Action', N'Remove Row', 'SCHEMA', N'dbo', 'TABLE', N'FactPersonExternalCourse', 'CONSTRAINT', N'FK_PersonExternalCourse_Course'
    GO
    IF @@ERROR <> 0 SET NOEXEC ON
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Foreign Key to DimCourse', 'SCHEMA', N'dbo', 'TABLE', N'FactPersonExternalCourse', 'CONSTRAINT', N'FK_PersonExternalCourse_Course'
    GO
    IF @@ERROR <> 0 SET NOEXEC ON
    GO
    EXEC sp_addextendedproperty N'FK_On_Delete_Action', N'Remove Row', 'SCHEMA', N'dbo', 'TABLE', N'FactStudentCourseSection', 'CONSTRAINT', N'FK_StudentCourseSection_Course'
    GO
    IF @@ERROR <> 0 SET NOEXEC ON
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Foreign Key to DimCourse', 'SCHEMA', N'dbo', 'TABLE', N'FactStudentCourseSection', 'CONSTRAINT', N'FK_StudentCourseSection_Course'
    GO
    IF @@ERROR <> 0 SET NOEXEC ON
    GO
    COMMIT TRANSACTION
    GO
    IF @@ERROR <> 0 SET NOEXEC ON
    GO
    DECLARE @Success AS BIT
    SET @Success = 1
    SET NOEXEC OFF
    IF (@Success = 1) PRINT 'The database update succeeded'
    ELSE BEGIN
    	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    	PRINT 'The database update failed'
    END
    GO
    

    I would expect that these extended properties should be recreated as part of the initial deployment, when the foreign keys referencing the changed object are recreated.
  • a.higginsa.higgins Posts: 62 Bronze 1
    Is there any other information I can provide to help you with this? These deployment issues are wreaking havoc with our release process, and I'm very motivated to help you if you need anything!
  • Hi Andrew,

    Thanks for your post and sorry you have been running into this issue. I can reproduce this problem and see it happening. I am going to add an issue into our internal bug tracking software to fix this, but at the moment I could not say when we are likely to be able to look into it.

    In the meantime I believe that re-running your deployment would re-add the extended properties that have been lost in rebuilding foreign keys, so this may be a useful approach to try.

    Matthew Chandler
    Software Developer on SQL Compare and SQL Data Compare
    Matthew Chandler
    Software Developer on SQL Compare and SQL Data Compare
  • a.higginsa.higgins Posts: 62 Bronze 1
    OK, thank you for letting me know! Glad to hear that it's in the queue. In the meantime, I'll do the double deployment (we use a scheduled release window, so it's hard to re-compare afterwards and get the new script out in time for the change).
  • a.higginsa.higgins Posts: 62 Bronze 1
    Is there any update on this issue? It is still messing up our build and deployment process, and the DBAs are getting annoyed at the repeated release requests.
  • We are planning to investigate if we can fix this issue in the next few weeks, but I'm afraid we don't know at the moment how likely it is that we will be able to release a fix soon.
    Matthew Chandler
    Software Developer on SQL Compare and SQL Data Compare
Sign In or Register to comment.