Collation --- please help!

From: jb (jb_at_discussions.microsoft.com)
Date: 06/18/04 

Date: Fri, 18 Jun 2004 04:57:01 -0700

I need to understand how to supply database creation scripts to customers wrt collation issues.

I am getting a collation conflict when my SQL code is used in USA (developed in UK). It is connected to querying the msdb database to do with scheduled jobs.

I (think I) understand that this is because:
* my database scripts create my db with collation Latin1_General_CI_AS , which is the default for UK SQL
* customer's database default collation is SQL_Latin1_General_CP1_CI_AS, which appears to be the (legacy) default for US SQL
* so for customer, msdb & my db have different collations

I only (intend to) work with UniCode nvarchar()s etc., and I put in N'...' for my literals, so I didn't think this was much of an issue. I will look at my code --- perhaps I have a problem there.

BUT WHAT I REALLY WANTED TO ASK IS THIS:
===============================

I supply a SQL application to customers. I don't really know where it will be used in long run. Maybe I will need to support French etc.; certainly for now I need to at least support UK & US, with the likelihood that they will have different default collations, and obviously I don't want customers to have to change their default collation.

Should my scripts which create dbs at customer sites be specifying collations at all? I have read all about it, and am not sure. The only reason they do specify collations is that, whether I generate scripts from EM/QA/VS.NET, they *all* put in explicit collation statements (and I don't see a way to suppress this), both for the db as a whole, and for every nvarchar() column in every table. I am beginning to wonder whether this is a (very) bad idea? Should I go through all the generated scripts and remove the collation clauses?

Please help!

Relevant Pages

  • Re: Change field collation
    ... constraints and statistics on the columns before you can alter ... There are quite a lot of caveats and you use this script at your own risk. ... Changing the collation of the existing tables is unfortunately not very ... You might consider using SEM to script out the objects in the database, ...
    (microsoft.public.sqlserver.programming)
  • Re: Changing collation -- URGENT
    ... You can use the following script to change the collations of your columns, ... duplicate primary key constraints create statements, ... Changing the collation of the existing tables is unfortunately not very ... You might consider using SEM to script out the objects in the database, ...
    (microsoft.public.sqlserver.server)
  • Re: Database collation error after moving to a remote sql server
    ... I think I'm going to try plan B: Install a second instance of SQL 2000 onto ... the remote server and set its default collation to the same as my SMS ... database, then move my db to the new instance. ... The char, varchar, text, nchar, nvarchar, or ntext system data types, ...
    (microsoft.public.sms.setup)
  • Re: COLLATION PROBLEM CAN ANY ONE HELP
    ... Sounds like you may have code page X data in a code page Y database. ... database with a collation that matches the data's actual code page: ... COLLATION PROBLEM CAN ANY ONE HELP ... | Content-Type: text/plain; ...
    (microsoft.public.sqlserver.datamining)
  • Re: Need to reconfigure SQL Server to be Case-Insensitive
    ... You will want to perform ALTER DATABASE and ALTER TABLE statements. ... You can change the collation of a column by using the ALTER TABLE statement: ... applied to all columns, user-defined data types, variables, and parameters ...
    (microsoft.public.sqlserver.server)