SQL Collation

[2can2]

Hi, It seems to be a pretty long winded process to change the Collation of a Database or Server. I have the server default as one type and the Databases as another. I have modified a couple of stored procs BUT when I try and update them in Crystal I get a Collation violation! I believe a fix is to add 'Collate Database Default' to every varchar/char field defined in the stored proc which is painful at best for several stored procs.
Is there a simple way around this - client is on SQL 2008 R2. Thanks.

Cheers

[Scott.Pearce]

We try to be collation-agnostic where possible, hence our use of "Collate Database Default". You should get in the habit of doing this also, wherever you find yourself defining temp tables.

One trick I've used before is to use "memory tables" instead of "temp tables" in stored procedures - as the name implies, memory tables are created in memory so they don't use tempdb and therefore don't run the risk violating collation across databases. Memory tables are pretty fast if the server has the RAM, but the downside is that you cannot define indexes on them as you can with normal temp tables (at least you couldn't last time I checked).

There are third-party tools out there that will generate scripts to change a database's collation, and a quick google search reveals a bunch of promising hits. I remember playing with some of these years ago and found their output to be a bit hit-and-miss. The tend to work by scripting everything out, and sometimes they miss attributes.

Maybe some other forum users out there have some suggestions or stories regarding collation?

[Scott.Pearce]

Further, one could consider installing a second *instance* of sql server, using a default collation setting that matches that of the live database. Then the database could be moved to the new instance, and the old instance uninstalled. All the collations on the new server would match (jiwa/master/tempdb, etc.), hence no collation violations could occur. This might be a viable solution for small sites.

[2can2]

Hi Scot, Thanks for the quick reply and your suggestions! I think I will just add 'Collate Database Default' for the stored procedure I have modified - the standard usp_JIWA_Invoices_Invoice which doesn't have any 'Collate' statements. Temp fix then maybe create another instance of Sql at a later stage.
Cheers