2

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?

Improve this question
4
  • 1
    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
    – DenisT
    Commented Oct 1, 2014 at 10:55
  • Hey, it works, thanks! (stupid me) But how can I refer to the server with a better name, rather than the IP address?
    – carlo.borreo
    Commented Oct 1, 2014 at 10:58
  • 1
    It's a public IP address. Hopefully 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.
    – DenisT
    Commented Oct 1, 2014 at 11:18
  • That's all I wanted to know. Please make this an answer. Thanks
    – carlo.borreo
    Commented Oct 1, 2014 at 13:33

1 Answer 1

Reset to default
5

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]
Improve this answer
1
  • I can now query the remote database: select * from [12.34.56.78].TESTDB.dbo.test_table It seems SYNONYM does not work as I expected. I tried with: > CREATE SYNONYM REMOTESERVER [12.34.56.78] > select * from REMOTESERVER.TESTDB.dbo.test_table Error is: Msg 7202, Level 11, State 2, Line 1 Could not find server 'REMOTESERVER' in sys.servers Even better, I would like to do something like: > CREATE SYNONYM REMOTEDB [12.34.56.78].TESTDB
    – carlo.borreo
    Commented Oct 2, 2014 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.