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.
Tag: t-sql
How Logins Work on Linked Servers (T-SQL Examples)
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.
Add a Linked Server Login in SQL Server (T-SQL Example)
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.
Create a Linked Server in SQL Server (T-SQL Example)
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).
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 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.
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).