December 3, 2003 at 1:21 pm
Having made several attempts to set up replication from a SQL 7 instance to SQL 2000, I am now ready for advice.
What are the known gotchas or recomendations to do this properly.. If anyone could point to me to an FAQ or step by step on doing this I would appreciate it..
Edited by - sbarclay on 12/03/2003 1:21:38 PM
December 9, 2003 at 12:00 pm
This was removed by the editor as SPAM
December 10, 2003 at 8:11 am
Heya,
For starters, you can look under the following in Books Online:
Replication between different versions of SQL server
Next, since that won't be very informative, try this google link (there was too many hits for me to wade through):
Hope it helps.
Joey Peloquin
December 10, 2003 at 8:24 am
How to Connect to an SQL Server 2000 Named Instance with the Previous Version's Client Tools
INF: How to Connect to an SQL Server 2000 Named Instance with the Previous Version's Client Tools
The information in this article applies to:
Microsoft SQL Server 6.5, 7.0
Microsoft SQL Server 2000 (all editions)
Summary
Microsoft SQL Server 2000 introduces multiple instances. However, client tools such as Query Analyzer or ISQL/W, which are available
with previous versions of Microsoft SQL Server, might experience difficulty connecting the non-default Named Instances.
For example, if you try to connect to a named instance by using \\computer_name\instance_name, the client tools might return this error message:
Error: \\computer_name\instance_name
unable to connect server \\computer_name\instance_name
Server: Msg 67, Level 61, State 1 [Microsoft][ODBC SQL Server Driver] Client unable to establish connection.
More Information
You must have Microsoft Data Access Components (MDAC) version 2.6, or later, to connect to a named instance by using the \\computer_name
\instance_name format. Microsoft recommends that you upgrade your client tools and MDAC version to work with Microsoft SQL Server 2000.
However, a workaround is possible by using server aliases on a client computer that only has client tools of previous versions and MDAC installed.
Please note that this workaround provides limited functionality. You can not use Microsoft SQL Server 7.0 Enterprise Manager to manage SQL Server 2000,
although you can connect to a SQL Server 2000 named instance with the Microsoft SQL Server 7.0 Query Analyzer, Osql.exe or
Microsoft SQL Server 6.5 ISQL/W and Isql.exe.
The following steps describe how to configure a server alias to use either TCP/IP sockets, or Named Pipes to connect to a SQL Server 2000 server directly
without having to specify the named instance name.
Configure an Alias by Using TCP/IP Sockets
NOTE: To configure an alias using TCP/IP, you must provide the server name and TCP/IP port number.
Determine the SQL Server instance port number.
To find the SQL Server instance port number:
On the Microsoft SQL Server 2000 server, start the SQL Server Network Utility.
Click the General tab, and then select the instance you want from the Instances drop-down menu.
Highlight TCP/ IP, and then click Properties. The port number for this instance is shown. Write down this port number for use later.
Configure the server alias on the client computer.
For SQL Server 7.0
Start the Client Network Utility.
In the General tab of the Server Alias Configuration dialog box, click Add, and then click TCP/ IP.
Enter an alias name in the Server Alias text box. The alias can contain any name.
Enter the SQL Server 2000 computer name or IP address in the Computer Name text box. Do not add an instance name.
In the Port Number text box, enter the port number you recorded in step 1. Click OK twice.
For SQL Server 6.5
Start the SQL Server Client Configuration Utility.
Click the Advanced tab, and then click Add/ Modify.
Enter an alias name in the Server text box.
Select TCP/ IP Sockets as the DLL name.
In the connection string, enter the SQL Server computer name followed by a comma (",") and the port number you recorded in step 1. For example:
ComputerA, 1523
Do not add an instance name. Click Add/ Modify.
Click Done. NOTE: Microsoft SQL Server 6.5 does not support trusted connections with TCP/IP. Therefore, you might see the following error message
when you attempt to use SQL Server 6.5 client tools to make a trusted connection to SQL Server 2000 when using TCP/IP sockets.
Msg 18452, Level 14, State 1: Login failed for user 'user_name'. Reason: Not associated with a trusted SQL Server connection. DB-Library: Login incorrect.
Please use Named Pipes to make a trusted connection when you use Microsoft SQL Server 6.5 client tools.
You should be able to use the server alias to connect to the named instance of Microsoft SQL Server 2000 by using SQL Server 7.0 or 6.5 client tools
such as Query Analyzer, Osql.exe or Isql.exe.
Configure an Alias Using Named Pipes
NOTE: In order to configure an alias by using Named Pipes, you must provide a Server name and a pipe name.
Determine the server instance pipe.
On the Microsoft SQL Server 2000 server, start the Server Network Utility.
In the General tab, select the instance you want from the Instances drop-down menu.
Highlight Named Pipes, and then click Properties. Write down the pipe name for use later.
Configure the server alias on the client computer.
For Microsoft SQL Server 7.0
Start the Client Network Utility.
In the General tab of the Server Alias Configuration dialog box, click Add, and then click Named Pipes.
Enter an alias name in the Server Alias text box. The alias can contain any name.
Enter the pipe name you recorded from step 1.
Make sure that you change the "." (period) to the actual SQL Server Name. For example:
\\ComputerA\pipe\MSSQL$s2000\sql\query
Please do not add the instance name. Click OK twice.
For Microsoft SQL Server 6.5
Start the SQL Server Client Configuration Utility.
Navigate to the Advanced tab.
Enter an alias name in the Server text box.
Select Named Pipes as the DLL name.
In the connection string, enter the pipe name you recorded in step 1.
Make sure that you change the "." (period) to the actual SQL Server computer name.
For example:
\\ComputerA\pipe\MSSQL$s2000\sql\query
Do not add the instance name.
Click Add/ Modify, and then click Done.
Now you should be able to use the server alias to connect to the instance by using the SQL Server 7.0 client tools such as Query Analyzer, Osql.exe or Isql.exe.
December 10, 2003 at 8:27 am
Please see that attached. It details a method of accessing SQL 2000 database via the SQL 7 ODBC Driver.
This is needed because the SQL 7 driver can't handle the new SQL2000 server\instance names.
We used the above topic for configuring replication from a SQL7 to 2000 instance.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply