Msg 3910 Transaction context in use by another session.

We are testing SQL 2005 SP1 in a multi-server environment. Stored Procedure 'A' is executed on server.database 'A'. It calls SP 'B' on server.database 'B'. SP 'B' in turn calls SP 'C' which is back on server.database 'A'. So in essence A calls an SP on B, which calls an SP back on A. Linked servers are present on both servers to enable these cross-server calls. This scenario works in our SQL 2000 environment.

In the SQL 2005 environment, we get the Msg 3910 message stated in the subject. Is this truly a 'loopback server' situation and if so, why does it work in SQL 2000? Did this functionality change in SQL 2005? In SQL 2005 SP1? This may seem to be a klugy way to do things but unfortunately, it is a core part of our application security system and won't be easily undone. Any ideas would be appreciated. Thanks.

The problem was not introduced in SQL 2005 SP1. The error still occurs when run using two servers with the base SQL 2005 install.|||

We have an application that uses link servers and is running into the same error.

Looks like an undocumented feature in SS2005 or a BUG.

Anyone have any ideas on how to get around it?

Eric

|||

We worked the issue through with Microsoft support and I've included their comments and resolution below. The problem with their answer is that if a consistent data view is their goal, then this should also fail when all the databases are on a single server. Perhaps they'll make this consistent in a future service pack. Anyway, such as it is, here's the answer.

Beginning of Microsoft response:

Yes, this is a known problem and we change the behavior for good reason in Yukon. We’d like you to work around this issue.

The reason we change to current SQL Server 2005 behavior is that it can guarantee consistent data view, while SQL Server 2000 behavior may expose inconsistent data if the loopback tries to access the table that is been changed.

other ways to avoid this error:

1. Change the application logic to not use loopback.

2. Separate the INSERT EXEC into two steps -- Run remote EXEC first(which will succeed for loopback) and put it into a remote table and then run a SELECT from the remote table

But in general, SQL Server doesn’t support loopback server, so you are still at risk that you might get impacted in future release. So the best way is to avoid using the loopback.

End of Microsoft response.

Hope this helps.

|||I was facing a similar problem wherein I was using loopback linked servers for synchronizing multiple databases on same server as well as remote servers. The code was generically handling this. Will have to look for alternate way as support for loopback server is not guaranteed.

Thanks

Chandrakant Karale.

Msg 3910 Transaction context in use by another session.

We are testing SQL 2005 SP1 in a multi-server environment. Stored Procedure 'A' is executed on server.database 'A'. It calls SP 'B' on server.database 'B'. SP 'B' in turn calls SP 'C' which is back on server.database 'A'. So in essence A calls an SP on B, which calls an SP back on A. Linked servers are present on both servers to enable these cross-server calls. This scenario works in our SQL 2000 environment.

In the SQL 2005 environment, we get the Msg 3910 message stated in the subject. Is this truly a 'loopback server' situation and if so, why does it work in SQL 2000? Did this functionality change in SQL 2005? In SQL 2005 SP1? This may seem to be a klugy way to do things but unfortunately, it is a core part of our application security system and won't be easily undone. Any ideas would be appreciated. Thanks.

The problem was not introduced in SQL 2005 SP1. The error still occurs when run using two servers with the base SQL 2005 install.|||

We have an application that uses link servers and is running into the same error.

Looks like an undocumented feature in SS2005 or a BUG.

Anyone have any ideas on how to get around it?

Eric

|||

We worked the issue through with Microsoft support and I've included their comments and resolution below. The problem with their answer is that if a consistent data view is their goal, then this should also fail when all the databases are on a single server. Perhaps they'll make this consistent in a future service pack. Anyway, such as it is, here's the answer.

Beginning of Microsoft response:

Yes, this is a known problem and we change the behavior for good reason in Yukon. We’d like you to work around this issue.

The reason we change to current SQL Server 2005 behavior is that it can guarantee consistent data view, while SQL Server 2000 behavior may expose inconsistent data if the loopback tries to access the table that is been changed.

other ways to avoid this error:

1. Change the application logic to not use loopback.

2. Separate the INSERT EXEC into two steps -- Run remote EXEC first(which will succeed for loopback) and put it into a remote table and then run a SELECT from the remote table

But in general, SQL Server doesn’t support loopback server, so you are still at risk that you might get impacted in future release. So the best way is to avoid using the loopback.

End of Microsoft response.

Hope this helps.

|||I was facing a similar problem wherein I was using loopback linked servers for synchronizing multiple databases on same server as well as remote servers. The code was generically handling this. Will have to look for alternate way as support for loopback server is not guaranteed.

Thanks

Chandrakant Karale.

Msg 3910 Transaction context in use by another session.

We are testing SQL 2005 SP1 in a multi-server environment. Stored Procedure 'A' is executed on server.database 'A'. It calls SP 'B' on server.database 'B'. SP 'B' in turn calls SP 'C' which is back on server.database 'A'. So in essence A calls an SP on B, which calls an SP back on A. Linked servers are present on both servers to enable these cross-server calls. This scenario works in our SQL 2000 environment.

In the SQL 2005 environment, we get the Msg 3910 message stated in the subject. Is this truly a 'loopback server' situation and if so, why does it work in SQL 2000? Did this functionality change in SQL 2005? In SQL 2005 SP1? This may seem to be a klugy way to do things but unfortunately, it is a core part of our application security system and won't be easily undone. Any ideas would be appreciated. Thanks.

The problem was not introduced in SQL 2005 SP1. The error still occurs when run using two servers with the base SQL 2005 install.|||

We have an application that uses link servers and is running into the same error.

Looks like an undocumented feature in SS2005 or a BUG.

Anyone have any ideas on how to get around it?

Eric

|||

We worked the issue through with Microsoft support and I've included their comments and resolution below. The problem with their answer is that if a consistent data view is their goal, then this should also fail when all the databases are on a single server. Perhaps they'll make this consistent in a future service pack. Anyway, such as it is, here's the answer.

Beginning of Microsoft response:

Yes, this is a known problem and we change the behavior for good reason in Yukon. We’d like you to work around this issue.

The reason we change to current SQL Server 2005 behavior is that it can guarantee consistent data view, while SQL Server 2000 behavior may expose inconsistent data if the loopback tries to access the table that is been changed.

other ways to avoid this error:

1. Change the application logic to not use loopback.

2. Separate the INSERT EXEC into two steps -- Run remote EXEC first(which will succeed for loopback) and put it into a remote table and then run a SELECT from the remote table

But in general, SQL Server doesn’t support loopback server, so you are still at risk that you might get impacted in future release. So the best way is to avoid using the loopback.

End of Microsoft response.

Hope this helps.

|||I was facing a similar problem wherein I was using loopback linked servers for synchronizing multiple databases on same server as well as remote servers. The code was generically handling this. Will have to look for alternate way as support for loopback server is not guaranteed.

Thanks

Chandrakant Karale.

MSDTC Unavailable Windows 2003

Hi
I have a VB6 windows app which calls a VB6 COM+ application (both running on
machine CHOPGBCOM001) which in turn calls a stored procedure on a remote
machine CANSUR001 but it keeps failing with error "MSDTC on server
'CANSUR001' is unavailable". The COM+ application is configured with
Transaction Support=Required and isolation level set to the default of
serialised (the COM+
app changes it to Read Committed)
* CHOPCOM001 and CANSUR001 both have Windows 2003 SP1 and CANSUR001
is running SQL Server 2000.
* MSDTC is started on both machines
* I have Installed/enabled windows components "Enable network COM+ access"
and "Enable network DTC access" on both machines
* I have configured MSDTC in Component services to use "Network DTC access",
allow outbound and inbound on "Transaction Manager Communication" and set
to "No authentication required"
* Both machines are in the same domain
* No local firewalls are installed
* I've rebooted both machines but same result
* CANSUR001 is NOT part of a cluster
* I've tried running
BEGIN distributed transaction
select * from cansur001.leisure.dbo.member
from SQL Analyser in CHOPCOM001 but get the same result
Any help would be gratefully received
God Bless
RonanMSDTC is turned off by default in Windows 2003. Have you enabled it in
Windows?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ronan" <Ronan@.discussions.microsoft.com> wrote in message
news:F77ECB86-A5E1-42CC-9D02-51CEBFDED779@.microsoft.com...
> Hi
> I have a VB6 windows app which calls a VB6 COM+ application (both running
> on
> machine CHOPGBCOM001) which in turn calls a stored procedure on a remote
> machine CANSUR001 but it keeps failing with error "MSDTC on server
> 'CANSUR001' is unavailable". The COM+ application is configured with
> Transaction Support=Required and isolation level set to the default of
> serialised (the COM+
> app changes it to Read Committed)
>
> * CHOPCOM001 and CANSUR001 both have Windows 2003 SP1 and CANSUR001
> is running SQL Server 2000.
> * MSDTC is started on both machines
> * I have Installed/enabled windows components "Enable network COM+ access"
> and "Enable network DTC access" on both machines
> * I have configured MSDTC in Component services to use "Network DTC
> access",
> allow outbound and inbound on "Transaction Manager Communication" and
> set
> to "No authentication required"
> * Both machines are in the same domain
> * No local firewalls are installed
> * I've rebooted both machines but same result
> * CANSUR001 is NOT part of a cluster
> * I've tried running
> BEGIN distributed transaction
> select * from cansur001.leisure.dbo.member
> from SQL Analyser in CHOPCOM001 but get the same result
>
> Any help would be gratefully received
> God Bless
> Ronan|||Distributed Transaction Coordinator service is enabled and started on both
machines
--
Ronan
"Ronan" wrote:

> Hi
> I have a VB6 windows app which calls a VB6 COM+ application (both running
on
> machine CHOPGBCOM001) which in turn calls a stored procedure on a remote
> machine CANSUR001 but it keeps failing with error "MSDTC on server
> 'CANSUR001' is unavailable". The COM+ application is configured with
> Transaction Support=Required and isolation level set to the default of
> serialised (the COM+
> app changes it to Read Committed)
>
> * CHOPCOM001 and CANSUR001 both have Windows 2003 SP1 and CANSUR001
> is running SQL Server 2000.
> * MSDTC is started on both machines
> * I have Installed/enabled windows components "Enable network COM+ access"
> and "Enable network DTC access" on both machines
> * I have configured MSDTC in Component services to use "Network DTC access
",
> allow outbound and inbound on "Transaction Manager Communication" and s
et
> to "No authentication required"
> * Both machines are in the same domain
> * No local firewalls are installed
> * I've rebooted both machines but same result
> * CANSUR001 is NOT part of a cluster
> * I've tried running
> BEGIN distributed transaction
> select * from cansur001.leisure.dbo.member
> from SQL Analyser in CHOPCOM001 but get the same result
>
> Any help would be gratefully received
> God Bless
> Ronan|||One thing I forgot to mention is that the remote server CANSUR001 is a
Domain Contoller, anyone come across problems running distributed
transactions against
servers which are also doiman controllers?
Ronan
"Ronan" wrote:
> Distributed Transaction Coordinator service is enabled and started on both
> machines
> --
> Ronan
>
> "Ronan" wrote:
>

MSDTC Unavailable

Hi
I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
calls a COM+ component which in turn calls another COM+ component ( data
access module) which in turn calls a stored procedure on another machine
CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC o
n
server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on bot
h
PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
in both directions. DTCtester fails with the same message as above. On the
Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
to "allow inbound" and "allow outound" and "no authentication required". I'v
e
run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
account) to CANPAD001 but again it gives the same error.
Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
Any help would be much appreciated
RonanI forgot to mention there is no firewall on either machine and all the ports
are open.
I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
Ronan
"Ronan" wrote:

> Hi
> I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
> calls a COM+ component which in turn calls another COM+ component ( data
> access module) which in turn calls a stored procedure on another machine
> CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC
on
> server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on b
oth
> PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works o
k
> in both directions. DTCtester fails with the same message as above. On th
e
> Win2K3 machine (CANPAD001) I've set the MSDTC setting in component service
s
> to "allow inbound" and "allow outound" and "no authentication required". I
've
> run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the
sa
> account) to CANPAD001 but again it gives the same error.
> Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
> Any help would be much appreciated
> --
> Ronan|||Also in the SQL Server logs for CANPAD001 I have the following message.
CANPAD001 is not part of a a cluster.
"Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"
Ronan
"Ronan" wrote:
[vbcol=seagreen]
> I forgot to mention there is no firewall on either machine and all the por
ts
> are open.
> I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
>
> --
> Ronan
>
> "Ronan" wrote:
>

MSDTC Unavailable

Hi
I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
calls a COM+ component which in turn calls another COM+ component ( data
access module) which in turn calls a stored procedure on another machine
CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
in both directions. DTCtester fails with the same message as above. On the
Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
to "allow inbound" and "allow outound" and "no authentication required". I've
run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
account) to CANPAD001 but again it gives the same error.
Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
Any help would be much appreciated
Ronan
I forgot to mention there is no firewall on either machine and all the ports
are open.
I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
Ronan
"Ronan" wrote:

> Hi
> I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
> calls a COM+ component which in turn calls another COM+ component ( data
> access module) which in turn calls a stored procedure on another machine
> CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
> server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
> PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
> in both directions. DTCtester fails with the same message as above. On the
> Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
> to "allow inbound" and "allow outound" and "no authentication required". I've
> run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
> account) to CANPAD001 but again it gives the same error.
> Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
> Any help would be much appreciated
> --
> Ronan
|||Also in the SQL Server logs for CANPAD001 I have the following message.
CANPAD001 is not part of a a cluster.
"Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"
Ronan
"Ronan" wrote:
[vbcol=seagreen]
> I forgot to mention there is no firewall on either machine and all the ports
> are open.
> I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
>
> --
> Ronan
>
> "Ronan" wrote:

MSDTC Unavailable

Hi
I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
calls a COM+ component which in turn calls another COM+ component ( data
access module) which in turn calls a stored procedure on another machine
CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
in both directions. DTCtester fails with the same message as above. On the
Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
to "allow inbound" and "allow outound" and "no authentication required". I've
run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
account) to CANPAD001 but again it gives the same error.
Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
Any help would be much appreciated
--
RonanI forgot to mention there is no firewall on either machine and all the ports
are open.
I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
Ronan
"Ronan" wrote:
> Hi
> I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
> calls a COM+ component which in turn calls another COM+ component ( data
> access module) which in turn calls a stored procedure on another machine
> CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
> server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
> PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
> in both directions. DTCtester fails with the same message as above. On the
> Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
> to "allow inbound" and "allow outound" and "no authentication required". I've
> run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
> account) to CANPAD001 but again it gives the same error.
> Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
> Any help would be much appreciated
> --
> Ronan|||Also in the SQL Server logs for CANPAD001 I have the following message.
CANPAD001 is not part of a a cluster.
"Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"
Ronan
"Ronan" wrote:
> I forgot to mention there is no firewall on either machine and all the ports
> are open.
> I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
>
> --
> Ronan
>
> "Ronan" wrote:
> >
> > Hi
> >
> > I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
> > calls a COM+ component which in turn calls another COM+ component ( data
> > access module) which in turn calls a stored procedure on another machine
> > CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
> > server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
> > PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
> > in both directions. DTCtester fails with the same message as above. On the
> > Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
> > to "allow inbound" and "allow outound" and "no authentication required". I've
> > run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
> > account) to CANPAD001 but again it gives the same error.
> > Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
> >
> > Any help would be much appreciated
> >
> > --
> > Ronan