Piotr Rodak

DATA ACCESS setting on local server

When you look at the sys.servers catalog view, you will see list of linked servers defined on the server plus one additional row for the server itself. You can distinguish the local server from linked servers by column server_id, which in case of local server has value of 0. 

select server_id, name, is_data_access_enabled from sys.servers

We have an application, that stores query to execute in a configuration table, along with name of the linked server. The query is executed using OPENQUERY statement. This setup allows for changing environment practically at runtime, all you have to do is to change linked server name in the configuration. Last week we decided to make a 'proxy' database that would contain a subset of data of original linked server database on local server. This way we can have consistent development and testing environment.

So, the query would look something similar to this:

select * from openquery(Amilo, 'select * from testdb.dbo.tLinked')

Unfortunately, it didn't work as we expected:

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

Now, it turns out that you have two ways of solving this issue.

The first option is that you can create loopback linked server to your local server.

To create a loopback server you can use sp_addlinkedserver procedure, as it is described in books on line:

Untitled

sp_addlinkedserver @server = N'AMILOLinked',
    @srvproduct = N' ',
    @provider = N'SQLNCLI', 
    @datasrc = N'AMILO', 
    @catalog = N'master'

After running this command you will see that there is new entry in sys.servers view and its is_linked column value will be set to 1.

Now you can use this server for example like this.

Untitled

select * from openquery(AmiloLinked, 'select * from testdb.dbo.tLinked')

The limitation of a loopback linked server is that it cannot take part in an explicit or implicit transaction because this would require loopback distributed transaction, something that MSDTC apparently does not support.In such scenario, you will get error 3910:

Msg 3910, Level 16, State 2, Line 1

Transaction context in use by another session.

Interestingly, autocommit transactions will work, but they are constrained to single statement, so it is not always what you maight be looking for.
If MSDTC is not enabled, both explicit and implicit transactions will throw a different error:

Msg 8501, Level 16, State 3, Line 2

MSDTC on server 'AMILO' is unavailable.

The second option you have is to enable DATA ACCESS on your server, the one listed with server_id = 0.

To enable this option hou have to use sp_serveroption procedure:

EXEC sp_serveroption 'AMILO', 'DATA ACCESS', TRUE

If you query sys.servers now, you will see that is_data_access_enabled column for server_id is now set to 1. Also implicit and explicit transactions work, unless you request for distributed transaction and MSDTC is not running:

begin distributed tran

insert Amilo.testdb.dbo.TLinked(a, b) values(1, 1)

commit

Msg 8501, Level 16, State 3, Line 2

MSDTC on server 'AMILO' is unavailable.

The DATA ACCESS server option is not very well documented in my opinion - the Books On Line say it is a property of linked servers. It doesn't mention at all that you actually can have it enabled on your local server to enable OPENQUERY calls. I noticed that when you disable DATA ACCESS on a linked server, you can't query any table located on it (I tested it on my loopback server) neither using OPENQUERY nor four-part naming convention. You can still call procedures (with four-part naming) that return rowsets. Well, the interesting question is why it is disabled by default on local server - I suppose to discourage users from using OPENQUERY against it.