2

I need to create a SQL View to query data on another SQL Server. This link explains how to do it: http://www.mytecbits.com/microsoft/sql-server/joining-tables-different-servers

However I cannot work out how to set up the parameters for the execution of the system stored procedures. When I run this;

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'LIVESQL', @srvproduct=N'SQL Server' ;
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LIVESQL', @locallogin = 'person', @rmtuser='person', @rmtpassword='password', @useself = N'True' ;
GO

I get a message saying;

Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 82 The server 'LIVESQL' already exists.

How do I fix this?

arame3333
  • 9,887
  • 26
  • 122
  • 205
  • 2
    Drop the existing linked server called `LIVESQL` or choose a different name. – Thom A Jun 15 '18 at 16:07
  • possible dupe: https://stackoverflow.com/questions/8887727/dropping-and-adding-link-servers – sniperd Jun 15 '18 at 16:50
  • What I am trying to do is write a View that queries data in a different server; "LIVESQL". My understanding is that I have to convert it to a linked server in order to do this. So the server is called LIVESQL, it already exists. But I need it to be a linked server. Is this the right way to do this? – arame3333 Jun 16 '18 at 19:26
  • The name of the linked server is just a reference, you can call it whatever you want. There is nothing to "convert", you will just add a link to the other server similar to a TCP/IP socket. If you already have a linked server called LIVESQL then make sure that you aren't currently using it in one of your processes, or make sure that it represents the same server you want to link it to before dropping and recreating it. – EzLo Jun 18 '18 at 06:47
  • OK. I have an existing server called LIVESQL. It is not a linked server. But I want my query to access a database on that server which for now it cannot do because it is not linked. – arame3333 Jun 18 '18 at 06:52

1 Answers1

2

I found I could get it to work by entering MSSQLSERVER\LIVESQL instead of just LIVESQL.

So

USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'MSSQLSERVER\LIVESQL', @srvproduct=N'SQL Server' ; GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MSSQLSERVER\LIVESQL', @locallogin = 'person', @rmtuser='person', @rmtpassword='password', @useself = N'True' ; GO

arame3333
  • 9,887
  • 26
  • 122
  • 205