ExecuteBlock changes collation from Finnish to SQL_Latin1_Ge

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

ExecuteBlock changes collation from Finnish to SQL_Latin1_Ge

Postby lcardani » Wed Jan 26, 2011 2:59 pm

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.
lcardani
 
Posts: 3
Joined: Wed Jan 26, 2011 2:45 pm

Postby Brian Donahue » Thu Jan 27, 2011 3:31 pm

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

Code: Select all
Options myOptions=Options.Default ^ Options.IgnoreCollations ...
work.BuildFromDifferences(stagingVsProduction, myOptions, true);
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Collation still changes

Postby lcardani » Mon Jan 31, 2011 3:10 pm

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
lcardani
 
Posts: 3
Joined: Wed Jan 26, 2011 2:45 pm

Postby Brian Donahue » Wed Feb 02, 2011 5:46 pm

Is Latin1_General the default database collation?
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Default collation?

Postby lcardani » Fri Feb 04, 2011 2:51 pm

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
lcardani
 
Posts: 3
Joined: Wed Jan 26, 2011 2:45 pm

Postby Brian Donahue » Fri Feb 04, 2011 4:45 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests

cron