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).
Tag: mssql
Remove a Linked Server in SQL Server using T-SQL
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.
2 Ways to Return a List of Linked Servers in SQL Server using T-SQL
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.
How to Fix Error Msg 7325 in SQL Server: “Objects exposing columns with CLR types are not allowed in distributed queries”
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.
Create a Linked Server Between Two Docker Containers running SQL Server (T-SQL Example)
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).
How to Run SQL Server 2017 & 2019 Simultaneously on a Mac
As I write this, there are two releases of SQL Server that can be run on a Mac: SQL Server 2017 and SQL Server 2019 Preview. This article explains how to run both of these on a Mac so that they run simultaneously. No need to uninstall one before you install the other.
The key is to use a different TCP port number for each instance (the TCP port that the containers are mapped to on the host machine). If you don’t do this, you’ll get an error. SQL Server uses port 1433 by default, which is fine to use for one of your instances, but not both. Therefore you’ll need to change this for at least one of your installations.
How APPROX_COUNT_DISTINCT() Works in SQL Server
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.
Install SQL Server 2019 on a Mac
I previously explained how to install SQL Server on a Mac via a Docker container. When I wrote that, SQL Server 2017 was the latest version of SQL Server, and it had just been made available for Linux and Docker (which means that you can also install it on MacOS systems).
In late 2018, Microsoft announced SQL Server 2019 Preview, and subsequently announced general release in late 2019. The installation process for SQL Server 2019 is exactly the same as for SQL Server 2017. The only difference is that you need to use the container image for SQL Server 2019 instead of the 2017 image. Here I show you how to do that.
Also, if you already have SQL Server 2017 installed, and you want to install SQL Server 2019 without removing the 2017 version, you’ll need to allocate a different port number on your host. I show you how to do that too.
But first… if you’re using a Mac with the M1 chip, see How to Install SQL Server on an M1 Mac (ARM64).
COUNT() vs COUNT_BIG() in SQL Server: What’s the Difference?
In SQL Server, the COUNT_BIG() function and the COUNT() do essentially the same thing: return the number of items found in a group. Basically, you can use these functions to find out how many rows are in a table or result set.
In many cases, you’ll be able to choose whichever one you prefer. However, there’s a difference between these two functions that might dictate that you to use one over the other.
The difference is that COUNT() returns its result as an int, whereas COUNT_BIG() returns its result as a bigint.
In other words, you’ll need to use COUNT_BIG() if you expect its results to be larger than 2,147,483,647 (i.e. if the query returns more than 2,147,483,647 rows).
How COUNT_BIG() Works in SQL Server
In SQL Server, the COUNT_BIG() function returns the number of items found in a group. You can use it to find out how many rows are in a table or result set.
This function works similar to the COUNT() function. The difference is that COUNT() returns its result as an int, whereas COUNT_BIG() returns its result as a bigint.
Therefore COUNT_BIG() could come in handy if you expect your result set to have a very large number of rows (i.e. larger than 2,147,483,647).