functions – Page 107

How to Fix “Server is not configured for DATA ACCESS” in SQL Server

February 14, 2020October 15, 2019 by Ian

The “Server is not configured for DATA ACCESS” error in SQL Server is a common error when trying to run a distributed query against a server that has its data access setting disabled.

The error will have the name of the server that you’re trying to access. For example, if your server name is SQL01, the error will read something like this:

Msg 7411, Level 16, State 1, Line 1
Server 'SQL01' is not configured for DATA ACCESS.

“Data access” is a setting that enables and disables a linked server for distributed query access.

A common cause of this error is when you try to run OPENQUERY() against the local server. If you really want to run OPENQUERY() against the server, you’ll need to ensure data access is enabled for that server – even if it’s your local server.

2 Ways to Check if Data Access is Enabled in SQL Server (T-SQL Examples)

February 14, 2020October 15, 2019 by Ian

In SQL Server, there’s a “data access” setting that enables and disables a linked server for distributed query access. If you ever get the “Server is not configured for DATA ACCESS” error, it’s probably because you’re trying to run a distributed query against a linked server that isn’t configured for data access. This can also happen when you try to run OPENQUERY() against your local server.

You can use sp_serveroption to enable or disable data access on a given server. However, you might want to check the existing settings before you start changing them. The following examples show how to do that.

How to Use the ‘sp_server_info’ Stored Procedure in SQL Server

February 14, 2020October 13, 2019 by Ian

In SQL Server the sp_server_info system stored procedure returns a list of attribute names and matching values for SQL Server, the database gateway, or the underlying data source. It returns a subset of the information provided by SQLGetInfo in ODBC.

Basically, it allows you to see information about SQL Server.

How Logins Work on Linked Servers (T-SQL Examples)

November 19, 2019October 5, 2019 by Ian

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.

Create a Linked Server in SQL Server (T-SQL Example)

November 19, 2019October 2, 2019 by Ian

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

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

February 14, 2020September 30, 2019 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.

How APPROX_COUNT_DISTINCT() Works in SQL Server

November 19, 2019September 27, 2019 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.

COUNT() vs COUNT_BIG() in SQL Server: What’s the Difference?

November 19, 2019September 25, 2019 by Ian

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

November 19, 2019September 24, 2019 by Ian

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

How COUNT() Works in SQL Server

November 19, 2019September 23, 2019 by Ian

In SQL Server, the COUNT() 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.