CODEWITHSUNDEEP
sqlsql-serverlinked-server
TheGameiswar
TheGameiswar

Reputation: 28920

Linked server naming issue for a two part table name

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?

Upvotes: 0

Views: 846

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

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';

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

While the server.database.owner.table syntax is available, in many cases you are better off using openquery. The reason is that if you want to do this:

 select somefields
 from server.database.owner.tablename
 where whatever

what will happen is that the entire contents of the remote table will come across before the where clause is applied. If the table has a lot of records, your queries will be painfully slow.

Upvotes: 1

Related Questions