Linked Server (SQL 2000 to SQL 6.5)

Hi,

I am trying to setup up a linked server using 'sp_addlinkedserver'. The host server is a SQL 2000 and the remote server is SQL 6.5. Both the servers are on the same domain.

When the system stored procedure is run, enterprise manager shows the name of the linked server but there are no tables.

I get error 'SQL Server does not exist or access denied' when trying to run query against a database on the remote server.

Does SQL server 6.5 support linked servers?

Please help,

DPKAOriginally posted by DPKA
Hi,

I am trying to setup up a linked server using 'sp_addlinkedserver'. The host server is a SQL 2000 and the remote server is SQL 6.5. Both the servers are on the same domain.

When the system stored procedure is run, enterprise manager shows the name of the linked server but there are no tables.

I get error 'SQL Server does not exist or access denied' when trying to run query against a database on the remote server.

Does SQL server 6.5 support linked servers?

Please help,

DPKA

Hi from me again,

now I am able to add the SQL server 6.5 database as a remote database but when trying to run a query against a table on this remote database, i get 'Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user '\'.

I have used the stored procedures sp_helplinkedsrvlogin and sp_addlinkedsrvlogin and have access to both the databases.

Any help appreciated,

DPKA|||Well...did you set up permissions on the linked server?

Even if you ass a linked login, it still has to be known to the server...

I have never tried adding a link to 6.5 though...

but I did find this in BOL

Note To use the Microsoft OLE DB Provider for SQL Server 2000 in SQL Server version 6.x, run the \Microsoft SQL Server\MSSQL\Install\Instcat.sql script against the version 6.x SQL Server. This script is essential for running distributed queries against an SQL Server 6.x server.