Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Accessing remotely through views Expand / Collapse
Author
Message
Posted Friday, December 7, 2007 12:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 7, 2007 12:24 PM
Points: 2, Visits: 2
Hi All
I am new to sql world. I want to access remote server database through the views on my local system database. Please help.

Sac
Post #430570
Posted Friday, December 7, 2007 3:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 8, 2014 12:54 AM
Points: 2,552, Visits: 2,598
You need to create a linked server using sp_addlinkedserver procedure

EXEC sp_addlinkedserver   
@server='TestServer',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='YourServer\InstanceName'

Then, create linker server login

EXEC sp_addlinkedsrvlogin 'TestServer', 'false', 'sa', 'sa', 'password'

Thats it.

Now query the server,

select * from TestServer.master.dbo.sysobjects



--Ramesh

Post #430601
Posted Friday, December 7, 2007 4:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:05 AM
Points: 3,461, Visits: 353
Ramesh,

You can also use opendatasource to query remote servers. try that if u need it only for the hour else go for linked servers for permanent solution.


Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Post #430618
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse