How to access the data of other Server..

[tapks]

I am wkoing in a Server from which I have to access the other Server also. Both the SQL server are of version 7.0.

I can able to access the Server2 data from Server1 through Enterprise Manager. But throught a Select statement how can I access the the data of the Server2?

I hve added the Server2 through Sp_addlinkedserver by doing the following:


EXEC sp_addlinkedserver @server = 'TEST', @srvproduct = 'SqlServer', @provider = 'SQLOLEDB', @datasrc = '[192.168.0.236]'

After this when I tried to do the following :

select * from Test.imwemv11.dbo.InstMast

I shows the error 'Server: Msg 6, Level 16, State 1, Line 1
Specified SQL server not found.'

But I can connect to the Server2 through Enterprice manager & access the data. Can any body tells me how to solve it ?

Thanks in advance.

Tapks

 

[markVII]

Make sure that your DTC (Distributed Transaction Coordinator) service is running; you find it in SQL Server 2000 Service Manager

 

[ClaireHCM]

I think the problem should be @datasrc = '[192.168.0.236]'

You should know the server name and instance name of
the server with IP add as[192.168.0.236]

If there is only one instance on that server(say only MSSQL 7.0)installed in that server.All you need to worry is what is the server name.

If you have more than one instance installed in that server.
you have to find out which instance you want to link (some box will install both 7.0 and 2000)

So instead of
EXEC sp_addlinkedserver @server = 'TEST', @srvproduct = 'SqlServer', @provider = 'SQLOLEDB', @datasrc = '[192.168.0.236]'

change to

EXEC sp_addlinkedserver @server = 'TEST', @srvproduct = 'SqlServer', @provider = 'SQLOLEDB', @datasrc = 'Server2'
(server2 is just a example,you have to change it to whatever name it is )

 

[tapks]

Thanks a lot ClaireHsu.

The problem was with the @dataSrc. After changing the same it is working fine.

Thanks once again.

TapKs