Accessing remotely through views

• sac-573340

  SSC Journeyman

  Points: 88

  More actions

  December 7, 2007 at 12:40 am

  #179759

  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

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  December 7, 2007 at 3:56 am

  #758001

  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
  

  ---

• Sugesh Kumar

  One Orange Chip

  Points: 27311

  More actions

  December 7, 2007 at 4:56 am

  #758015

  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