New Linked Server Uses Local Catalogs - Not Those on the Remote Server

• dbajunior

  SSCrazy

  Points: 2307

  More actions

  April 8, 2013 at 4:07 pm

  #276248

  I have tried every way I can think of to define a linked server so that I can query catalogs on a remote production server. Problem is, when I expand the catalogs on the new linked server, it shows the catalogs on the local server where it's defined - and not the catalogs on the remote server! Could it be that the login on the remote server is invalid - so it defaults to the catalogs on the local server? Here's the code:

  EXEC master.dbo.sp_addlinkedserver @server = N'ProdServer', @srvproduct=N'SQL Server'

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ProdServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'RPTSUSER',@rmtpassword='########'

  This definition seems to work fine (no errors). But when I look at the catalogs, hoping to see those on the remote server, I see the ones on the current server. What am I missing here? Thanks!

  Ok, this may help. Found out this morning, I CAN create this linked server (and its correct catalogs) on a server different from the one I am trying to create it on. So I assume there may be a setting to be changed on that server then?