"Invalid object name" when executing a query from a linked server on SQL

Question

I have created linked servers on SSMS before but I am having trouble with this particular server. The linked server has come back as successfully connected and I can see the available tables, however, when I do a simple select query it's as if it cannot locate any of the tables within the database.

Would anyone have any clue as to why i'm receiving this error ?

SELECT * FROM [AMP].[default].[Import_records]

Msg 208, Level 16, State 1, Line 4
Invalid object name 'AMP.default.Import_records'.

You should try your query in the below form. SELECT * FROM <linkedServerName>.<DatabaseName>.<SchemaName>.<TableName> — Suraj Kumar
– Suraj Kumar, Commented Feb 18, 2020 at 14:51
Because you have to use 4 part naming, not 3 when querying a linked server. — Sean Lange
– Sean Lange, Commented Feb 18, 2020 at 14:52
@SurajKumar there's no schema, reason why its a 3 part name rather than a 4 part name — jcoke
– jcoke, Commented Feb 18, 2020 at 14:52
@SeanLange If there's no schema, what is this replaced with ? — jcoke
– jcoke, Commented Feb 18, 2020 at 14:53
Every object has a schema. You can always skip it to use the default. So it would be something like [LinkedServer].[DatabaseName]..[ObjectName] Notice the two periods between database and object. — Sean Lange
– Sean Lange, Commented Feb 18, 2020 at 15:11

Thom A · Accepted Answer · 2020-02-18 15:20:23Z

1

Perhaps you would be better off with OPENQUERY:

SELECT *
FROM OPENQUERY(N'AMP',N'SELECT * FROM default.Import_records')OQ;

You will be able to do this with 4 part naming, however, we're not getting the information we need to able be able to really answer with that solution.

answered Feb 18, 2020 at 15:20

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jcoke Over a year ago

The provider indicates that the user did not have the permission to perform the operation. It now shows this error, so I probably need some permission granted before this query can work, but seeing as it's not giving me the original error i'll mark this as the answer.

Collectives™ on Stack Overflow

"Invalid object name" when executing a query from a linked server on SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related