October 26, 2006 at 2:59 pm
After looking at a problem that turned out to be collation-related, I'm concerned that my new SQL2k5 server needs to be reinstalled with a different collation. Here's the story:
Our new SQL Server instance was setup with a server collation of Latin1_General_CI_AI. At the time of installation I was unaware that the application we'd be running off it uses Latin1_General_BIN for its databases. This is the only app that will be using the instance.
What really caught my attention was when one of my developers received a collation error when trying to join a table to a temp table. I now have pictures in my head of one of those 'Bang Head Here' targets posted outside my office as me and the developers cope with multiple collation in most every sql query we write from now till the foreseeable future. It's something I'd like to avoid, if possible.
Right now the instance is a mostly clean install. Besides the app databases, which have to yet been installed, we've been building reports in Reporting Services. I've noted that RS has it's own TempDB (good call, MS engineers!), so it seems safe to leave RS out of the equation.
So, my question is, what's the best way to get this server to a single collation? I've thought of maybe just changing the collation of tempdb, but that seems dubious. I could reinstall the entire instance, I suppose, but that'd mean I'd miss my favorite tv show tonight. 😉 Or is there some new, safe way to change the server collation that I don't know about?
October 26, 2006 at 4:08 pm
Sorry for the bad news but let me put it to you this way:
You *are* missing your favorite show tonight!
* Noel
October 27, 2006 at 6:17 am
Individual database collations can be "changed" when they are created, but the System DBs get their settings from the server itself. So, if you want to change TempDB, you'll have to reinstall from scratch. However, you should be aware that SQL Server 2k5 gets its default collation from your Windows OS. You may (or may not) have to change the Windows OS collation.
BOL says "Change the default settings for Windows collation only if your installation of SQL Server must match the collation settings used by another instance of SQL Server, or if the collations setting must match the Windows system locale of another computer."
Hope this helps.
October 27, 2006 at 6:55 am
Another way is to change the temp table. Add "COLLATE database_default" to string columns of all temp tables or table variables. E.g.
CREATE #t TABLE (c1 nvarchar(10) COLLATE database_default NOT NULL)
So the string columns in the temp table will not use the default collate of temp db. It will use the collate of your DB.
October 27, 2006 at 8:39 am
Well, I tivo'ed my show (tivo is any admins best friend), and stayed late to reinstall the server with the proper collation. I have to say, MS did a great job making installation and configuration friendly. Even restoring the entire Reporting Services environment (including IIS directories, scheduled jobs, etc) was nearly painless and mostly automated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply