T-SQL Tip: How to Create a Simple Linked Server Between SQL Server Instances

If you are looking for a T-SQL tip, on how to create a simple linked server between SQL Server instances, then the below tip might come in handy.

The below T-SQL code does just that:

--
--Note: The Entire Operation Takes Place on the Local Instance
--

USE [master]
GO

--Create the linked server object
EXEC master.dbo.sp_addlinkedserver 
@server = N'[LinkedServerName]', 
@srvproduct=N'', 
@provider=N'SQLNCLI', 
@datasrc=N'[DestSQLInstanceName]'

 --Set up the user mapping between local and remote instances
EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'[LinkedServerName]',
@useself=N'False',@locallogin=N'[LocalLogin_SQL_or_Windows]',
@rmtuser=N'[Remote_SQL_Login_Name]',
@rmtpassword='[Remote_SQL_Login_Password]'
GO


--Example of querying a remote table
select * from [LinkedServerName].[Database_Name].[Schema_Name].[Table_Name];

 

Learn More:

 

Read Also:

 

Featured Database Security and Administration Tool: DBA Security Advisor

DBA Security Advisor: Secure your SQL Server instances by scanning multiple instances against a rich set of security checks, and by getting recommendations and remediation steps.

DBA Security Advisor - SQL Server Security and Administration Tool

Learn more

 

Did you find this article useful and interesting? Feel free to leave your comment!

If you enjoy my SQL Server administration tips and articles, I have something special just for you. It is one of my eBooks and it is called “Administering SQL Server“. Check it out!

Subscribe to our newsletter and stay up to date with our latest articles on SQL Server and related technologies!

Check out our latest software releases! All our software tools have 30-day Trial Versions which are free to download.

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub