Extended Properties on Foreign Keys missing

• 
  Hi Andrew,
  
  Thanks for your post!
  
  Can you confirm the detailed version of SQL Compare you are using? (Help --> About SQL Compare)

  Anu D November 14, 2016 03:56

  - 0 +
• 
  I am running Version 12.0.37.3561 Professional.

  a.higgins November 14, 2016 07:56

  - 0 +
• 
  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.higgins November 14, 2016 09:02

  - 0 +
• 
  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!

  a.higgins November 22, 2016 07:52

  - 0 +
• 
  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

  mattchandler November 22, 2016 12:38

  - 0 +
• 
  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.higgins November 29, 2016 07:44

  - 0 +
• 
  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.

  a.higgins January 16, 2017 11:55

  - 0 +
• 
  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.

  mattchandler January 19, 2017 08:49

  - 0 +

Add comment

Please sign in to leave a comment.