Loopback Server Policy

Hello all,

Recently, we ran into the issue that you can't do an insert into..exec statement on a loopback linked server that was previously commented on in:

http://www.dbnewsgroups.net/link.aspx?url=http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124137&SiteID=1

So, for example, if you have a linkedserver to a database that happens to be on the same server as the querying thread, it fails with the message 'context in use'.

The answer from the previous thread was, don't use linked servers when the database is on the same server.

However,

The enviornment in our production system is fairly dynamic -- operations can be expected to move databases around in response to load balancing issues. We were counting on linked servers to make certain (non-performance sensitive) queries without regard to where a given database was located. Accepting that we have to make an exception case where the database lives on the same server means we'll have to have two sets of queries for every case this happens.

Something like

(pseudocode)

If server of linkedserver <> @.@.server

Insert into table....

Exec linkedserver.database.dbo.sproc

Else

Insert Into Table

exec database.dbo.sproc

(end pseudocode)

This seems pretty kludgy to me -- any suggestions on how to better manage this situation?

Thanks in advance

use cluster services instead of linked server

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

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:
>
>
>
> - 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
sql

Loopback linked servers

I want to set a server up to be able to reference itself
via an alias in a 4-part name. Let us call the server
SQLSERVER and its alias BILLING. The motive for this is
to be able to, move the so-referenced databases to another
server when I want to and reference that truely remote
server by the BILLING alias.
I have the alias set-up correctly and the server linked
via that alias with the proper security contexts.
When I query:
select * from [billing].master.dbo.sysdatabases
I get the resultset I want.
When I query:
begin tran
select * from [billing].master.dbo.sysdatabases
rollback tran
I get the following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
If I use BILLING to alias another server (a truely remote
server), the transactioned query works without error.
I've came to understand this as a loopback linked server limitation
in that loopback linked servers cannot enlist in a distributed transaction.
But, I made a table, x, on sqlserver.Master with one int column, x
I made a stored proc, spx, on sqlserver.Master that takes one parameter
and inserts it's value into table x
On Sql Server I did the following
Use Master
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 1
select @.@.Trancount TranCount
if @.@.trancount != 0 commit tran
select * from [BILLING].Master.dbo.x
-- Saw 1 and no errors so transaction worked.
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 2
select @.@.Trancount TranCount
if @.@.trancount != 0 rollback tran
select * from [BILLING].Master.dbo.x
-- Saw 2 and no errors so transaction worked.
go
begin tran
select * from [BILLING].Master.dbo.x
-- Saw same error as cited above as expected.
if @.@.trancount != 0 rollback tran
What is going on? Transactions should have failed in stored proc usage
as well as a direct query if this were a loopback failure to enlist a
distributed transaction, shouldn't they?Hi
There are problems with loopbacks within implicit, explicit or distributed
transactions see http://tinyurl.com/5dlnq
John
"de_corum" <de_corum@.hotmail.com> wrote in message
news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> I want to set a server up to be able to reference itself
> via an alias in a 4-part name. Let us call the server
> SQLSERVER and its alias BILLING. The motive for this is
> to be able to, move the so-referenced databases to another
> server when I want to and reference that truely remote
> server by the BILLING alias.
> I have the alias set-up correctly and the server linked
> via that alias with the proper security contexts.
> When I query:
> select * from [billing].master.dbo.sysdatabases
> I get the resultset I want.
> When I query:
> begin tran
> select * from [billing].master.dbo.sysdatabases
> rollback tran
> I get the following error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> If I use BILLING to alias another server (a truely remote
> server), the transactioned query works without error.
> I've came to understand this as a loopback linked server limitation
> in that loopback linked servers cannot enlist in a distributed
transaction.
> But, I made a table, x, on sqlserver.Master with one int column, x
> I made a stored proc, spx, on sqlserver.Master that takes one parameter
> and inserts it's value into table x
> On Sql Server I did the following
> Use Master
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 1
> select @.@.Trancount TranCount
> if @.@.trancount != 0 commit tran
> select * from [BILLING].Master.dbo.x
> -- Saw 1 and no errors so transaction worked.
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 2
> select @.@.Trancount TranCount
> if @.@.trancount != 0 rollback tran
> select * from [BILLING].Master.dbo.x
> -- Saw 2 and no errors so transaction worked.
> go
> begin tran
> select * from [BILLING].Master.dbo.x
> -- Saw same error as cited above as expected.
> if @.@.trancount != 0 rollback tran
> What is going on? Transactions should have failed in stored proc usage
> as well as a direct query if this were a loopback failure to enlist a
> distributed transaction, shouldn't they?
>
>|||Ok, but why does it work when I am executing stored procs against the
loopback server and not when I am directly querying the tables?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ue4HnKHiEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> There are problems with loopbacks within implicit, explicit or distributed
> transactions see http://tinyurl.com/5dlnq
> John
> "de_corum" <de_corum@.hotmail.com> wrote in message
> news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> > I want to set a server up to be able to reference itself
> > via an alias in a 4-part name. Let us call the server
> > SQLSERVER and its alias BILLING. The motive for this is
> > to be able to, move the so-referenced databases to another
> > server when I want to and reference that truely remote
> > server by the BILLING alias.
> >
> > I have the alias set-up correctly and the server linked
> > via that alias with the proper security contexts.
> >
> > When I query:
> >
> > select * from [billing].master.dbo.sysdatabases
> >
> > I get the resultset I want.
> >
> > When I query:
> >
> > begin tran
> > select * from [billing].master.dbo.sysdatabases
> > rollback tran
> >
> > I get the following error:
> >
> > Server: Msg 7391, Level 16, State 1, Line 2
> > The operation could not be performed because the OLE DB
> > provider 'SQLOLEDB' was unable to begin a distributed
> > transaction.
> > [OLE/DB provider returned message: New transaction cannot
> > enlist in the specified transaction coordinator. ]
> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> > ITransactionJoin::JoinTransaction returned 0x8004d00a].
> >
> > If I use BILLING to alias another server (a truely remote
> > server), the transactioned query works without error.
> >
> > I've came to understand this as a loopback linked server limitation
> > in that loopback linked servers cannot enlist in a distributed
> transaction.
> >
> > But, I made a table, x, on sqlserver.Master with one int column, x
> > I made a stored proc, spx, on sqlserver.Master that takes one parameter
> > and inserts it's value into table x
> >
> > On Sql Server I did the following
> >
> > Use Master
> > go
> > if @.@.Trancount = 0 begin tran
> > exec [BILLING].Master.dbo.spx 1
> > select @.@.Trancount TranCount
> > if @.@.trancount != 0 commit tran
> >
> > select * from [BILLING].Master.dbo.x
> > -- Saw 1 and no errors so transaction worked.
> > go
> >
> > if @.@.Trancount = 0 begin tran
> > exec [BILLING].Master.dbo.spx 2
> > select @.@.Trancount TranCount
> > if @.@.trancount != 0 rollback tran
> >
> > select * from [BILLING].Master.dbo.x
> > -- Saw 2 and no errors so transaction worked.
> > go
> >
> > begin tran
> > select * from [BILLING].Master.dbo.x
> > -- Saw same error as cited above as expected.
> > if @.@.trancount != 0 rollback tran
> >
> > What is going on? Transactions should have failed in stored proc usage
> > as well as a direct query if this were a loopback failure to enlist a
> > distributed transaction, shouldn't they?
> >
> >
> >
> >
>

Loopback linked servers

I want to set a server up to be able to reference itself
via an alias in a 4-part name. Let us call the server
SQLSERVER and its alias BILLING. The motive for this is
to be able to, move the so-referenced databases to another
server when I want to and reference that truely remote
server by the BILLING alias.
I have the alias set-up correctly and the server linked
via that alias with the proper security contexts.
When I query:
select * from [billing].master.dbo.sysdatabases
I get the resultset I want.
When I query:
begin tran
select * from [billing].master.dbo.sysdatabases
rollback tran
I get the following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
If I use BILLING to alias another server (a truely remote
server), the transactioned query works without error.
I've came to understand this as a loopback linked server limitation
in that loopback linked servers cannot enlist in a distributed transaction.
But, I made a table, x, on sqlserver.Master with one int column, x
I made a stored proc, spx, on sqlserver.Master that takes one parameter
and inserts it's value into table x
On Sql Server I did the following
Use Master
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 1
select @.@.Trancount TranCount
if @.@.trancount != 0 commit tran
select * from [BILLING].Master.dbo.x
-- Saw 1 and no errors so transaction worked.
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 2
select @.@.Trancount TranCount
if @.@.trancount != 0 rollback tran
select * from [BILLING].Master.dbo.x
-- Saw 2 and no errors so transaction worked.
go
begin tran
select * from [BILLING].Master.dbo.x
-- Saw same error as cited above as expected.
if @.@.trancount != 0 rollback tran
What is going on? Transactions should have failed in stored proc usage
as well as a direct query if this were a loopback failure to enlist a
distributed transaction, shouldn't they?
Hi
There are problems with loopbacks within implicit, explicit or distributed
transactions see http://tinyurl.com/5dlnq
John
"de_corum" <de_corum@.hotmail.com> wrote in message
news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> I want to set a server up to be able to reference itself
> via an alias in a 4-part name. Let us call the server
> SQLSERVER and its alias BILLING. The motive for this is
> to be able to, move the so-referenced databases to another
> server when I want to and reference that truely remote
> server by the BILLING alias.
> I have the alias set-up correctly and the server linked
> via that alias with the proper security contexts.
> When I query:
> select * from [billing].master.dbo.sysdatabases
> I get the resultset I want.
> When I query:
> begin tran
> select * from [billing].master.dbo.sysdatabases
> rollback tran
> I get the following error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> If I use BILLING to alias another server (a truely remote
> server), the transactioned query works without error.
> I've came to understand this as a loopback linked server limitation
> in that loopback linked servers cannot enlist in a distributed
transaction.
> But, I made a table, x, on sqlserver.Master with one int column, x
> I made a stored proc, spx, on sqlserver.Master that takes one parameter
> and inserts it's value into table x
> On Sql Server I did the following
> Use Master
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 1
> select @.@.Trancount TranCount
> if @.@.trancount != 0 commit tran
> select * from [BILLING].Master.dbo.x
> -- Saw 1 and no errors so transaction worked.
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 2
> select @.@.Trancount TranCount
> if @.@.trancount != 0 rollback tran
> select * from [BILLING].Master.dbo.x
> -- Saw 2 and no errors so transaction worked.
> go
> begin tran
> select * from [BILLING].Master.dbo.x
> -- Saw same error as cited above as expected.
> if @.@.trancount != 0 rollback tran
> What is going on? Transactions should have failed in stored proc usage
> as well as a direct query if this were a loopback failure to enlist a
> distributed transaction, shouldn't they?
>
>
|||Ok, but why does it work when I am executing stored procs against the
loopback server and not when I am directly querying the tables?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ue4HnKHiEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> There are problems with loopbacks within implicit, explicit or distributed
> transactions see http://tinyurl.com/5dlnq
> John
> "de_corum" <de_corum@.hotmail.com> wrote in message
> news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> transaction.
>

Loopback linked servers

I want to set a server up to be able to reference itself
via an alias in a 4-part name. Let us call the server
SQLSERVER and its alias BILLING. The motive for this is
to be able to, move the so-referenced databases to another
server when I want to and reference that truely remote
server by the BILLING alias.
I have the alias set-up correctly and the server linked
via that alias with the proper security contexts.
When I query:
select * from [billing].master.dbo.sysdatabases
I get the resultset I want.
When I query:
begin tran
select * from [billing].master.dbo.sysdatabases
rollback tran
I get the following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
If I use BILLING to alias another server (a truely remote
server), the transactioned query works without error.
I've came to understand this as a loopback linked server limitation
in that loopback linked servers cannot enlist in a distributed transaction.
But, I made a table, x, on sqlserver.Master with one int column, x
I made a stored proc, spx, on sqlserver.Master that takes one parameter
and inserts it's value into table x
On Sql Server I did the following
Use Master
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 1
select @.@.Trancount TranCount
if @.@.trancount != 0 commit tran
select * from [BILLING].Master.dbo.x
-- Saw 1 and no errors so transaction worked.
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 2
select @.@.Trancount TranCount
if @.@.trancount != 0 rollback tran
select * from [BILLING].Master.dbo.x
-- Saw 2 and no errors so transaction worked.
go
begin tran
select * from [BILLING].Master.dbo.x
-- Saw same error as cited above as expected.
if @.@.trancount != 0 rollback tran
What is going on? Transactions should have failed in stored proc usage
as well as a direct query if this were a loopback failure to enlist a
distributed transaction, shouldn't they?Hi
There are problems with loopbacks within implicit, explicit or distributed
transactions see http://tinyurl.com/5dlnq
John
"de_corum" <de_corum@.hotmail.com> wrote in message
news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> I want to set a server up to be able to reference itself
> via an alias in a 4-part name. Let us call the server
> SQLSERVER and its alias BILLING. The motive for this is
> to be able to, move the so-referenced databases to another
> server when I want to and reference that truely remote
> server by the BILLING alias.
> I have the alias set-up correctly and the server linked
> via that alias with the proper security contexts.
> When I query:
> select * from [billing].master.dbo.sysdatabases
> I get the resultset I want.
> When I query:
> begin tran
> select * from [billing].master.dbo.sysdatabases
> rollback tran
> I get the following error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> If I use BILLING to alias another server (a truely remote
> server), the transactioned query works without error.
> I've came to understand this as a loopback linked server limitation
> in that loopback linked servers cannot enlist in a distributed
transaction.
> But, I made a table, x, on sqlserver.Master with one int column, x
> I made a stored proc, spx, on sqlserver.Master that takes one parameter
> and inserts it's value into table x
> On Sql Server I did the following
> Use Master
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 1
> select @.@.Trancount TranCount
> if @.@.trancount != 0 commit tran
> select * from [BILLING].Master.dbo.x
> -- Saw 1 and no errors so transaction worked.
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 2
> select @.@.Trancount TranCount
> if @.@.trancount != 0 rollback tran
> select * from [BILLING].Master.dbo.x
> -- Saw 2 and no errors so transaction worked.
> go
> begin tran
> select * from [BILLING].Master.dbo.x
> -- Saw same error as cited above as expected.
> if @.@.trancount != 0 rollback tran
> What is going on? Transactions should have failed in stored proc usage
> as well as a direct query if this were a loopback failure to enlist a
> distributed transaction, shouldn't they?
>
>|||Ok, but why does it work when I am executing stored procs against the
loopback server and not when I am directly querying the tables?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ue4HnKHiEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> There are problems with loopbacks within implicit, explicit or distributed
> transactions see http://tinyurl.com/5dlnq
> John
> "de_corum" <de_corum@.hotmail.com> wrote in message
> news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> transaction.
>

Loopback Adapter Hides Instance Names

I have laptops that require the loopback adapter when they are not
connected. However when they connect back to the network the networked
servers display just the computer name not the / instancename. Any
ideas? It only happens on network instances. The local instances are
correct. This happens in Enterprise Manager, Access connection tool
and using SQL-DMO.
Gary Shane LimIn message <ogfj31dfgdq76mvs61hj3aofv0k6vpg1j0@.4ax.com>, Shane Lim
<gslim@.blizzardice.com> writes
>I have laptops that require the loopback adapter when they are not
>connected. However when they connect back to the network the networked
>servers display just the computer name not the / instancename. Any
>ideas? It only happens on network instances. The local instances are
>correct. This happens in Enterprise Manager, Access connection tool
>and using SQL-DMO.
>Gary Shane Lim
Have you tried Bridging the Loopback Adapter to your main Lan Adapter.
This way your network settings remain the same whether on the network or
not.
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Loopback Adapter Hides Instance Names

I have laptops that require the loopback adapter when they are not
connected. However when they connect back to the network the networked
servers display just the computer name not the / instancename. Any
ideas? It only happens on network instances. The local instances are
correct. This happens in Enterprise Manager, Access connection tool
and using SQL-DMO.
Gary Shane Lim
In message <ogfj31dfgdq76mvs61hj3aofv0k6vpg1j0@.4ax.com>, Shane Lim
<gslim@.blizzardice.com> writes
>I have laptops that require the loopback adapter when they are not
>connected. However when they connect back to the network the networked
>servers display just the computer name not the / instancename. Any
>ideas? It only happens on network instances. The local instances are
>correct. This happens in Enterprise Manager, Access connection tool
>and using SQL-DMO.
>Gary Shane Lim
Have you tried Bridging the Loopback Adapter to your main Lan Adapter.
This way your network settings remain the same whether on the network or
not.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
sql

Loopback Adapter Hides Instance Names

I have laptops that require the loopback adapter when they are not
connected. However when they connect back to the network the networked
servers display just the computer name not the / instancename. Any
ideas? It only happens on network instances. The local instances are
correct. This happens in Enterprise Manager, Access connection tool
and using SQL-DMO.
Gary Shane LimIn message <ogfj31dfgdq76mvs61hj3aofv0k6vpg1j0@.4ax.com>, Shane Lim
<gslim@.blizzardice.com> writes
>I have laptops that require the loopback adapter when they are not
>connected. However when they connect back to the network the networked
>servers display just the computer name not the / instancename. Any
>ideas? It only happens on network instances. The local instances are
>correct. This happens in Enterprise Manager, Access connection tool
>and using SQL-DMO.
>Gary Shane Lim
Have you tried Bridging the Loopback Adapter to your main Lan Adapter.
This way your network settings remain the same whether on the network or
not.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Loopback adapter

I am running Windows XP and have SQL Server 2005 Enterprise Edition that I use for testing on my notebook. I want to install Oralce 11g on it as well and use VS 2005 as a front end programming. For me to install Oracle I need to install a loopback adapter. If I do this will it mess up SQL Server 2005 or will it not affect SQL Server 2005? Please advise

Hi,

Do you mean you want to install Microsoft Loopback Adapter on your Windows XP machine?

The Microsoft Loopback adapter is a testing tool for a virtual network environment where network access is not available. It will not affect the Microsoft SQLServer2005.

For more information on installation, see:
http://support.microsoft.com/kb/839013/en-us

Thanks.