BUG: SQL 6.5 SEM Transfer UsingTCP/IP Registered Servers

• Microsoft SQL Server, version 6.5

SYMPTOMS

You can use Microsoft SQL Server Enterprise Manager (SEM) with registered server names that contain a period (".") or a hyphen ("-") in the name. For example, 157.57.8.256 (a TCP/IP Address) or Test-1 (a named pipe Advanced configuration entry) and either a default Network Library of TCP/IP Sockets or Named Pipes on the source server. However, when you transfer a database from the source server to the destination server, you will lose of all data in all tables from the source server and no data is transferred to the destination server. This only occurs when the transfer is scripted at the same physical computer as the source server.

When you use a named pipe without a period or hyphen (\\ServerName\Pipe\Sql\Query, for example) to register servers and the default Network Library of Named Pipes on the source server, and attempt to transfer a database from the source server to the destination server, no data or structures are transferred to the destination and the source data is preserved.

WORKAROUND

In SQL Server version 6.5, use SEM to register SQL Servers with the machine name, for example ServerName, to transfer databases.

You can also work around this problem by using Advanced Client entries the SQL Client Configuration Utility if there is no period or hyphen in the server name. Consider transferring from the destination server and pulling data from the source server.

Another alternative is to use a Scheduled Transfer. You can use Xfragnet.exe to set up a scheduled task that automatically executes on the destination server. For additional information about using Xfragent.exe, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q172012
   TITLE     : INF: How Scheduled Data Transfer Works

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Please note that you cannot use a period or hyphen in a server name. This is clearly documented in SQL Server Books Online; see the "Registering Servers" topic. You should not use IP addresses as the server names when you register SQL Servers either. Also note that the use of internet domain names can also cause the same problem because a domain name contains periods (for example, myserver.microsoft.com").

When using the SEM with registered servers that contain a period or hyphen in the server name of the source server, the database and all table structures exist but with no data. On the destination server the database exists, but no table structures are created and there is, therefore, no data. The transfer completes with the message

   The Transfer was completed Successfully

If you are logged on to the destination server and transfer the database from the source server to the destination server using registered servers with a period or hyphen, database structures and all data are transferred completely.

This is not a problem when you use SQL Transfer Manager on the destination server because the transfer function is unable to process periods or hyphens in the server name, and thus makes a connection to the local pipe. In this instance, this happens to be the correct server to connect to because the local server is the destination server.

If you attempt the transfer using Windows 95 SEM with TCP/IP address registered servers, the transfer fails with Error 10004 and you receive the following error message:

  Transfer not completed.

   Transfer not complete. Please look at the error files for details.
   Error 21770: The name '<destination database name>' was not found
   in the database collection.

If you are using named registered servers (without a period or hyphen in the name) in the HOSTS or LMHOSTS files, the database structures and all data are transferred completely. Also, if you are using non-machine name advance entries for either TCP/IP or named pipes, the database structures and all data is transferred completely.