December 19, 2006 at 4:25 pm
I am reinstalling a test database that was un-installed on me...I am trying to mimic the production collation and am having a hell of a time figuring out which selection to make
When I run the following in production this is the result
SELECT SERVERPROPERTY( 'Collation' ) AS Server_Default_Collation
SQL_1xCompat_CP850_CI_AS
When I ran the following to get a better description I got the following
SELECT * FROM ::fn_helpcollations() where name = 'SQL_1xCompat_CP850_CI_AS'
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 49 on Code Page 850 for non-Unicode Data
When I am going through the install I am having a hard time making the connection as to which collation is the correct one to select...if anyone can give me reference as to which one I should select I would appreciate it.
Thanks in advance,
Leeland
December 19, 2006 at 4:34 pm
Choose CUSTOM INSTALL. The latter part of the installation allows you to specify these options.
December 19, 2006 at 4:43 pm
That is where I am at...I am at the selection portion where you can pick the collation but the selection names don't seem to match up...
example
Dictionary order, case-sensitive,accent sensitive....
December 19, 2006 at 5:04 pm
Choos Dictionary Order and accent-sensitive ONLY.
December 19, 2006 at 5:46 pm
WHen I am on the Collation Settings menu I am not seeing an option under SQL Collations that ='s Dictionary Order, accent-sensitive ONLY.
Am I looking in the wrong place? Sorry for the ignorant question
December 19, 2006 at 6:01 pm
AS, CS, etc.
These are various triggers set with the various collation types. I am writing this at night, so I am sure you are well past the install process, however if your server will not contain databases requiring foreign or special case-sensitivity or query syntax then one of the more standard collations to use is 'SQL_Latin1_General_CP1_CI_AS'. Using a collation with a case-sensitive trigger can get annoying especially when writing queries, because each time you refer to table names or field names, you must take case-sensitivity into consideration. Although it can be negated with the COLLATION function, you still have to remember to use it.
December 19, 2006 at 6:35 pm
Which part are you at? What do you see? What are the possible values?
December 19, 2006 at 7:06 pm
I am still sitting at the COllation Settings menu...I am remoted in from work and was hoping to get this finished before work tomorrow...it is not a priority but once I got into it now it is irritating me and I want to figure this out.
There has to be 40+ selections I can make...The thing that is sticking it to me, is when you go into the properties of the production box it shows the collation as SQL_1xCompat_CP850_CI_AS
Why can't they make the selection of the collation a little easier...
December 19, 2006 at 7:28 pm
Actually, the collation is simply US. English but put in a more unique ID way.
Do you need to have the exact collation setting in the server installation? Or you just need it for a particular database. You can install the default server collation, which is, as Jeff mentioned, 'SQL_Latin1_General_CP1_CI_AS'. This is also US. English. Your production collation only distinguisheds the advanced setting on case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive.
December 19, 2006 at 9:06 pm
I was told to make it mimic production so I would like to attempt to figure out how to get that done.
December 19, 2006 at 10:10 pm
I do not have the means to mimic an installation but I assume you are looking at the Collation Settings dialogue box where you chose to either use windows collation or SQL Collations. I also assume that you chose SQL Collations, hence the drop down selection of the Collation Names. If you cannot find SQL_1xCompat_CP850_CI_AS from the list, chose Windows Locale and select the appropriate check boxes (i.e. case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data)
December 20, 2006 at 4:19 am
Hi, had a similar issue last year.
The compat collations are often for upgrades from previous versions/access.
The only way around it I found (don't try to get a 'similar' one - its either the same or wrong) is to create an unattended installation file, hack the default collation (just copy and paste overit) and run the unattended install.
Quite simple, but may take a few gos...
December 20, 2006 at 7:27 am
GOT IT!
OK so I emailed my boss last night to gain some insight if there was a method to the maddness that is selecting the correct collation from the menu without persay building the right collation....here is his response.
SQL_1xCompat_CP850_CI_AS
Secret?? Look for the only one with 1x and 850 Character set in it
I don't have the exact name of the collation but when going through the list there was only one selection option that had 1.x and then 850 for the character set and that was the correct collation to select.
Thanks again to all who took the time to give me advice I do appreciate it.
Leeland
December 20, 2006 at 7:41 am
|
49 |
nocase34.850 |
Strict compatibility with Version 1.x case-insensitive databases |
SQL_1xCompat_Cp850_CI_AS_KI_WI |
sp_helpsort provides a lot of info.
When I had to recreate a servers character set and sort order, I installed my best guess, tried to restore a small backup for the target server. If successful great I had it. If not I had to redo the Install and choose a differt character set and sort order.
September 20, 2010 at 3:53 pm
I'm doing this exact same install today. I will double check my work when the install is done but I think it is the Strict Compatibility with version 1.x case-insensirtive database for use with the 850 (Multilingual) character set.
I understand your frustration as the collations you see on install do not match what you see after the databases have been created.
The database you have is Sort Order 49. Microsoft does not make it easy in trying to figure out what to install
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply