Microsoft KB Archive/246181

= INF: SQL 6.5 Database Upgrade with a Space in the Path to Master.dat Fails =

Article ID: 246181

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q246181



BUG #: 56979 (SQLBUG_70)



SUMMARY
When you attempt to perform a database upgrade with the Upgrade Wizard, the SQL Server Upgrade Script Interpreter screen displays a message box at the point of EXPORT and IMPORT VIA NAMED PIPES titled Informational Files Found. The message box contains the following two files:

EXPORT and IMPORT VIA NAMED PIPES - 003MODEL.ERR

EXPORT and IMPORT VIA NAMED PIPES - 003MODEL.OUT

The contents of the EXPORT and IMPORT VIA NAMED PIPES - 003MODEL.ERR file is as follows:

Export Exit Code: -1 - d:\mssql7\upgrade\export.exe -CodePage 1252

-DeviceType Pipe -AllTables yes -MasterPath C:\MSSQL\test bug

DATA\MASTER.DAT -DatabaseName model -DevicePath \\.\pipe\~cnvpipe0

I do not recognize the option 

Usage :

export.exe

-MasterPath _MASTER_DEVICE_PATH_

Import Exit Code: -1 - ~cnvpipe0

Msg 4854, Level 21, State 1, Server LARRYCHE2, Procedure, Line 1

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

Time:11-15-1999 10:49:10.558

Error return from function CreateFile

in file CnvPipe.cpp

Line=140

rc=2

Msg=The system cannot find the file specified.



The EXPORT and IMPORT VIA NAMED PIPES - 003MODEL.OUT file contains the following:

One Machine Data Transfer...

Export Exit Code: -1 - ~cnvpipe0

Import Exit Code: -1 - ~cnvpipe0

This condition may be caused by the SQL Server 6.5 installation having one or more spaces in the path of the directory to the Master.dat file.

For example: C:\MSSQL\test bug DATA\MASTER.DAT

The following excerpt is from SQL Server 6.5 Books Online:

The default SQL Server 6.5 root directory is C:\MSSQL, although during installation the setup program allows you to change this setting. SQL Server directory names must conform to the FAT 8.3 naming convention, even if the directory is installed on an NTFS volume. Do not use spaces in the directory name.



MORE INFORMATION
Relocate the SQL 6.5 Master.dat file to a directory path that does not have spaces. To do this, perform the following steps:  If SQL Server 7.0 is on the same computer as SQL 6.5, version switch to 6.5. Match the SQL Server 6.5 device names with their paths using the sp_helpdevice stored procedure.  Install the stored procedure sp_movedevice found in SQL Server 6.5 Books Online. Use master Go

Exec sp_configure "allow",1 RECONFIGURE WITH OVERRIDE go

CREATE PROCEDURE sp_movedevice @@devname varchar(30), @@newpath varchar(255) AS BEGIN EXEC sp_configure "allow",1 RECONFIGURE WITH OVERRIDE BEGIN TRAN UPDATE sysdevices set phyname = @@newpath WHERE name = @@devname IF @@rowcount <> 1 BEGIN PRINT "**********************ERROR**********************" PRINT "Moved failed - more or less than one row affected" PRINT "**********************ERROR**********************" ROLLBACK TRAN END ELSE BEGIN PRINT "Device moved successfully" PRINT "Change will take effect next time you start SQL Server" COMMIT TRAN END EXEC sp_configure "allow",0 RECONFIGURE WITH OVERRIDE END Go

EXEC sp_configure "allow",0 RECONFIGURE WITH OVERRIDE   Run sp_movedevice master, 'new path' as follows: exec sp_movedevice MASTER, 'D:\MSSQL\DATA\MASTER.DAT' reconfigure To ensure that the changes made were the ones intended, run the sp_helpdevice stored procedure to confirm that the new location is correct.  Open the SQL Server 6.5 Enterprise Manager, highlight the server, click Server, click SQL Server, and then click Configure. Modify the "Master Database Path" option on the Server Options tab of this dialog box to look like this:

D:\MSSQL\DATA\MASTER.DAT

WARNING: After completing this step, you must make sure your parameters are in the correct order or you may run into another possible hindrance when using the Upgrade Wizard. To check parameter order, click the PARAMETERS button. In the window titled Existing Parameters make sure the sequence of vertical order for your server parameters matches the following:

-dD:\MSSQL\DATA\MASTER.DAT

-eD:\MSSQL\LOG\ERRORLOG

For additional information concerning this problem, see the following article in the Microsoft Knowledge Base:

231988 BUG: 7.0 Upgrade Loops When 6.5 Parameters Are Reversed

 Stop the SQL Server 6.5 services.</li> Move the Master.dat file to the new location (for example D:\Mssql\Data).</li> Start the SQL Server 6.5 services.</li> Check the SQL Server 6.5 error log to see if all the databases have recovered or if there are any marked as suspect. Correct any errors. If there are no errors, move to the next step.</li>  Use the Upgrade Wizard to perform the upgrade.NOTE: If you want to have all SQL devices (MSDBData and MSDBLog, and so forth) under the new path, you can repeat steps 4, 6, 8, and 9. For example: exec sp_movedevice msdbdata, 'd:\mssql\data\msdb.dat' exec sp_movedevice msdblog, 'd:\mssql\data\msdblog.dat' </li></ol>

Another workaround is as follows: <ol> If SQL Server 7.0 is installed on the same computer as SQL Server 6.5, uninstall SQL Server 7.0.

NOTE: If there are SQL 7.0 databases already existing, do not remove the data files/folder when removing SQL Server 7.0. Instead, before removing SQL Server 7.0, rename the folder that contains the data and log files to DATA_OLD so that the folder will not be inadvertently deleted during the uninstall, and to ensure that you will be left with a backup of the current state of the SQL Server 7.0 before the uninstall.</li> Create and save a script for the later re-creation of your SQL 6.5 user databases by running the stored procedure sp_help_revdatabase.</li> Dump the databases.</li>  Run the following script, substituting for your particular databases' name. Save the output, and search for any "Msg" statements. If there are no "Msg" error messages, continue to the next step. If there are, resolve those error messages before continuing. DBCC TRACEON (3604) go DBCC CHECKCATALOG (pubs) go DBCC TEXTALL (pubs) go DBCC NEWALLOC (pubs) go DBCC CHECKDB (pubs) </li> Uninstall SQL Server 6.5.</li> Reinstall SQL Server 6.5 with the Master.dat file being written to a folder without spaces.</li> Re-create the user databases by using the script from the sp_help_revdatabase output.</li> Restore the SQL Server 6.5 databases.</li>  Run the following script again, substituting for your particular databases' names. Save the output, and search for any "Msg" statements. If there are no "Msg" error messages, continue to the next step. If there are, resolve those error messages before continuing. DBCC TRACEON (3604) go DBCC CHECKCATALOG (pubs) go DBCC TEXTALL (pubs) go DBCC NEWALLOC (pubs) go DBCC CHECKDB (pubs) </li> Install SQL Server 7.0. If there were previous SQL 7.0 user databases in existence prior to uninstalling, then after the install, swap the new MSSQL7\DATA folder name with the original DATA_OLD.</li> Use the Upgrade Wizard to perform the upgrade.</li></ol>

Keywords: kbinfo kbpending KB246181

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.