Tag: t-sql

Return a List of Tables from a Linked Server in SQL Server (T-SQL Examples)

Posted on by Ian

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).

Continue reading →

Remove a Linked Server in SQL Server using T-SQL

Posted on by Ian

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.

Continue reading →

2 Ways to Return a List of Linked Servers in SQL Server using T-SQL

Posted on by Ian

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.

Continue reading →

How to Fix Error Msg 7325 in SQL Server: “Objects exposing columns with CLR types are not allowed in distributed queries”

Posted on by Ian

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.

Continue reading →

Create a Linked Server Between Two Docker Containers running SQL Server (T-SQL Example)

Posted on by Ian

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).

Continue reading →

How APPROX_COUNT_DISTINCT() Works in SQL Server

Posted on by Ian

APPROX_COUNT_DISTINCT() is one of the new functions introduced in SQL Server 2019. This function returns the approximate number of unique non-null values in a group.

Basically, you can use it to get an approximate idea of the number of non-duplicate rows in a large table or result set. It works similar to the COUNT_BIG() and COUNT() functions (when using the DISTINCT clause), but it returns an approximate number rather than a precise number.

APPROX_COUNT_DISTINCT() is aimed mainly at big data scenarios. It’s designed for accessing large data sets with more than a million rows, and aggregation of a column or columns that have many distinct values. It is intended for scenarios where responsiveness is more critical than absolute precision.

Microsoft states that the function implementation guarantees up to a 2% error rate within a 97% probability.

At the time of writing, APPROX_COUNT_DISTINCT() is a public preview feature. It was introduced in SQL Server 2019, which is also currently in preview status.

Note that Microsoft states that preview features are not intended for production use.

Continue reading →