Extended Properties on Foreign Keys missing

a.higgins

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?

Anu D

Hi Andrew,

Thanks for your post!

Can you confirm the detailed version of SQL Compare you are using? (Help --> About SQL Compare)

a.higgins

I am running Version 12.0.37.3561 Professional.

a.higgins

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

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!

mattchandler

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

a.higgins

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

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.

mattchandler

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.