0

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?

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • 2
    Have you considered, maybe, taking the period out of the name? "Doctor, it hurts when I do this..." – Aaron Bertrand Nov 10 '13 at 05:05
  • If the table is actually in the report schema, then its name is not `reports.datasetstatus`. Its name is `datasetstatus` and it is in the `report` schema. Being specific about this kind of thing prevents all kinds of time from being spent troubleshooting the wrong thing. – Aaron Bertrand Nov 10 '13 at 05:38

2 Answers2

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';
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • This didnt worked,the same error,more than 3 prefixes,dbo.Reports.DataSetStatus' contains more than the maximum number of prefixes. The maximum is 3. – TheGameiswar Nov 10 '13 at 05:01
  • Thanks,the table name is [reports].[datasetstatus],so when i gave the query like [reports.datasetstatus],error is no table name found,when i gave like [[reports].[datasetstatus]],syntax error.I will try to create a table named test on destination and populate first the data into table through sql job and access the table test using linked server.I am sysadmin on both source and destination.I will probably have to stick with this. – TheGameiswar Nov 10 '13 at 05:27
  • I think that his schema is report. That's why SQL can not find [report.datasetstatus] – Bogdan Bogdanov Nov 10 '13 at 05:28
1

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.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43