linked server with 2 instance

[theresatan]

Hi, all

In domain A I have Server1(sql 7),
Server2(sql 2000).

In domain B I have Server3(sql 2000) with two instances(Red and Blue). Both instances' ports are not 1433.

My goal is adding Server3\Blue as a linked server in both Server1 and Server2.

In Server2 I added Server3\Blue as a linked server successfully by specified alias and port number.

But I failed in Server1.

I got 'unable to connect to specified server' error.

Any idea?

Thanks in advance,

[MAK]

1. what MDAC is on Server1. If it is MDAC 2.6 or 2.7 then it can see the instance names or else it always look for default server name. Install sql 2000 client on that server1.

2.can you ping the server3 from server1?
do a "ping" and "odbcping" from server1

[theresatan]

Mdac is 2.6 in Server1.

I can ping Server3 in Server1.

Do I have to install 2000 client?

Since this is a production server.

Thanks!

TT

[MAK]

go to server1.

create a ODBC connection to server2's default instance. If u can connect its fine. if not something is wrong.

create a ODBC connection to server3's (Server3\Blue). If u can connect server2 and not server3, it means it requires MDAC 2.7.

install SQL 2000 client on that box server1 during Off hours.

[MAK]

what SP is on server 1?

[rmiao]

Or add an alias that points to server3\blue with ip address and port used on server1, then create linked server to this alias.

[theresatan]

Server1 has sp3 on it.

I tried the alias with IP and Port, but it still doesn't work.

Thanks!

[rmiao]

But worked on my sql7 sp4 server.

[MAK]

I believe SP4 updates to MDAC 2.7 so it can recognizes the named instances.