This article provides an example of using Transact-SQL to remove a linked server in SQL Server.
To remove a linked server, use the sp_dropserver system stored procedure. This removes a server from the list of known remote and linked servers on the local instance of SQL Server. This stored procedure accepts two arguments: the server name, and an optional argument for removing any logins associated with the server.
If you find yourself needing to use Transact-SQL to get a list of all linked servers in SQL Server, below are two ways you can go about doing this.
In the first example I use the sp_linkedservers system stored procedure to return the linked servers. In the second example I use the sys.servers system catalog view.
If you’ve encountered error message 7325 in SQL Server “Objects exposing columns with CLR types are not allowed in distributed queries” it’s probably because you’re trying to run a distributed query against a table that contains one or more columns with CLR types.
For example, you could be querying a table that uses a geography or geometry data type in one or more of its columns. These data types are implemented as .NET common language runtime (CLR) data types in SQL Server. And as the error message says, “Objects exposing columns with CLR types are not allowed in distributed queries”.
Fortunately, there’s an easy fix for this issue. And the answer is provided with the full error message.
This article walks through the process of creating a linked server from a SQL Server instance on one Docker container, to a SQL Server instance on another Docker container. Both Docker containers are on the same machine.
The process is exactly the same that you would use to create any linked server (for example, even if the SQL Server instances were on separate machines and not running in a Docker container).
All examples here use Transact-SQL. This enables you to create the linked server without relying on a graphical user interface (such as SSMS).