SQL Collation  Topic is solved

Support for Microsoft SQL Server in the context of Jiwa installations.

SQL Collation

Postby 2can2 » Wed Oct 21, 2015 4:29 pm

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
2can2
Frequent Contributor
Frequent Contributor
 
Posts: 171
Joined: Tue Feb 26, 2008 10:24 am
Topics Solved: 25

Re: SQL Collation  Topic is solved

Postby Scott.Pearce » Wed Oct 21, 2015 4:52 pm

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
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: SQL Collation

Postby Scott.Pearce » Wed Oct 21, 2015 4:56 pm

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.
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: SQL Collation

Postby 2can2 » Thu Oct 22, 2015 10:34 am

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
2can2
Frequent Contributor
Frequent Contributor
 
Posts: 171
Joined: Tue Feb 26, 2008 10:24 am
Topics Solved: 25


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest

cron