8/30/25, 5:37 PM HOWTO: How do I create a linked server in SQL Server?
ID: QA68702 | Access Levels: TechConnect
HOWTO: How do I create a linked server in
SQL Server?
Document ID QA68702
Published Date 02/24/2024
Summary
HOWTO: How do I create a linked server in SQL Server?
Question
The information in this article applies to:
Platform: MS SQL Server
Datasweep Product: N/A
How do I create a linked server in SQL Server?
Answer
When troubleshooting historical transfer problems where the active and historical
databases are on separate database servers, it is often helpful to set up a linked server in
SQL Server so that you can perform linked SQL queries against the 2 database servers (eg.
to identify what has and has not been transferred over).
A linked server is a virtual server that has been defined to Microsoft® SQL Server™ with all
the information needed to access an OLE DB data source. After a linked server is defined,
a four-part name in the form linked_server_name.catalog.schema.object_name can be
used in Transact-SQL statements to reference data objects in that linked server. The
following table describes the parts of a four-part name:
Part name Description
https://support.rockwellautomation.com/app/answers/answer_view/a_id/1143291 1/3
8/30/25, 5:37 PM HOWTO: How do I create a linked server in SQL Server?
linked_server Linked server referencing the OLE DB data source
Catalog in the OLE DB data source that contains the
catalog
object
schema Schema in the catalog that contains the object
object_name Data object in the schema
SQL Server uses the linked server name to identify the OLE DB provider and the data
source. The catalog, schema, and object_name parameters are passed to the OLE DB
provider to identify a specific data object. When the linked server refers to a server
running SQL Server, catalog refers to a database and schema refers to an owner ID.
This example defines a linked server against a server running SQL Server and references
one of the remote tables using a four-part name in a SELECT statement.
use master exec sp_addlinkedserver
@server = N’LinkServer’,
@srvproduct = N’ ’,
@provider = N’SQLOLEDB’,
@datasrc = N’ServerNetName’,
@catalog = N’Northwind’
where
@server is the local name of the linked server to create;
@srvproduct is the product name of the OLE DB data source to add as a linked server.
product_name is nvarchar(128), with a default of NULL.
@provider is the unique programmatic identifier (PROGID) of the OLE DB provider
corresponding to this data source. In this case, it is SQLOLEDB.
@datasrc is the name of the data source as interpreted by the OLE DB provider.
data_source is nvarchar(4000), with a default of NULL.
@catalog is the catalog to be used when making a connection to the OLE DB provider
After executing the stored procedure sp_addlinkedserver above, you should now be able
to access the "Shipper" table in the "Northwind" database on the remote server
"LinkServer".
SELECT * FROM LinkServer.Northwind.dbo.Shippers
Recently Viewed
https://support.rockwellautomation.com/app/answers/answer_view/a_id/1143291 2/3
8/30/25, 5:37 PM HOWTO: How do I create a linked server in SQL Server?
FactoryTalk Historian
SE: How to configure FactoryTalk Historian FactoryTalk Historian
a linked server with PI Site Edition and SE and the OSIsoft PI
OLEDB or PI OLEDB Virtualization OLEDB Providers
Enterprise
DISCLAIMER
This knowledge base web site is intended to provide general technical information on a particular subject
or subjects and is not an exhaustive treatment of such subjects. Accordingly, the information in this web
site is not intended to constitute application, design, software or other professional engineering advice or
services. Before making any decision or taking any action, which might affect your equipment, you should
consult a qualified professional advisor.
ROCKWELL AUTOMATION DOES NOT WARRANT THE COMPLETENESS, TIMELINESS OR
ACCURACY OF ANY OF THE DATA CONTAINED IN THIS WEB SITE AND MAY MAKE CHANGES
THERETO AT ANY TIME IN ITS SOLE DISCRETION WITHOUT NOTICE. FURTHER, ALL
INFORMATION CONVEYED HEREBY IS PROVIDED TO USERS "AS IS." IN NO EVENT SHALL
ROCKWELL BE LIABLE FOR ANY DAMAGES OF ANY KIND INCLUDING DIRECT, INDIRECT,
INCIDENTAL, CONSEQUENTIAL, LOSS PROFIT OR DAMAGE, EVEN IF ROCKWELL AUTOMATION
HAVE BEEN ADVISED ON THE POSSIBILITY OF SUCH DAMAGES.
ROCKWELL AUTOMATION DISCLAIMS ALL WARRANTIES WHETHER EXPRESSED OR IMPLIED IN
RESPECT OF THE INFORMATION (INCLUDING SOFTWARE) PROVIDED HEREBY, INCLUDING THE
IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE, MERCHANTABILITY, AND
NON-INFRINGEMENT. Note that certain jurisdictions do not countenance the exclusion of implied
warranties; thus, this disclaimer may not apply to you.
https://support.rockwellautomation.com/app/answers/answer_view/a_id/1143291 3/3
