SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

You cannot create a local SQL Server as a linked server

It is possible to create SQL Server linked server on a SQL Server instance pointing to itself. This is called loopback linked server in SQL Server. By creating local SQL Server instance as a linked server to itself enables Transact-SQL developers to use SQL Server OpenRowset or OpenQuery commands for special purposes. But SQL Server database administrators or SQL developers can get the following error message while trying to add local database instance as SQL Server linked server.

You cannot create a local SQL Server as a linked server.

SQL engine throws above error message when SQL Server instance name is written explicitly in the Linked Server name textbox on New Linked Server creation wizard. There is a trick to create a linked server to its self.

You cannot create a local SQL Server as a linked server
You cannot create a local SQL Server as a linked server.

But we can create loopback linked server or linked server to itself on any SQL Server version.

In order to create loopback linked server on SQL Server 2012, I've followed the below steps.

Open the "create new Linked Server wizard" using SQL Server Management Studio.
In the Linked server textbox instead of providing the name of the SQL Server instance, type "." (only dot). This will indicate the linked server is a loopback linked server.

new SQL Server linked server

Then switch to Security tab, choose "Be made using the login's current security context" select option.

linked server security context on SQL Server 2012

Press OK button to finish adding linked server to SQL Server which points to itself.

In Object Explorer window, you will see the new loopback SQL Server linked server with "." as name under Server Objects > Linked Servers node.

SQL Server Management Studio Server Objects Linked Servers

You can read our SQL tutorial on how to select from stored procedure in SQL Server where local database instance is used as an SQL Server linked server. Sometimes to create loopback linked server on SQL Server can be very handy for SQL programmers to solve some tricky situations.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.