How To Connect Remote Server's DataBase from another's Server's QueryWindow

Bhaskar_ExcelIndia




Please Help Me to do this...


Consider i hv already logged in

sql server 2005
ip no : 192.168.2.20
db name: framework

from this query window is it possible to access the objects(tables,stored porc's)
of another server's database

for example ,

sql server 2005
ip no : 192.168.2.21
db name: framework1

...................................................

Im able to access the same server's db objects
consider im frame work db
declare @qry as nvarchar(max)
set @qry= '
use [master]
select * from [Sales]
'
exec (@qry)
... but when i use use statement like,(its not working)
use 192.168.2.21
use [master]


Please help me out .....

Martin_McNally

No it is not possible from that query window unless you use linked servers.

Take a look at: http://www.sqlservercentral.com/columnists/ksonti/anintroductiontolinkedservers.asp

Martin

Hilary Cotter

If the servers are part of a replicated topology you will have to set the server option for data access to true,

is sp_serveroption 'ServerIWishToQuery','Data Access','True'

and then

select * from ServerIWishToQuery.Database.dbo.TableName

Bhaskar_ExcelIndia

Thank u Hilary...

It was really helpful...

as u said i executed

exec sys.sp_serveroption [192.168.2.20],'data access','true'

It is Giving this type of error ...

Msg 15015, Level 16, State 1, Procedure sp_serveroption, Line 101
The server '192.168.2.20' does not exist. Use sp_helpserver to show available servers.



I think we have to link the servers ..

can u please guide me how to do it.......