Database upsizing from sqlserver6.5 to sqlserver2000

server|sqlserver| Data | database
This article is dedicated to a confused friend of the database upgrade process from sql6.5 to sqlserver2000.

I was fortunate to encounter such a case: (a word is called: the difficulty of how much, happiness is how big, this is not, happiness came ~)

A power plant that runs several application systems, with material systems being the most important. Architecture, roughly: NT4.0 Server Simplified Chinese + SQL server6.5 English (material system data) application system with powerbuilder6.0 development, has been running for 5 years, the amount of data about 300M. As the system continues to be used, it is now necessary to upgrade the existing system, from sql6.5 to sqlserver2000 appears to be a digital change on the surface, or a simple import export, but I think it is too simple.

For this upgrade, I have used three sets of scenarios:
1, in sqlserver6.5 and on the use of external tools bcp ... out command export;
Test with one of the tables, and the results are imported sqlserver2000

Fault 1:bcp ... in hint "string data, right intercept";
Fault 2: Chinese Characters of the field display garbled;
Fault 3: The Time field format with Chinese characters cannot be successful when imported with BCP in.

2, with 2000 Upgrade Wizard;

Failure 1: Overcome the difficulties (require patching, reset landing mode, etc.) after the upgrade to report some of the errors can not be viewed, and then open the database a look, nothing;

Failure 2: The Upgrade Wizard is not able to do so with high transparency and cannot be traced incorrectly.

3, using ODBC configuration to access the sql6.5 data source, and then use the Sqlserver2000 Import Wizard;
Failure: After changing more than 90 new table names, start copying, successful. However, Chinese characters appear garbled. Subsequently, in 2000 to re-establish a Latin general bin1 for the sorted database, import again, the fault remains.

After a week of repeated testing, repeated failure, the final decision or the adoption of the first option is more flexible, the first concrete steps to tidy up to 饔 the reader.

1 Open SQL Server Enterprise Manager on the NT Server 4.0 machine, select the database you want to upgrade, select the table, and generate a script to create the table;
2 Generate scripts to create stored procedures;
3 If there is a view, please save it as a text for use;
4 Open the Isql/w tool, select the database to be upgraded, and run the following statement to generate the bcp out/in command (otherwise, manual editing is laborious):
Select ' bcp mydatabase.dbo. ' + name + ' out ' +
'/data/' + name + '. TXT ' +
'-usa-p***-sservername-c-c1252 '
From MyDatabase. sysobjects
where type = ' U '


Description: MyDatabase is the database name.
'/data/' is the DATA directory in the directory running BCP, which needs to be established beforehand.
The database password.
ServerName server name.
1252 is the default code page for sqlserver6.5, it must be specified, otherwise the output of Chinese characters will be garbled.

Save results as Bcpout.bat after running
You can then replace out with in to generate the Bcpin.bat file to run on the sqlserver2000 machine.

5 OK, to use bcp, but we can not use the bcp tool on the sql6.5, in my experiment with it exported with Date field data will generate such as "20,032 months 15:09am" format, so on the sqlserver2000 machine with bcp in A replication error occurs when you import without knowing the date format. Here, you must use the BCP tool for SQL Server 2000 by using the following methods:
Open the C:\Program Files\Microsoft SQL Server\80\Tools\Binn and copy the Bcp.exe and resources to the ntserver4.0 bcp work path of the sqlserver6.5 machine (C:\mssql\binn, the original Bcp.exe file renamed to Bcp65.exe);

6 in the NT machine to establish a upgradedb directory, the Bcpin.bat, Bcpout.bat and just generated the script copy in, and then set up the data directory;
7 Ensure that SQL Server service starts, double-click to run Bcpout.bat, wait for the end;
8 copy or share the Upgradedb directory to the sqlserver2000 machine;
9 Open SQL Server Enterprise Manager, new database with the same name, collation for Chinese_prc_bin;
10 Open Query Anlyzer, select the database, run just the generated script to create the table, build the table structure, run the build script of the stored procedure just generated, build the stored procedure ... New view like the old library;
11 Open the Command prompt state, enter the Upgradedb directory, run Bcpin.bat, wait for the end (if there is an error, please check the password, server name, path, etc.).
12 Open Enterprise Manager, open the database, select the table to return all rows, check the data
found that the Chinese character display normal, date format is also normal.

13 Copy the original application to the sqlserver2000 machine run, found everything as usual, to upgrade the database successfully!

===================
I have a limited level, I am bound to make mistakes, I hope you correct, criticism.

IUPRG (China Materialia Yun Chi Line, carved character Map)
2004-4-8
Http://iuprg.51.net
Iuprg@163.net