Tag: t-sql

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

Posted on 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.

Continue reading →

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

Posted on 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.

Continue reading →

How to Enable/Disable Data Access in SQL Server (T-SQL Example)

Posted on 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.

Continue reading →

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

Posted on 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.

Continue reading →

Return Primary Keys from a Linked Server in SQL Server (T-SQL Examples)

Posted on by Ian

In SQL Server you can use the sp_primarykeys system stored procedure to return the primary key columns from a specified linked server. It returns one row per key column, for the specified remote table.

The simplest way to execute this stored procedure is to pass the name of the linked server. Doing that will return all primary keys from the default database on the specified linked server.

You also have the option of specifying a different database and/or a specific table schema.

Continue reading →

Return Table Privileges from a Linked Server in SQL Server (T-SQL Examples)

Posted on by Ian

In SQL Server you can use the sp_table_privileges_ex system stored procedure to return privilege information about a specified table from a specified linked server.

You can specify an individual table, or you can specify all tables from a given database or table schema. You can also use wildcard characters to specify the table/s. However, you can also specify whether the wildcard characters should be interpreted as wildcard characters.

Continue reading →

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 →