Difference list doesn't work

Automate and integrate using the SQL comparison API

Difference list doesn't work

Postby njansen » Tue Nov 27, 2012 4:07 pm

I'm building a tool to sync a specific list of objects
For each object i want to sync i'll set the Difference.Selected property to true, for all other objects i don't want to sync, i'll set the Difference.Selected property to false.

After that, i'll call the Work.BuildFromDifferences method;
Work.BuildFromDifferences(Differences, Options.Default, true);

Why do i see objects where the Difference.Selected prop is set the false?
njansen
 
Posts: 5
Joined: Tue Nov 27, 2012 3:45 pm

Postby james.billings » Wed Nov 28, 2012 5:40 pm

The last time I tried this it seemed to work okay. One possibility is if the objects being included are dependencies of others - is this the case? If so, you may need to set the option to not include dependencies.

Failing that, can you post back a code sample of how you've got it set up and details of the assembly versions you're referencing so I can try it out here?
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby njansen » Thu Nov 29, 2012 12:52 pm

I can only select Options.IgnoreSynonymDependencies, but that doesn't work. The other option about dependecies is IncludeDependencies, there's no IgnoreDependencies option.

I'm trying to synchronise a list of table valued functions.

Here's a code fragment (in C#);

Code: Select all
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using RedGate.SQLCompare.Engine;
using RedGate.Shared.SQL.ExecutionBlock;

namespace Config.DatabaseUpdate
{
    class OltpFunctions
    {

   public static bool SyncOltpFunctionsToFile(string TargetCatalog, string TargetServer, int logType)
        {
            bool res = true;
            string snapshotFile = Config.DataManager.ApplicationScriptPath() + \"\\\\SnapshotProqOltp.snp\";

            using (Database stagingDB = new Database(),productionDB = new Database())
            {
                ConnectionProperties targetConnectionProperties = new ConnectionProperties(TargetServer, TargetCatalog);

                // Connect to the two databases and read the schema
                try
                {
                    Logger.Log(\"Loading snapshot file\", 2);
                    stagingDB.LoadFromDisk(snapshotFile);
                }
                catch (SqlException e)
                {
                    Logger.Log(e.Message, logType);
                    res = false;
                    return res;
                }
                try
                {
                    Logger.Log(\"Registering database \" + targetConnectionProperties.DatabaseName, 2);
                    productionDB.Register(targetConnectionProperties, Options.Default);
                }
                catch (SqlException e)
                {
                    Logger.Log(e.Message, logType);
                    res = false;
                    return res;
                }

                // Compare snapshot to production. Comparing in this order makes production the second database
                Differences stagingVsProduction = stagingDB.CompareWith(productionDB, Options.IgnoreSynonymDependencies);

                // Select the differences to include in the synchronization. In this case, we're using all differences.
                foreach (Difference difference in stagingVsProduction)
                {
                    difference.Selected = false;

                    // Only select functions
                    if (difference.DatabaseObjectType == ObjectType.Function)
                    {
                        // only fix differences or create new objects in the target database
                        if ((difference.Type == DifferenceType.Different) || (difference.Type == DifferenceType.OnlyIn1))
                        {
                            // Only select objects which start with Dim, Fact, Etl or Sec
                            if ((difference.Name.ToUpper().StartsWith(\"[DBO].[DIM\")) || (difference.Name.ToUpper().StartsWith(\"[DBO].[FACT\")) || (difference.Name.ToUpper().StartsWith(\"[DBO].[ETL\")) || (difference.Name.ToUpper().StartsWith(\"[DBO].[SEC\")))
                            {
                                Logger.Log(string.Format(\"Selected objectname: {0}\", difference.Name), 6);
                                difference.Selected = true;
                            }
                        }
                    }
                }

                Work work = new Work();

                // Calculate the work to do using sensible default options
                // The script is to be run on production so the runOnTwo parameter is true
                work.BuildFromDifferences(stagingVsProduction, Options.Default, true);

                // We can now access the messages and warnings
                Logger.Log(\"Messages:\", logType);

                foreach (Message message in work.Messages)
                {
                    Logger.Log(message.Text, logType);
                }

                Logger.Log(\"Warnings:\", logType);

                foreach (Message message in work.Warnings)
                {
                    Logger.Log(message.Text, logType);
                }

                // Disposing the execution block when it's not needed any more is important to ensure
                // that all the temporary files are cleaned up
                using (ExecutionBlock block = work.ExecutionBlock)
                {
                    // Display the SQL used to synchronize
                    Logger.Log(\"SQL to synchronize:\", logType);
                    Logger.Log(block.GetString(), logType);

                    // Finally, use a BlockExecutor to run the SQL against the WidgetProduction database
                    try
                    {
                        //BlockExecutor executor = new BlockExecutor();
                        //executor.ExecuteBlock(block, targetConnectionProperties.ServerName, targetConnectionProperties.DatabaseName);
                       
                        // Write the sql block to a file.
                        DataManager.FileWriter(block.GetString(), @\"D:\\Temp\\SqlToSync.sql\");
                    }
                    catch (SqlException e)
                    {
                        Logger.Log(e.Message, 2);
                        res = false;
                        return res;
                    }
                    return res;
                }
            }
        }


   }
}
njansen
 
Posts: 5
Joined: Tue Nov 27, 2012 3:45 pm

Postby james.billings » Thu Nov 29, 2012 1:08 pm

IncludeDependencies is a default option. To turn it off you need to explicitly select all other defaults asides from that; so replace:

options.default

with

options.IgnoreFileGroups | options.IgnoreFillFactor | options.IgnoreUserProperties | options.IgnoreWhiteSpace | options.IgnoreWithElementOrder | options.IgnoreDatabaseNames | options.DecryptPost2kEncryptedObjects
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby njansen » Thu Nov 29, 2012 2:03 pm

Options.IgnoreDatabaseNames doesn't exists, so i tried IgnoreDatabaseName, but the option is obsolete, so i used the recomended option IgnoreDatabaseAndServerName.
I still receive a script with objects i don't want.

for example, the script want's to drop constraints on tables;
ALTER TABLE [dbo].[AGMG] DROP CONSTRAINT [PK_AGMG]

As you can see in the code fragment, I only select functions which start with a specific name.
njansen
 
Posts: 5
Joined: Tue Nov 27, 2012 3:45 pm

Postby james.billings » Thu Nov 29, 2012 2:51 pm

OK, I tried your code here with defaults and a couple of functions and it only scripts the function that matches.

Is it possible for you to supply your snapshot as the problem you're seeing is likely to be rather dependent on the specifics of your DB. If so, please mail it to support@red-gate.com quoting F0067161 in the subject line.
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby njansen » Thu Nov 29, 2012 4:18 pm

I can't provide the snapshot in the current shape. I'll have to strip the database and make a new snapshot.
njansen
 
Posts: 5
Joined: Tue Nov 27, 2012 3:45 pm

Postby james.billings » Thu Nov 29, 2012 4:21 pm

Sure- otherwise, it's probably just down to the objects that function relates to so a simple repro should also suffice track it down.
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby james.billings » Fri Nov 30, 2012 12:31 pm

Thanks for supplying the snapshot. It does look like dependencies- I loaded up your snapshot in your sample code and found that it was also scripting some tables.

Amending the options as I described earlier stops that - here's the correct line (notice it's the options at the point of building your 'work' that's important as the dependencies are included (or not) during the sync phase usually)

Code: Select all
                // Calculate the work to do using sensible default options
                // The script is to be run on production so the runOnTwo parameter is true
                work.BuildFromDifferences(stagingVsProduction, Options.IgnoreFileGroups | Options.IgnoreFillFactor | Options.IgnoreUserProperties | Options.IgnoreWhiteSpace | Options.IgnoreWithElementOrder | Options.IgnoreDatabaseAndServerName | Options.DecryptPost2kEncryptedObjects, true);
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby njansen » Fri Nov 30, 2012 12:48 pm

Ok, thanks. This works for me.
njansen
 
Posts: 5
Joined: Tue Nov 27, 2012 3:45 pm

Postby james.billings » Fri Nov 30, 2012 12:50 pm

Glad that's sorted. Post back or email us if you need anything further!
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Operator '|' cannot be applied to operands of type ' RedGate

Postby stefano_frigerio » Fri Aug 22, 2014 5:08 pm

Hi
I have the same problem and i Need to exclude dependecies from the difference list.

So as described in the post I have added the list of options

work.BuildFromDifferences(differences, Options.IgnoreUserProperties | Options.IgnoreWhiteSpace | Options.IgnoreWithElementOrder | Options.IgnoreDatabaseAndServerName | Options.DecryptPost2kEncryptedObjects, true);

But when I go to compile the project I got this error.

Operator '|' cannot be applied to operands of type ' RedGate.SQLCompare.Engine.Options' and 'RedGate.SQLCompare.Engine.Options'

Any Idea on how to solve?

I'm Using Visual Studio 2013 and SQL Comparison SDK 10.5

Thanks
stefano_frigerio
 
Posts: 1
Joined: Fri Aug 22, 2014 5:01 pm


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests