SELECT from default catalog of linked server

Question

I was able to create an alias for a linked server using this method. While linking the server I specified default catalog so now I can execute remote SP by skipping schema and Database name e.g. this works:

EXEC REMOTE_SERVER_ALIAS...REMOTE_STORED_PROCEDURE

But for some reason I cannot use the same approach for simple select:

SELECT * FROM REMOTE_SERVER_ALIAS...REMOTE_TABLE

Attempting to execute this throws error:

An invalid schema or catalog was specified

Any idea how to make it work?

P.S. I am aware about OPENQUERY approach, but rather not use it due to its limitations

Answer 1

I think you have to specify the schema for the table, so you'll have to use the full path for that table:

Select * From [Remote_Server_Alias].[Database_Name].[Schema].[Table_Name]

I know you've specified the default catalog for the linked server, but you may have to include it in there anyway. In any event, you can always try leaving one piece or another off, but the last time I used a linked server, I believe this was how I had to reference the table.