ExecuteBlock changes collation from Finnish to SQL_Latin1_Ge

lcardani

Hi,

I am using the SDK tool to syncrhonize our customers' database to a given snapshot when upgrading their software.

I am using Options.IgnoreCollations, which works to prevent any differences from being reported if the only differences are Collation. But if a table's field has a difference in addition to collation (such as field size), the field size difference is reported.

One customer has their collation set to Finnish_Swedish_CI_AS. When my database synchronization tool is run on their database with our database snapshot, differences are found in their table fields (which is expected). But when the ExecuteBlock is run to synchronize the differences, their field's collation is changed from Finnish_Swedish_CI_AS to SQL_Latin1_General_CP1_CI_AS. That is the collation that we use but not what the customer wants.

How do I tell the SDK tool to leave the collation alone?

Thanks,

Larry C.

Brian Donahue

Be sure to use the same options in BuildFromDifferences that you use for the database registration and that should work.

Options myOptions=Options.Default ^ Options.IgnoreCollations ...
work.BuildFromDifferences(stagingVsProduction, myOptions, true);

lcardani

Thanks, Brian, for your response. But I still have the same problem.

1. I have a table named Campus, with a field called Name. It is a VarChar(32) and a collation of SQL_Latin1_General_CP1_CI_AS.

- I use SQL Server to change the field to be VarChar(31) and a collation of Finnish_Swedish_CI_AS with the following SQL command:

alter table campus alter column Name varchar(31) collate Finnish_Swedish_CI_AS

2. I run my synch tool, which uses the SDK, to use my snapshot to synchronize the database.

3. The tool registers the target datebase, using the following call:

targetDB.Register(targetConn, Options.Default Or Options.IgnoreTriggers Or Options.IgnoreCollations)

Options.Default Or Options.IgnoreTriggers Or Options.IgnoreCollations is shown to be equal to &H12050000B040A.

4. The tool compares to find differences, using the same options:

Dim dbdiffs As Differences = sourceDB.CompareWith(targetDB, Options.Default Or Options.IgnoreTriggers Or Options.IgnoreCollations)

5. The tool tells me there is one difference. The tool builds from the differerences, to prepare to synch, using the same options:

work.BuildFromDifferences(dbdiffs, dbdiff, Options.Default Or Options.IgnoreTriggers Or Options.IgnoreCollations, True)

6. The tool synchronizes the difference:

exblock.ExecuteBlock(work.ExecutionBlock, targetConn.ServerName, targetConn.DatabaseName, False, targetConn.UserName, targetConn.Password)

7. When I look at the Campus table's Name field in SQL Server, its length is properly set to varChar(32). But its collation is set back to SQL_Latin1_General_CP1_CI_AS.

So the collation is changed to SQL_Latin1_General_CP1_CI_AS, when I wanted it to remain at Finnish_Swedish_CI_AS.

Let me know if you need more info from me!

Thanks,

Larry

Brian Donahue

Is Latin1_General the default database collation?

lcardani

Hi, Brian,

>>>Is Latin1_General the default database collation?

Do you mean in the database snapshot? Or the database being updated? And how would I check that?

Thanks,

Larry

Brian Donahue

My guess is that when the object is created, it's simply inheriting the default collation of the database you are running the query against. Logically, this is what I think should happen if you omit a collation entirely.