In SQL Server you can use the sp_column_privileges_ex system stored procedure to return column privileges for the columns from a specified linked server.
You can specify an individual column, or you can specify all columns from a given database, table, etc.
In SQL Server you can use the sp_columns_ex system stored procedure to return column information about the columns from a specified linked server.
You can specify an individual column, or you can specify all columns from a given database, table, etc.
In SQL Server you can use the sp_tables_ex system stored procedure to return table information about the tables from a specified linked server.
The simplest way to execute this stored procedure is to pass the name of the linked server. Doing that will return all tables from the default database on the specified linked server, including system tables and views. This could be a big list.
You also have the option of specifying a different database and/or a specific table schema. You can also filter the results based on the table type (e.g. table, view, system table, etc).
In this article, I configure a linked server in SQL Server so that only a single local login can access it. To do this, I delete any mappings that are automatically created when I create the linked server, before adding a login mapping for the single local login.
When configuring a linked server in SQL Server, setting up the logins can sometimes be confusing. In this article, I aim to provide a high-level overview of how SQL Server maps local logins to remote logins on the linked server.
In this article, I create a login for a linked server in SQL Server. In this case I use Transact-SQL (as opposed to using a GUI such as SSMS).
To create a login for linked server, use the sp_addlinkedsrvlogin system stored procedure.
This article provides an example of creating a linked server in SQL Server using Transact-SQL. Using T-SQL enables you to create the linked server without relying on a graphical user interface (such as SSMS).
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.