Red Gate forums :: View topic - ExecuteBlock changes collation from Finnish to SQL_Latin1_Ge
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

ExecuteBlock changes collation from Finnish to SQL_Latin1_Ge

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
lcardani



Joined: 26 Jan 2011
Posts: 3

PostPosted: Wed Jan 26, 2011 2:59 pm    Post subject: ExecuteBlock changes collation from Finnish to SQL_Latin1_Ge Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6581

PostPosted: Thu Jan 27, 2011 3:31 pm    Post subject: Reply with quote

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

Code:

Options myOptions=Options.Default ^ Options.IgnoreCollations ...
work.BuildFromDifferences(stagingVsProduction, myOptions, true);
Back to top
View user's profile Send private message
lcardani



Joined: 26 Jan 2011
Posts: 3

PostPosted: Mon Jan 31, 2011 3:10 pm    Post subject: Collation still changes Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6581

PostPosted: Wed Feb 02, 2011 5:46 pm    Post subject: Reply with quote

Is Latin1_General the default database collation?
Back to top
View user's profile Send private message
lcardani



Joined: 26 Jan 2011
Posts: 3

PostPosted: Fri Feb 04, 2011 2:51 pm    Post subject: Default collation? Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6581

PostPosted: Fri Feb 04, 2011 4:45 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group