Linked server naming issue for a two part table name

Question

I have a table name like reports.datasetstatus, I am trying to query it using linked server with below query:

select [status] 
from   [server name].[database].dbo.reports.datasetstatus

With this query, I am getting the below error.

Max prefixes are three.

I changed the table name to [reports.datasetstatus] which is now throwing the table name not found error,[[reports].[datasetstatus]] is throwing a syntax error.

Can some one help me on this syntax?

Answer 1

I created an ill-advised table name on a linked server and was able to access it no problem. On the destination server:

USE dbname;
GO
CREATE TABLE dbo.[report.datasetstatus](status INT);

Then on the server that runs the query:

SELECT [status] FROM [server].dbname.dbo.[report.datasetstatus];

This worked no problem. If you are getting an error message like table not found, then it's either because you don't have permission, you spelled the table wrong, or it is in a different schema than dbo. For example, if the table is actually in the report schema, then you shouldn't also specify dbo:

SELECT [status] FROM [server].dbname.report.datasetstatus;

Of course, if your table is named report.datasetstatus, a smarter solution would be to not use such a terrible table name in the first place, whether there are linked servers involved or not. One way to fix this is to replace the . in the name with an _:

EXEC [server name].[database]..sp_rename 
    @objname = N'dbo.[report.datasetstatus]', 
    @newname = N'report_datasetstatus',
    @objtype = N'OBJECT';