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.

 

T-SQL Example for Creating the Linked Server

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 Tips like this – Enroll to the Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Upgrade your Tech Skills – Learn all about Azure SQL Database

Enroll to our online course on Udemy titled “Introduction to Azure SQL Database for Beginners” and get lifetime access to high-quality lessons and hands-on guides about all aspects of Azure SQL Database.

Introduction to Azure SQL Database (Online Course - Lifetime Access)
(Lifetime Access/ Live Demos / Downloadable Resources and more!)
Learn More

 

 

Featured Online Courses:

 

Read Also:

 

Check our 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

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHubTV)!

Like our Facebook Page!

Check our SQL Server Administration articles.

Check out our latest software releases!

Check our eBooks!

 

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

Loading...

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

© SQLNetHub