Collation problem with Snapshot

aperregatturv

Hi Brian,

one of customer is having a problem with this collation problem. Let me explain. We use SQL 2000
We create a snapshot file using SQL Compare with English version
and we distribute it as database update with other files. Now the problem if its updated on a English version of OS with SQL or MSDE everythings good. our customer uses Dutch version of OS/MSDE so when we create the database in Dutch version we set the collation to system default.
the problem is when the database is updated using the snapshot file which is created using english version it overwrites default dutch collation to english which is SQL_Latin1_General_CP1_CI_AS and we get in trouble with our application which tries to query 2 different database to pull records since collation does n't match we get an error saying
"implicit conversion of carchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict"

Its one of the fields in a table thats different.

Please let me know if you can suggest me something on this.

Thanks
Arun

aperregatturv

Any response?. I have a maintaince contract for SQL Bundle and was wondering if i can get some kind of reply for a possible solution and i have put our customer on hold for this problem. I hope RedGate understands customer problems and respond according.

Waiting patiently.

Thanks
Arun

Brian Donahue

Hi Arun,

I know we're not very clear about this, but the support forum aren't integrated into the support system. So you will not get an answer based on your support entitlement. You will get an answer from a member of the development staff and in two days from the moderator if they don't answer. 'Official' support is by email. We can easily identify your support entitlement by email and respond accordingly.

I think the solution to the problem is pretty easy, though. If you use the 'ignore collation order' setting, that should create a schema migration script without the column collations in it, and in this case it will use the database default collation for character data.

If this does not work, please let us know.

aperregatturv

Thanks for the reply. I will remember to use support system email for any emergency problem and thanks for letting me know.
I was under lot of pressure to get this fixed. Sorry about this.

Can you walk me through where i can set this option for creating a Snapshot file.

Thanks,
Arun

Brian Donahue

Hi Arun,

It's more about the comparison options. The snapshot is a representation of the schema and includes everything, but if you do not want collation orders in the migration script produced as the result of a comparison, then you'll want to edit the project, click the options tab, click 'more' and then tick 'Collations' under the Ignore heading.

aperregatturv

Brian,

I understand what you saying but the problem is, let me explain
I open SQL Compare and select
Tools -> Create Database Snapshot it displays a dialog box to select Server and database to create the Snapshot. I dont see any option when i do this may be i am not following something. Please explain.

Thanks
Arun

Brian Donahue

Hi Arun,

The snapshot contains the whole schema -- you don't get any choice about that. What you can do is change the comparison settings to ignore the collations.