Transaction with loopback linked server - locking issues

Question

I have two databases A and B. They are both stored on one database instance. I created a loopback linked server on the instance.

Database A contains one table dbo.Users and one stored procedure updating dbo.Users table. In database B I have a query which does two things:

1. Execute the stored procedure from database A which updates the dbo.Users table.
2. Select data from dbo.Users through the linked server.

BEGIN TRANSACTION
EXEC [LinkedServer].A.dbo.UpdateUser
select * from [LinkedServer].A.dbo.Users 
ROLLBACK TRANSACTION

When I try to execute this stored procedure I get the following exception only when I set a timeout on the linked server; in other cases the query doesn’t finish:

Msg 3971, Level 16, State 1, Line 1
The server failed to resume the transaction. Desc:3900000002.

The reason of this problem is that execution of the [LinkedServer].A.dbo.UpdateUser stored procedure creates a transaction which does not allow to make a select statement.

So I decided to add WITH (NOLOCK) as below:

BEGIN TRANSACTION
EXEC [LinkedServer].A.dbo.UpdateUser
select * from [LinkedServer].A.dbo.Users WITH (NOLOCK)
ROLLBACK TRANSACTION

Then I get this exception:

OLE DB provider "SQLNCLI11" for linked server " LinkedServer " returned message "Unspecified error".
OLE DB provider "SQLNCLI11" for linked server " LinkedServer " returned message "Query timeout expired".
Msg 7311, Level 16, State 2, Line 4 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI11" for linked server "LinkedServer". The provider supports the interface, but returns a failure code when it is used.

I found information about this exception on microsoft support page. There is information that this error occurs when you try to run distributed queries from a 64-bit SQL Server client to a linked 32-bit SQL Server. In my case it doesn’t make sense because I have a loopback linked server.

The above errors don't occur when the databases are deployed on separate SQL server instances. Any ideas how to omit locks or change the T-SQL to not get exceptions when using a loopback linked server?

Answer 1

OLE DB provider "SQLNCLI11" for linked server " LinkedServer " returned message "Unspecified error".

OLE DB provider "SQLNCLI11" for linked server " LinkedServer " returned message "Query timeout expired".

Msg 7311, Level 16, State 2, Line 4 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI11" for linked server "LinkedServer". The provider supports the interface, but returns a failure code when it is used.

I have also faced the above mentioned error.

If you are doing loop back linked server and receive the same error then check that any SP using loop back server should be written (Create Procedure)at the end of the script.

If using code from TFS, write create procedure statement of that SP, using loop back linked server in the post deployment.

Simply follow any one of the below mentioned steps:-

1. Copy paste the SP in the end of script. OR
2. Don't use loop back server. OR
3. If you are using publish script from TFS write SP in the Post deployment script (after Data insert into all table).

These solution worked for me.