This article is published by IUPRG, iUprg @ Tom.com, www.newsoftstudio.com Reprinted please keep]
This document gives a confused friend in the process of upgrading from SQL6.5 to SQLServer2000.
The author is fortunate to encounter such a case: (there is a saying: how big is the difficulty, how big is happiness, this is not, happiness is coming ~)
[This article is published by IUPRG, IUPRG @ Tom.com, www.newsoftstudio.com]
A power plant runs a number of applications, which is most important in the material system. The architecture, which is roughly like this: NT4.0 Server Simplified Chinese version SQL Server 6.5 English (material data) application system is developed with PowerBuilder6.0, has been run for 5 years, and the amount of data is about 300m. With the constant use of the system, it is now necessary to upgrade the existing system, from SQL6.5 to SQLServer2000 from the surface, it seems to be a number of changes, or simple import export, but I think it is too simple. .
For this upgrade, I use three sets of options: 1. Export in SQL Server 6.5 and its external tool BCP ... out command; use one of the table tests, the result is imported SQLServer2000
Fault 1: BCP ... IN Tip "String Data, Right Tone"; Fault 2: The field of Chinese characters shows garbled; fault 3: The time field format with Chinese characters cannot be successful when imported with BCP IN.
2, use 2000 upgrade wizard; fault 1: overcome heavy difficulties (requires patch, resetting the landing method, etc.) After upgrading, report some unrecognizable errors, then, open the database, there is nothing; fault 2: Upgrade wizard cannot be performed High transparency, unable to track errors.
3. Configure the SQL6.5 data source with the ODBC, then use the SQLServer 2000 import wizard; fault: After changing more than 90 new table names, start copying, success. However, Chinese characters show garbled. Subsequently, a Latin General Bin1 is re-established in 2000, which is re-imported, and the fault is still.
[This article is published by IUPRG, IUPRG @ Tom.com, www.newsoftstudio.com]
After a week's repeated test, repeatedly failed, and finally decided to use the first solution to be more flexible, first organize the specific steps to readers.
1) Open SQLServer Enterprise Manager on the NT Server 4.0 machine, select the database to be upgraded, select the table and generate the script for the creation table; 2) Generate a script that creates a stored procedure; 3) If there is a view, please save as text, Use; 4) Open the ISQL / W tool, select the database to be upgraded, run the following statement to generate a bcp out / in command (otherwise, manually edit very hard): select 'bcp myDatabase.dbo.' Name ' OUT ' ' / DATA / ' NAME '.TXT' '-USA -P *** -SSERVERNAME -C -C1252' from myDatabase...ysObjects where type = 'u' Description: MyDatabase is the database name. '/ Data /' is a Data directory in the directory of the BCP and needs to be established in advance. *** Database password. ServerName server name. 1252 is the default code page of SQLServer6.5, must be specified, otherwise the output of the Chinese characters will be garbled. After running, save the results as bcpout.bat and then replace the OUT to generate the bcpin.bat file to run on the SQLServer2000 machine.
5) Ok, to use BCP, but we can't use BCP tools on SQL6.5, in my test, use it exported with the date field of the date field, such as "2003 December 23 15:09 AM "The format, so that the copy error that does not recognize the date format when imported with BCP IN on the SQL Server 2000 machine. Here, you must use the BCP tool for SQL Server 2000. Under the working path (C: / MSSQL / BINN, rename the original bcp.exe file) BCP65.exe); Note: This BCP should be used with odbcbcp.dll (70 edition), otherwise the version error occurs when running the BCP export. Or cannot open the database. If you have a friend can't find it, please go: http://iuprg.51.net/odbcbcp.dll download (this file is all Microsoft companies)
[This article is published by IUPRG, IUPRG @ Tom.com, www.newsoftstudio.com] 6) Create a UPGRADEDB directory on the NT machine, copy bcpin.bat, bcpout.bat, and copy the script you just generated, and then establish a Data directory; 7 Make sure the SQL Server service is started, double-click to run bcpout.bat, wait end; 8) Copy or share the UPGRADEDB directory or share it on the SQLServer2000 machine; 9) Open SQLServer 2000 Enterprise Manager, create the same name's database, sort rules for Chinese_prc_bin; 10) Open Query Anlyzer, select the database, run the script of the established table that has just generated, establish a table structure; run the built-in script that runs the stored procedure generated, generates a stored procedure ... New and old library; 11) Open a command prompt State, enter the Upgradedb directory, run bcpin.bat, wait for the end (if an error, check the password, server name, path, etc.). 12) Open Enterprise Manager, open the database, select the table to return all lines, check the data found that the Chinese characters are normal, and the date format is normal. 13) Copy the original application to the SQLServer2000 machine run, find everything as usual, to this upgrade the database successfully!
=================== I have limited level, it is inevitable, I hope that you will finish, criticize.
IUPRG (遨 云驰 行, 图) 2004-4-8 [This article is published by Iuprg, Iuprg @ Tom.com, www.newsoftstudio.com]
