0

I want to pull some data into my queries from a remote server (also SQL Server 2008), because some related data is on the remote server but not the local server.

The "add linked server" wizard in SQL Server 2008 asks me for a "Linked Server Name", so I make up a name like "remote_server_name". It also asks me to choose the server type, so I choose "SQL Server" for the server type, because that's what it is.

Where do I specify the IP address or domain name of the remote server? Where do I specify the instance name? It tells me the connection fails, well obviously because it never asked me where the server is located or what its instance name is. It happens to be the default instance that I want to connect to on the remote machine.

Triynko
  • 3,418
  • 6
  • 31
  • 30

1 Answers1

1

If you're using the wizard and selecting "SQL Server" as the server type then you need to provide the instance name of the remote SQL server as the value for Linked Server Name. This will either be the hostname of the server if using the default SQL instance, or the fully qualified SQL named instance in hostname\instancename form.

Note that if your remote server is a named instance then you'll want to use brackets around the linked server name when performing operations against it (after it's created).

SELECT Id from [myserver\instance1].[mydatabase].[dbo].[Products]
squillman
  • 37,883
  • 12
  • 92
  • 146
  • I tried that at first (just using the hostname), and it didn't work, but that's probably because I had the port blocked. I later unblocked it, but never tried this original approach. I did end up getting it to work using the SQLOLEDB provider specifying a data source and port, but I'm going to try just using the hostname again as suggested. I think I prematurely ruled out this approach because I thought the dots in the URL would interfere with the name, but mentioning the use of delimited names made me quickly realize that it should work after all. Thanks for responding. – Triynko Mar 20 '12 at 14:21
  • Does this approach allow you to specify a custom port? I'm not using the default port. Where would the port go in the hostname? Would it require a comma or colon after the hostname for example? – Triynko Mar 20 '12 at 14:23
  • To answer my own question, a custom port *can* be specified in the name. So a name like "my.domain.com,1433" works fine, and can be queried like [my.domain.com,1433].[databasename].[owner].[tablename]. Thank you! – Triynko Mar 20 '12 at 14:30
  • Just as an FYI to anyone reading this, if you want to use Windows Authentication to connect, you probably need active directory set up and have the "client" of the linked server set as a trusted domain somehow. For simpler setups with no active directory, it seems that you must have SQL Server authentication enabled and set up an SQL Server login on the linked server, and specify that account as the remote login (and password) for the linked server. – Triynko Mar 20 '12 at 14:38