Did you know... Because of Collation settings for the database in SQL Server, you may not be able to navigate through the Database objects in Enterprise Manager

  • Article

At times, when you try to open the tables within a Database in SQL Server 2000 Enterprise Manager, you may see below error(s):

1. Error while trying to explore table from Enterprise Manager:

-----------------------------------------------------------------------------------------------------

"Error: 2775: Code page 850 is not supported by the operating system."

2. Error while trying to open tables from object browser in Query Analyzer:

------------------------------------------------------------------------------------------------------------------------------

[Microsoft][ODBC SQL Server Driver][SQL Server]Code page 850 is not supported by the operating system.

[Microsoft][ODBC SQL Server Driver][SQL Server] [Microsoft][ODBC SQL Server Driver][SQL Server

Location: colnames.cpp:4212

Expression: ptrCC

SPID: 100

Process ID: 1436

Description: Bad error handling downstream of PtrGetCmd!!

You may see some other codepage number in the error message rather than 850 (depending upon the wrong codepage setting)

One will surely try below step(s) to resolve this issue:

1. Try to check the properties of the DB in question; you get the same error message.

2. If you try to restore the DB from a backup, you will not be able to do that.

Even if you restore the DB on another server and then take a backup and try to restore it on the original server, you'll see below error:

Microsoft SQL-DMO (ODBC SQLState: 42000)

--------------------------------------------------------------------

Database <Database> contains columns or parameters with the following code page(s) not supported by the operating system: 850.

RESTORE DATABASE is terminating abnormally.

This happens because of the Codepage setting on the system (O/S setting) and also the collation setting of the tables/DB in question. You can check the O/S Codepage setting at: [HKEY_LOCAL_MACHINE \SYSTEM \CurrentControlSet \Control \Nls \Codepage] and the key is OEMCP. By default, it should be 437(The Original IBM PC code page). You may see some other code page setting like 866 which means Cyrillic.

To know more about codepage, please see https://en.wikipedia.org/wiki/Code_page.

As a troubleshooting step(s), one can try to restore the DB on a different server. Check the properties of the DB. It will be the same as of what we are seeing in the Codepage related error message(s) above. Change the DB collation to CP1 using ALTER Database command. Take a backup and try to restore it on the main server, it will again fail.

Another way to have your database back in working state is try to move the data using an ETL tool (like DTS or SSIS package). Even in this process, you may see failure as there are possibilities that even the columns have different collation set.

You can then script out the Database and modify the Database script to use default collation type. Create a new DB on the original server using this new script. Then do a BCP out and BCP in to the Database and data will be able to move to this DB.

Additional Information regarding codepage can be found at below sites,

https://www.microsoft.com/typography/unicode/cscp.htm

https://www.microsoft.com/globaldev/reference/WinCP.mspx

https://en.wikipedia.org/wiki/Code_page

https://msdn2.microsoft.com/en-us/library/aa214328>(SQL.80).aspx

Raman Sharma
SE, Microsoft SQL Server