Dependent foreign keys affected by Options

clamk123

I've found a very very strange issue.

Some background, we use Redgate heavily in our enterprise for deployments. Due to the nature of how we do deployments we compare a source folder to a target folder and generate a .sql script based on the differences between the two. Easy stuff. No different that using the UI (but we use command line to do it).

What happens is, if you rename a primary key in the source, it does not reflect the dependency change in the script for the dependent foreign keys. SqlCompare's output script drops and recreates the primary key to rename it, but doesn't not correspondingly drop and rebuild the dependent foreign keys on tables with a relationship, of course this causes the script to fail.

Now if we compare between two databases, this problem doesn't occur. It only occurs between to script folders, in which it doesn't appear to recognize the foreign key relationships. This occurs regardless or using the option to include dependencies or not.

So what does this have to do with the SDK? That's an interesting part.

I'm rewriting our system to not use the UI or CommandLine, but to do it all programmatically. When I do the same thing the UI would do (register the source and target folders, do the comparison, etc) it outputs the exact same script, with the issue of not seeing the foreign key relationships if we rename a primary key (which it interprets as dropping the old one and creating a new one I suppose). Except there's a situation where it does create the dependencies correctly.

If, in code, I include as an option:

Options options = Options.ForceSyncScriptGeneration; //other options removed for clarity

..and code like...

Work work = new Work();
work.BuildFromDifferences(differences, options, true);

It suddenly does see the foreign key relationships when renaming a primary key, and does drop the dependent foreign keys and recreates them after the new primary key is created in the script.

This, of course, doesn't make any sense to me. But it is causing a huge issue with our deployment script generation and we've had to drop back to RG 8 or 9 for our output scripts. This has been tested on SqlCompare 10.2.3.1. Sadly our commandline and UI tool does not include an option called "ForceSyncScriptGeneration" in a documented or undocumented form (unless that option flag was renamed). There is an element in the .SCP xml files we use for our command line where you could set the options integer value where maybe the flag could be set in there.

So if this is a known problem (with an unknown fix) I hope this helps from an SDK perspective. If this issue (foreign key dependencies ignored on primary key renaming) has some other resolution in the meantime for the UI or Commandline methods of using SQL Compare I'd love to know it.

Thanks in advance.

Brian Donahue

The details about the option "Options.ForceSyncScriptGeneration" are pretty thin on the ground - it seems this option is to force the SQL Compare Engine to create a synchronization script rather than update a collection of static files in a scripts folder - at any rate if the output is a sync script, they scripts should theoretically be the same whether you use this option or not...

If you have dependency problems, though, the best thing to check is that you set Options.IncludeDependencies, and that you use this setting consistently wherever in your code that Options are used.

clamk123

I think that the ForceSyncScriptGeneration flag isn't being hit in the UI/Commandline interfaces due to a bug, and therefore is causing this problem.

Here's how you can easily replicate it to understand more what the problem is.

1. Create the two below tables in scripts in a folder called souce.

CREATE TABLE [dbo].[tblTableP]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[value] [varchar] (15) NULL
) 
GO
ALTER TABLE [dbo].[tblTableP] ADD CONSTRAINT [pc_tblTableP] PRIMARY KEY CLUSTERED  ([id])
GO

CREATE TABLE [dbo].[tblTableC]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[value] [varchar] (15) NULL
) 
GO
ALTER TABLE [dbo].[tblTableC] ADD CONSTRAINT [pc_tblTableC] PRIMARY KEY CLUSTERED  ([id])
GO

ALTER TABLE [dbo].[tblTableC] WITH NOCHECK ADD CONSTRAINT [FK_tblTableC] FOREIGN KEY ([Id]) REFERENCES [dbo].[tblTableP] ([Id])

These scripts create a referential integrity reference between tblTableC and tblTableP.

Make a copy of this in another folder called target.

If I compare the two folders with the below code, there are no differences (which there shouldn't be).

            source.Register(@"C:\source\", db, Options.None);
            target.Register(@"C:\target\", db, Options.None);

            Differences differences = source.CompareWith(target, Options.None);

            Options options;
//these options don't matter, but removes the extra stuff.  Note that I am including dependencies, this has no effect either way.
            options = Options.NoSQLPlumbing | Options.DoNotOutputCommentHeader | Options.IncludeDependencies;

       
            Work work = new Work();

            work.BuildFromDifferences(differences, options, true);

            string s = work.ExecutionBlock.GetString();

At this point "s" will contain the output script to be looked at.

If you run it on the identical files at this point you will get an output script doing nothing (since they are the same), but if you make a slight change to the souce table tblTableP:

ALTER TABLE [dbo].[tblTableP] ADD CONSTRAINT [pc_tblTableP2] PRIMARY KEY CLUSTERED ([id]) 

...and change the NAME of the primary key constraint, we get an output change script of the below:

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
PRINT N'Dropping constraints from [dbo].[tblTableP]'
GO
ALTER TABLE [dbo].[tblTableP] DROP CONSTRAINT [pc_tblTableP]
GO
PRINT N'Altering [dbo].[tblTableP]'
GO
PRINT N'Creating primary key [pc_tblTableP2] on [dbo].[tblTableP]'
GO
ALTER TABLE [dbo].[tblTableP] ADD CONSTRAINT [pc_tblTableP2] PRIMARY KEY CLUSTERED  ([id]) ON [FG_Data]
GO

...which will of course fail. If I run this on a database that the "target" folder script represents, it will fail because it is ignoring the foreign key relationship which hasn't been dropped yet. When I look at the source and destination database objects and drill down to the foreign keys, everything is mapped correctly, but RG doesn't even see them when generating the output script.

But one change "fixes" the problem.

If I add the ForceSyncScriptGeneration flag, it fixes it correctly. Make the below change to the C# code. Notice I removed the flag to IncludeDependencies because it has no impact whatsoever.

options = Options.NoSQLPlumbing | Options.DoNotOutputCommentHeader  | Options.ForceSyncScriptGeneration;

Now when the code is run, the output script is:

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
PRINT N'Dropping foreign keys from [dbo].[tblTableC]'
GO
ALTER TABLE [dbo].[tblTableC] DROP CONSTRAINT[FK_tblTableC]
GO
PRINT N'Dropping constraints from [dbo].[tblTableP]'
GO
ALTER TABLE [dbo].[tblTableP] DROP CONSTRAINT [pc_tblTableP]
GO
PRINT N'Creating primary key [pc_tblTableP2] on [dbo].[tblTableP]'
GO
ALTER TABLE [dbo].[tblTableP] ADD CONSTRAINT [pc_tblTableP2] PRIMARY KEY CLUSTERED  ([id]) ON [FG_Data]
GO
PRINT N'Adding foreign keys to [dbo].[tblTableC]'
GO
ALTER TABLE [dbo].[tblTableC] WITH NOCHECK  ADD CONSTRAINT [FK_tblTableC] FOREIGN KEY ([id]) REFERENCES [dbo].[tblTableP] ([id])
GO

Our script is now properly seeing the foreign key constraint, dropping it, dropping the primary key, and then rebuilding them in the correct order. No error is received.

My theory is that at some point in RG10, there was a code change made to the engine that outputs scripts. In the UI, you would compare to folders in Sql Compare, and at the end you can choose to sync the folders, or generate an output script instead via radio buttons on the left hand side as the last step. Some code change does not appopriately set the flag to forcescriptsynchronization.

When doing the same test in the Sql Compare UI, and even if you tell it to create sync scripts, it won't have the foreign key references in the script.

As to why it isn't seeing it in the other cases, which it should with or without script synchronization beign set, I don't know.

This "fixes" it in code, however this code is being developed as a replacement for the UI/Commandline methods.

The gravity of the problem is this. We have lots and lots of RG licenses for versions 8, 9, and 10 that we use for various things. We (unfortunately) have to change the constraint names of various things all the time. This is clearly a bug that is causing big headaches for everyone, and having to downgrade to earlier versions, while at the same time dealing with our various enterprise environments upgrading to SQL 2012 (which is why we want to use RG10 anyway), is causing us a lot of concern.

Brian Donahue

Can you please try this SDK patch and see if it's any better?
ftp://support.red-gate.com/patches/SQL_ ... .0.138.exe

clamk123

I downloaded the patch and installed it. In my project I dropped the existing references and referenced the new components.

However when I run the code, it attempts to run the line:

work.BuildFromDifferences(differences, options, true);

and throws and exception:

System.IO.FileNotFoundException was unhandled
Message=Could not load file or assembly 'LinqBridge, Version=1.1.0.0, Culture=neutral, PublicKeyToken=c2b14eb747628076' or one of its dependencies. The system cannot find the file specified.
Source=RedGate.SQLCompare.Engine
FileName=LinqBridge, Version=1.1.0.0, Culture=neutral, PublicKeyToken=c2b14eb747628076

It's looking for some type of assembly which I don't appear to have (I referenced every assembly that was in the SDK/Assembly folder installed).

Brian Donahue

Funny I though you would have had that error in the old version, too. Just make sure you have got the LinqBridge.dll in the build output folder. That should fix it.

clamk123

I found LinqBridge.dll in a sepaate folder and referenced it, although I honestly don't recall having to reference it before.

Now when I execute it, it stopped the ability for ForceScriptSynchronization to work. Whether I include it as an option or not, it only outputs the primary key drop and ignores the foreign key referential integrity dependency.

Now it doesn't work at all.

Brian Donahue

Right, well, please ensure you are using the "IncludeDependencies" option. Because ForceScriptSynchronization is not the correct option to make dependencies work -- whatever you had must have been some unintentional behavior of that option, as I said before.

Also, you don't have to create a reference to LinqBridge as far as I know - just copy the file to the folder.

clamk123

I tried it with or without IncludeDependencies, and with or without ForceScriptSnchronization (just to make sure that's no longer relevent), and I am still getting the output script just dropping and recreating the primary key. Whatever was tripping it before to include the foreign keys as well has been stopped, but it doesn't seem to be enabled with the IncludeDependencies.

On a side note, we generally don't use IncludeDependencies because it isn't very granular and has a fairly global effect. And by that I mean, it includes hundreds of extra objects we don't want deployed. As a future "feature" in SqlCompare, if "dependencies" can be included for specific types of objects, or be enabled or disabled at different levels that would be a nice addition.

clamk123

The behavior, where it dropped dependent foreign keys of a changing primary key, was the behavior that happened in previous versions were used without having to include dependencies. I guess what we're looking for isn't new functionality, as much as acting like it did under 8 and 9 if that makes any sense.

Brian Donahue

We're probably going to need the source code and the databases (backups, scripts, or snapshots) to replicate the problem, as it's more than likely specific to your databases. You can send them to support@red-gate.com, quote F0065592 in the subject line.

clamk123

I emailed the sql scripts, a code sample, and explanation of the issue referencing the above subject line.

Brian Donahue

I have logged a bug ( SC-6081 ). I cannot explain why SQL Compare Engine is behaving like this.

Brian Donahue

It appears to work correctly now in SDK build 170.
ftp://support.red-gate.com/patches/sql_ ... .0.170.exe
Using code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RedGate.SQLCompare.Engine;
using RedGate.SQLCompare.Engine.ReadFromFolder;


namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            using (Database source=new Database(), target=new Database())
            {
                ScriptDatabaseInformation db = new ScriptDatabaseInformation();
                source.Register(@"D:\work\casework\65592\source\", db, Options.None);
                target.Register(@"D:\work\casework\65592\target\", db, Options.None);

            Differences differences = source.CompareWith(target, Options.None);

            Options options;
            //these options don't matter, but removes the extra stuff. Note that I am including dependencies, this has no effect either way. 
            options = Options.NoSQLPlumbing | Options.DoNotOutputCommentHeader | Options.IncludeDependencies;


            Work work = new Work();

            work.BuildFromDifferences(differences, options, true);

            string s = work.ExecutionBlock.GetString(); 
            }
        }
    }
}

Produces this script, which drops the foreign key first:

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
PRINT N'Dropping foreign keys from [dbo].[tblTableC]'
GO
ALTER TABLE [dbo].[tblTableC] DROP CONSTRAINT [FK_tblTableC]
GO
PRINT N'Dropping constraints from [dbo].[tblTableP]'
GO
ALTER TABLE [dbo].[tblTableP] DROP CONSTRAINT [pc_tblTableP2]
GO
PRINT N'Creating primary key [pc_tblTableP] on [dbo].[tblTableP]'
GO
ALTER TABLE [dbo].[tblTableP] ADD CONSTRAINT [pc_tblTableP] PRIMARY KEY CLUSTERED  ([id]) ON [PRIMARY]
GO
PRINT N'Adding foreign keys to [dbo].[tblTableC]'
GO
ALTER TABLE [dbo].[tblTableC] ADD CONSTRAINT [FK_tblTableC] FOREIGN KEY ([id]) REFERENCES [dbo].[tblTableP] ([id])
GO