OEM code page error

I want to open database but I got this error (My database have Latin1_General collation name) “The current ANSI or OEM Code page on your compuer doesn’t match the 1252 code page of the Latin1_General collation for the test2 database” I did try to rename collation name in sql server Alter database Test2 collate Thai_BIN. I got this error “The database could not be exclusively locked to perform the operation.” Anyone could help me to open the database?

Mena, What version of SQL Server are you running? Important: If it is SQL Server 7 the collation setting is on the SQL Server itself whereas SQL Server 2000, you can set different collation settings per database in the SQL Server. To alter the collation on a SQL Server, you must rebuild the master database. Read the following; http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_collation_192m.asp Hope this helps. [;)]

First, I can see that you are using a Windows Collation for your database. Unless you are running Navision 3.70, Windows Collations are only supported for binary sort orders - probably not what you want. I suggest you use a SQL collation and choose the dictionary order that you probably want. The client machine that you create the database from, has an ANSI and OEM system code page that determines the collations available to you in the Collation tab of the New Database window. Someone has created a database from a machine that has a different code page to your machine. In any case your machine does not have a 1252 ANSI code page. So you can’t open it. Your system code page is changeable in Control Panel “Regional Options”. In windows 2000 this is accessed from a System Default… button; in XP it is on the Advanced tab, “Language for non-unicode programs”. Maybe your machine has the wrong language here. When you create the database choose the correct collation in the collation tab - don’t rely on the default. There are numerous other posts about this if you search for code page or something. Altering the database with ALTER DATABASE … COLLATE … does not help. This changes the system tables in the database and sets the default for future columns of user tables, but does not change existing columns. This is because all indexes containing character data need to be recreated.

Conull, rebuilding master does not affect your user databases and will not help. There is actually no easy way to change your database collation once its been set. You can script all your objects and recreate them in another database, using the COLLATE keyword for all columns, then use a DTS job to export/import the data. But it is a bit tricky and very time consuming, and you need to first create the target database as a Navision database, so that it contains the application role and encrypted password. But its possible. BTW, In 3.70 there is a feature to alter the collation which does the work for you. But since all indexes containing charcater data must be recreated, it is unavoidably a long task for a large database.

I have 2 database 1.Cronus database Latin1 Collation – This database can’t open.But It can open before I create IJSS database. 2.IJSS database Thai_Bin – This database can open. I want to open this 2 database because I have to test user portal.I did change code page to Thai at Regional Setting.It doesn’t work. How can I open 2 database that different collation?

Are you changing the Default (or Advanced) regional settings locale, not the user locale?

Mena, Look at Robert’s earlier post - what operating system are you working on?

Mena, I think you got the same problem as me before: I found that if you are using thai collated database. You can’t open it by using client with 1252 code page. You can open it by using thai client or chinese client. I had asked this question before. But no solution can be found yet. Why not you ask help from NTR.

Quote: Your system code page is changeable in Control Panel “Regional Options”. In windows 2000 this is accessed from a System Default… button; in XP it is on the Advanced tab, “Language for non-unicode programs”. Maybe your machine has the wrong language here. Do this on the client machines - so they match the server setting.

Richard, Mena: I think we have covered how you change a client code page to match a database. If what you want is two clients with different system default code pages to open the same database, then that can’t be done*. You cannot open a database from a client machine, whose system default code page - either its OEM (e.g, 850 (West. Europe), 437 (US/UK)), or ANSI (e.g. 1252 (West. Europe), 1250 (Hungarian)) does not match the database collation’s code page. This is by design and prevents inter-code page conversion errors of character data. This problem is also present in Navision Server, except in Navision Server there is no check to stop you from doing this. In the SQL option, there is such a check and an error message. It is to protect your data. There is no way around that problem, because it is a code page problem - not a SQL problem, or Navision Attain problem etc. The potential ‘solutions’ are: 1. If the client deals only with Unicode character data, and the server is capable of storing Unicode data, then the concept of code pages is removed and the problem goes away. Unfortunately, although SQL Server can happily store Unicode data, Attain is not Unicode enabled internally and nor is Navision Server. 2. If you are sure that all characters you will be storing have the same code point (i.e. numeric value, e.g. 65 for ‘A’) for each client’s code page then conversions won’t be a problem. Normally, this is not true of ‘extended’ characters - those above 127 - that represent accented charcters that are language-specific. *In light of (2) above, Navision 3.70 allows you to relax the code page constraint so that you can open a database from clients with different system code pages - the checking is disabled. The responsibility lies with the Navision administrator, that no character data will be corrupted because of this. Also, since character sorting will follow the database collation, one of the clients may not ‘like’ the sorting.