how to – Page 179

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 Enable/Disable Data Access in SQL Server (T-SQL Example)

February 14, 2020October 14, 2019 by Ian

SQL Server has a “data access” configuration option that enables and disables a linked server for distributed query access.

If you ever get a “Server is not configured for DATA ACCESS” error, you’ll probably need to enable data access for the linked server you’re trying to run the distributed query against. Conversely, there may also be times where you need to disable data access.

To enable or disable data access, use the sp_serveroption system stored procedure. Execute it against the server that you intend to run distributed queries from. The following example demonstrates how to do this.

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 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 to Run SQL Server 2017 & 2019 Simultaneously on a Mac

February 14, 2020September 29, 2019 by Ian

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.

Install SQL Server 2019 on a Mac

January 16, 2026September 26, 2019 by Ian

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

How to Include Results that Tie for Last Place when Using the TOP Clause in SQL Server

February 14, 2020September 16, 2019 by Ian

When using the TOP clause in a query in SQL Server, you may encounter occasions where two or more results tie for last place. You probably wouldn’t even know when this happens, because the default behavior of TOP is to return no more than the number of rows you specify.

The TOP clause accepts a WITH TIES argument that allows you to specify whether or not to include all results that tie for last place. Rows can tie for last place due to their ORDER BY column containing the same value. Using this argument may therefore result in more rows being returned than you actually specified.

Add a CHECK Constraint to an Existing Table in SQL Server (T-SQL)

August 9, 2024September 10, 2019 by Ian

This article demonstrates how to add a CHECK constraint to an existing table.

You can add a constraint to an existing table by using the ALTER TABLE statement along with the ADD CONSTRAINT argument. Examples below.

Rename a CHECK Constraint in SQL Server using T-SQL

August 9, 2024August 27, 2019 by Ian

You can use the sp_rename system stored procedure to rename a CHECK constraint in SQL Server.

The purpose of this stored procedure is to allow you to rename user-created objects in the current database. So you can also use it to rename other objects such as tables, columns, alias data types, etc.