SQL Server – Remote Linked Servers

linked-serversql server

I have a SQL Server 2008R2, and I want to connect remotely to a SQL Server 2012SP1, for which I am given:

IP: 12.34.56.78
Login: john
Password: pw1
Database: TESTDB
Table: test_table

The remote database is outside my LAN, but firewall is configured on both sides, so I can query it by opening SSMS directly from my database server.
I perform the following:

EXEC sp_addlinkedserver '12.34.56.78', 'SQL Server';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = [12.34.56.78], @locallogin = NULL , @useself = N'False', @rmtuser = 'john', @rmtpassword = 'pw1'

Up to now, no error, and I can see the linked server in SSMS, "Test Connection" is successful but I cannot query it.

select * from [12.34.56.78].TESTDB.test_table

Returns this error:

Msg 208, Level 16, State 1, Line 1
Invalid object name '12.34.56.78.TESTDB.test_table'.

What is the proper way to define a linked server from the Internet, and if possible assigning a nice name to it (rather than referring to it by IP)?
Should I be using SQLNCLI as a provider?

Best Answer

You are missing the schema name in your query. It should be [LINKED_SERVER].[DB_NAME].[SCHEMA_NAME].[OBJECT_NAME]. So in your case, [12.34.56.78].TESTDB.[HERE SHOULD BE YOUR MISSING SCHEMA].test_table

It's a public IP address. Hopefully it is a firewall's address that routes the traffic to a SQL Server! If it has a DNS name assigned to it, then you could use it. Or you could create a SYNONYM and forget about typing this long name.

CREATE SYNONYM [schema].[synonym_name] FOR [LINKED_SERVER].[DB_NAME].[SCHEMA_NAME].[OBJECT_NAME]
Related Question