How to access LinkedServer on another database in SQL Server

Question

I want to know how to access LinkedServer of databaseA from databaseB in SQL Server.

I need to know sample code. Thanks.

Answer 1

If the servers are already linked, are both running SQL Server (you're not linking to a different kind of database completely), and you are connected to databaseB with a login having appropriate permissions, here is an example:

SELECT <columns>
FROM ServerName.databaseA.schema.Table alias
WHERE alias.Column = 'SomeValue'

Note you need to use all four parts of the name (server, database, schema, object), and therefore you will definitely want to use a table alias. The schema is practically almost always dbo, and can often be elided with just an extra . ( FROM ServerName.databaseA..Table).

But as long as you get the name right, you can use items in databaseA as if they were there in B, including for JOINs and similar. However, performance can be poor, because the server for databaseB must put together an execution plan without knowing anything about indexes or statics from databaseA.

If the databaseA is something other than SQL Server, you will need to use OPENQUERY(). Like the first option, OPENQUERY() results can be given an alias and then used with JOIN, APPLY, subquery, etc, and the same warning about performance applies.