Connectivity between SQL 7 and 6.5

[jcrivera]

Scenario:
Two SQL Servers - one being 7.0 and the other 6.5

Problem:
The 7.0 cannot see the 6.5 by either a distributed query or browsing in the Enterprise Manager. The only way I could see data in the 6.5 is by making a direct connectivity with the Query Analyzer, but this won't be so useful.

A coworker suggests using COM. Can anybody give me a hint on how to afford it? Is there another way?

Please advise.

J.C.

 

[TomSark]

You should be able to set up a linked server in 7.0 that uses the OLEDB provider for OBDC to see and query from the 6.5 server.

I haven't ever done this (6.5 and 7.0 talking together), but here's what I would do:
(1) On the 7.0 box, create an ODBC DSN that points to 6.5
(2) Configure a linked server on 7.0 that uses the OLEDB provider for ODBC
(3) Test the linked server in query analyzer by issuing a query that uses a fully qualified reference to the linked server.

If that works, then you can do whatever you want all from within 7.0. One drawback to linked servers is that they are usually slow.

There are obviously more solutions than this, but this should work and get you querying the 6.5 server from 7.0 as quickly as possible.

Let me know if this works... Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[jcrivera]

Greetings Tom...

It worked indeed. Thank you. Even more, the connectivity is not so slow, given the 6.5 server is onsite.

However, the unique difficulty we found is to add the ODBC connectivity in the Control Panel. After my partner and I worked this for several hours, we found that in the 7.0's Client Network Utility, the 6.5 box had to be defined using Named Pipes, instead of TCP/IP or Multiprotocol, otherwise the connectivity process would freeze.

After we figured out this, the process resumed normally.

Thanks again.

J.C.