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.

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.

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.

...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.

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.

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).

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.

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.

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.

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.

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.