Loopback linked servers cannot be used in a distributed transactions? This worked in Sql20

I recently did an upgrade to SQL 2005 and there's some odd behavior
that I'm seeing.
Given the following SQL:
Use MyDB1
go
create table #test (a int)
insert into #test
exec [MyServer].MyDB2.dbo.SP_RETURNS_INT
where MyServer is a linked server that points back to itself. This
SQL works under SQL2000 but does not work in SQL2005. The error
returned in SQL 2005 is:
Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.
In this whitepaper (http://msdn2.microsoft.com/en-us/library/
ms188716.aspx) i see the following warning:
"Loopback linked servers cannot be used in a distributed transaction.
Trying a distributed query against a loopback linked server from
within a distributed transaction causes an error, such as error 3910:
"[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in
use by another session."
The reason for the loopback linked server here is that in my live
enviornment, the linked server points to a different machine, (MyDB1
and MyDB2 are on different physical servers) but for my development
enviornment MyDB1 and MyDB2 are on the same physical server. I'd like
to be able to keep that configuration without having to change code
when promoting stored procedures from the development branch to QA and
LIVE. Any way to get around this?You should be able to get around it by running a second instance in
your development environment, one instance for each of the production
systems.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 07:27:06 -0700, MS <spilich@.gmail.com> wrote:
>I recently did an upgrade to SQL 2005 and there's some odd behavior
>that I'm seeing.
>Given the following SQL:
>Use MyDB1
>go
>create table #test (a int)
>insert into #test
>exec [MyServer].MyDB2.dbo.SP_RETURNS_INT
>where MyServer is a linked server that points back to itself. This
>SQL works under SQL2000 but does not work in SQL2005. The error
>returned in SQL 2005 is:
>Msg 3910, Level 16, State 2, Line 1
>Transaction context in use by another session.
>In this whitepaper (http://msdn2.microsoft.com/en-us/library/
>ms188716.aspx) i see the following warning:
>"Loopback linked servers cannot be used in a distributed transaction.
>Trying a distributed query against a loopback linked server from
>within a distributed transaction causes an error, such as error 3910:
>"[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in
>use by another session."
>The reason for the loopback linked server here is that in my live
>enviornment, the linked server points to a different machine, (MyDB1
>and MyDB2 are on different physical servers) but for my development
>enviornment MyDB1 and MyDB2 are on the same physical server. I'd like
>to be able to keep that configuration without having to change code
>when promoting stored procedures from the development branch to QA and
>LIVE. Any way to get around this?|||On Aug 15, 10:53 am, Roy Harvey <roy_har...@.snet.net> wrote:
> You should be able to get around it by running a second instance in
> your development environment, one instance for each of the production
> systems.
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 15 Aug 2007 07:27:06 -0700, MS <spil...@.gmail.com> wrote:
> >I recently did an upgrade to SQL 2005 and there's some odd behavior
> >that I'm seeing.
> >Given the following SQL:
> >Use MyDB1
> >go
> >create table #test (a int)
> >insert into #test
> >exec [MyServer].MyDB2.dbo.SP_RETURNS_INT
> >where MyServer is a linked server that points back to itself. This
> >SQL works under SQL2000 but does not work in SQL2005. The error
> >returned in SQL 2005 is:
> >Msg 3910, Level 16, State 2, Line 1
> >Transaction context in use by another session.
> >In this whitepaper (http://msdn2.microsoft.com/en-us/library/
> >ms188716.aspx) i see the following warning:
> >"Loopback linked servers cannot be used in a distributed transaction.
> >Trying a distributed query against a loopback linked server from
> >within a distributed transaction causes an error, such as error 3910:
> >"[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in
> >use by another session."
> >The reason for the loopback linked server here is that in my live
> >enviornment, the linked server points to a different machine, (MyDB1
> >and MyDB2 are on different physical servers) but for my development
> >enviornment MyDB1 and MyDB2 are on the same physical server. I'd like
> >to be able to keep that configuration without having to change code
> >when promoting stored procedures from the development branch to QA and
> >LIVE. Any way to get around this... Hide quoted text -
> - Show quoted text -
Yes, you are correct, that works and I am aware of this. I was hoping
not to go this route because I already have multiple instances already
defined to allow different branches of development to run in
parallel. So, I have a DEV01, DEV02, and DEV03 instances to allow
three parallel branches of development. I'd have to effectively
double the number of instances (so i would have a DEV01a, DEV01b,
DEV02a DEV02b, DEV03a, DEV03b instance). I was hoping to not have to
manage this many instances on a single server. Sounds like I need to
acquire additional hardware. Is anyone aware of a way to get around
this error? Why does it work in 2000 and not in 2005?
Thanks