Selecting specific columns from linked SQL Server

Question

I have a physical server running two instances of SQL Server, one 2008 R2 (port 1433) and one plain 2008 (port 1444).

I have a linked server setup and the following query runs fine from 2008 R2

SELECT * 
FROM [LINKEDSERVER,1444].[DBNAME].[dbo].[TABLENAME]

When I attempt to run this query I get the error the multi-part identifier could not be bound.

SELECT [LINKEDSERVER,1444].[DBNAME].[dbo].[TABLENAME].[COLUMNNAME]
FROM [LINKEDSERVER,1444].[DBNAME].[dbo].[TABLENAME]

Can anyone please shed some light on what is going on here? I assume I have a simple syntax error or something but all the examples I can find are just Select *, and that's not what I need.

Have you tried just using the column name in the SELECT, rather than fully qualifying it? — Siyual
– Siyual, Commented Jun 16, 2016 at 15:52
It has to be fully qualified as my queries span across multiple instances of SQL Server and even multiple databases within the same instance. I know it sounds convoluted, however, it has to be this way for reasons I'm not going to get into here. The synonym solution worked well for me. I could have used table aliases, however, I'm querying this constantly and the short name helps immensely. — user2625842
– user2625842, Commented Jun 17, 2016 at 17:25

Matt · Accepted Answer · 2016-06-16 16:06:12Z

1

4 levels is the deepest you can go, but if you want to reference a specific column in the case above you can use a table alias. Putting a space then a letter or shortened abbreviation after the table name in the from space. The you can reference that alias in the query to alleviate your issue.

SELECT abrv.[COLUMNNAME]
FROM [LINKEDSERVER,1444].[DBNAME].[dbo].[TABLENAME] abrv

Yet another alternative if you are going to access it via linked server a lot is to actually create Synonym for the table (https://msdn.microsoft.com/en-us/library/ms187552.aspx) and use it like it is a local table.

edited Jun 16, 2016 at 16:06

answered Jun 16, 2016 at 15:54

Matt

14.5k2 gold badges19 silver badges29 bronze badges

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

1 Comment

Siyual Over a year ago

Just a bit of a terminology pet peeve - these should not be called table "abbreviations." They're table aliases.

Collectives™ on Stack Overflow

Selecting specific columns from linked SQL Server

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