SQL Server 7.0 talking to SQL Server 2000

[mesavage]

I have a SQL Server 7.0 database I'm trying to get to talk to a SQL Server 2000 database. The communication actually needs to be 2-way in the end.

I've been trying to use the sp_addlinkedserver stored procedure in conjuction with sp_addlinkedsrvlogin.

So far, her is what I have:

exec sp_addlinkedserver @server='NAICT011\MFGSQL', @srvproduct='',
@provider='SQLOLEDB', @datasrc='NAICT011\MFGSQL'

exec sp_addlinkedsrvlogin 'NAICT011\MFGSQL', FALSE,
NULL, 'engdb', 'engdb'

Then, I attempted to do the following query:

select * from [NAICT011\MFGSQL].[Drive Loaner Test].[dbo].[tbl_Drives_Out]
where userid='mbenue'

I get the following error:
[OLE/DB provider returned message: Client unable to establish connection]
[DBNMPNTW]ConnectionOpen (CreateFile()).

This is the SQL Server 7.0 trying to talk to the SQL Server 2000 database. If anyone can tell me what I'm doing wrong or tell me how they have done it successfully, I would be greatly appreciative! Thanks!

 

[TheBugSlayer]

Try dropping the linked servers and then re-linking using the wizard in EM (in the Security node) and visualizing the contents of the linked tables.

Do you need to use the domain name in the name of the remote servers? Have you tried
exec sp_addlinkedserver @server='MFGSQL', @srvproduct='',
@provider='SQLOLEDB', @datasrc='MFGSQL' ?

Good luck.

 

[mesavage]

The other server is a SQL Server 2000. In SQL Server 2000 you can have several "instances" of SQL Server on one server. So that is not actually the domain name, but the name of the server, and that instance of SQL Server 2000 is the NAICT011/MFGSQL.

For posparity sake, the final solution turned out to be several things.

1) Had to create an alias through the client network utility on the actual server.

2) Had MDAC version 2.5 on the SQL Sever 7.0 box. Apparently this has some issues. Downloaded 2.8 and it worked fine.

Thanks!