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

Looking for thoughts on my upgrade strategy

Hello,

We've run upgrade advisor and fixed everything and we're ready to upgrade from sql 2k to sql 2k5. We did find a problem though. Our current sql2k has an instance name of "dbserver". The problem is that we wanted to put CRM 3.0 data on this instance as well, but I guess CRM 3.0 will only install based upon the default instance only. So here is my thoughts and was wondering if someone see any problems on this process.

1. Install another sql2k instance, using the default instance name this time, on the current w2k3 box which holds our current sql2k instance named "dbserver".

2. Once default instance is up and running, transfer over logins, operators.

3. Stop current live instance "dbserver"

4. Use new default sql2k instance and attach all databases from the stopped "dbserver" instance other than master, tempdb, model, msdb.

5. Run the upgrade on default instance, which now has all current databases associated to it, for sql2k5.

6. Uninstall "dbserver" instance.

Anyone see any problems on suggestions? Thanks in advance.

You approach will work but there is a quicker option you could consider. You could install a default instance of SQL Server 2005 and migrate your databases and logins directly over to that new instance instead of going through the intermediate SQL 2000 installation step.

Michelle

Looking for SQL 2000 to SQL 2005 upgrade

All,

I am looking for upgrading SQL 2000 databases to SQL 2005.Has anybody got a detailed plan /Documents for doing so?.

Thanks and Regards

Avijit Macker

Here's a good start... you can start with Upgrade Advisor and check out the technical reference guide.

http://www.microsoft.com/sql/solutions/upgrade/default.mspx

Thanks,
Sam Lester (MSFT)

|||Thanks..this was quite usefull

Looking for SQL 2000 to SQL 2005 upgrade

All,

I am looking for upgrading SQL 2000 databases to SQL 2005.Has anybody got a detailed plan /Documents for doing so?.

Thanks and Regards

Avijit Macker

Here's a good start... you can start with Upgrade Advisor and check out the technical reference guide.

http://www.microsoft.com/sql/solutions/upgrade/default.mspx

Thanks,
Sam Lester (MSFT)

|||Thanks..this was quite usefull

Looking for file s80305i.exe

Hi all
I'm trying to track down a copy of the file s80305i.exe for use in a
SQL server 2000 upgrade and was wondering if anyone knew of a location
as it is a broken link on the Microsoft website.
My email is ian.hayward@.preferredmortgages.com if anyone could email me
a copy.
Many thanks in advance.
Ian
ihayward
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message330249.html
Please contact PSS and the engineer who you work with should be able to
locate the hotfix for you internally.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||I believe that you got the link from
http://support.microsoft.com/?kbid=300676, correct? If you cut & paste the
following text into the address bar of your browser,
ftp://ftp.microsoft.com/bussys/sql/t.../sql80/setup/, does it work?
I ask because even though clicking on the link worked for me a couple of
minutes ago, earlier this week it wasn't. When I manually put that link into
the address bar of my copy of Internet Explorer earlier this week, it did.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"ihayward" <ihayward.1a7j53@.mail.webservertalk.com> wrote in message
news:ihayward.1a7j53@.mail.webservertalk.com...
> Hi all
> I'm trying to track down a copy of the file s80305i.exe for use in a
> SQL server 2000 upgrade and was wondering if anyone knew of a location
> as it is a broken link on the Microsoft website.
> My email is ian.hayward@.preferredmortgages.com if anyone could email me
> a copy.
> Many thanks in advance.
> Ian
>
> --
> ihayward
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message330249.html
>
|||That link is definitely cursed :-)
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:ORSj7jldEHA.212@.TK2MSFTNGP12.phx.gbl...
> I believe that you got the link from
> http://support.microsoft.com/?kbid=300676, correct? If you cut & paste the
> following text into the address bar of your browser,
> ftp://ftp.microsoft.com/bussys/sql/t.../sql80/setup/, does it work?
> I ask because even though clicking on the link worked for me a couple of
> minutes ago, earlier this week it wasn't. When I manually put that link
into
> the address bar of my copy of Internet Explorer earlier this week, it did.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "ihayward" <ihayward.1a7j53@.mail.webservertalk.com> wrote in message
> news:ihayward.1a7j53@.mail.webservertalk.com...
>
|||can you send me this file too...
thank you
gallus
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||can I get this file too...
my email adress is gallus68@.web.de
thank you
gallus
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Looking for file s80305i.exe

Hi all
I'm trying to track down a copy of the file s80305i.exe for use in
SQL server 2000 upgrade and was wondering if anyone knew of a locatio
as it is a broken link on the Microsoft website.
My email is ian.hayward@.preferredmortgages.com if anyone could email m
a copy.
Many thanks in advance.
Ia
-
ihaywar
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message330249.htmPlease contact PSS and the engineer who you work with should be able to
locate the hotfix for you internally.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I believe that you got the link from
http://support.microsoft.com/?kbid=300676, correct? If you cut & paste the
following text into the address bar of your browser,
ftp://ftp.microsoft.com/bussys/sql/transfer/sql80/setup/, does it work?
I ask because even though clicking on the link worked for me a couple of
minutes ago, earlier this week it wasn't. When I manually put that link into
the address bar of my copy of Internet Explorer earlier this week, it did.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"ihayward" <ihayward.1a7j53@.mail.webservertalk.com> wrote in message
news:ihayward.1a7j53@.mail.webservertalk.com...
> Hi all
> I'm trying to track down a copy of the file s80305i.exe for use in a
> SQL server 2000 upgrade and was wondering if anyone knew of a location
> as it is a broken link on the Microsoft website.
> My email is ian.hayward@.preferredmortgages.com if anyone could email me
> a copy.
> Many thanks in advance.
> Ian
>
> --
> ihayward
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message330249.html
>|||That link is definitely cursed :-)
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:ORSj7jldEHA.212@.TK2MSFTNGP12.phx.gbl...
> I believe that you got the link from
> http://support.microsoft.com/?kbid=300676, correct? If you cut & paste the
> following text into the address bar of your browser,
> ftp://ftp.microsoft.com/bussys/sql/transfer/sql80/setup/, does it work?
> I ask because even though clicking on the link worked for me a couple of
> minutes ago, earlier this week it wasn't. When I manually put that link
into
> the address bar of my copy of Internet Explorer earlier this week, it did.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "ihayward" <ihayward.1a7j53@.mail.webservertalk.com> wrote in message
> news:ihayward.1a7j53@.mail.webservertalk.com...
> >
> > Hi all
> >
> > I'm trying to track down a copy of the file s80305i.exe for use in a
> > SQL server 2000 upgrade and was wondering if anyone knew of a location
> > as it is a broken link on the Microsoft website.
> >
> > My email is ian.hayward@.preferredmortgages.com if anyone could email me
> > a copy.
> >
> > Many thanks in advance.
> >
> > Ian
> >
> >
> >
> > --
> > ihayward
> > ---
> > Posted via http://www.webservertalk.com
> > ---
> > View this thread: http://www.webservertalk.com/message330249.html
> >
>

Looking for file s80305i.exe

Hi all
I'm trying to track down a copy of the file s80305i.exe for use in a SQL ser
ver 2000 upgrade and was wondering if anyone knew of a location as it is a b
roken link on the Microsoft website.
My email is ian.hayward@.preferredmortgages.com if anyone could email me a co
py.
Many thanks in advance.
IanPlease contact PSS and the engineer who you work with should be able to
locate the hotfix for you internally.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I believe that you got the link from
http://support.microsoft.com/?kbid=300676, correct? If you cut & paste the
following text into the address bar of your browser,
ftp://ftp.microsoft.com/bussys/sql/...r/sql80/setup/, does it work?
I ask because even though clicking on the link worked for me a couple of
minutes ago, earlier this week it wasn't. When I manually put that link into
the address bar of my copy of Internet Explorer earlier this week, it did.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"ihayward" <ihayward.1a7j53@.mail.webservertalk.com> wrote in message
news:ihayward.1a7j53@.mail.webservertalk.com...
> Hi all
> I'm trying to track down a copy of the file s80305i.exe for use in a
> SQL server 2000 upgrade and was wondering if anyone knew of a location
> as it is a broken link on the Microsoft website.
> My email is ian.hayward@.preferredmortgages.com if anyone could email me
> a copy.
> Many thanks in advance.
> Ian
>
> --
> ihayward
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message330249.html
>|||That link is definitely cursed :-)
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:ORSj7jldEHA.212@.TK2MSFTNGP12.phx.gbl...
> I believe that you got the link from
> http://support.microsoft.com/?kbid=300676, correct? If you cut & paste the
> following text into the address bar of your browser,
> ftp://ftp.microsoft.com/bussys/sql/...r/sql80/setup/, does it work?
> I ask because even though clicking on the link worked for me a couple of
> minutes ago, earlier this week it wasn't. When I manually put that link
into
> the address bar of my copy of Internet Explorer earlier this week, it did.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "ihayward" <ihayward.1a7j53@.mail.webservertalk.com> wrote in message
> news:ihayward.1a7j53@.mail.webservertalk.com...
>|||can you send me this file too...
thank you
gallus
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||can I get this file too...
my email adress is gallus68@.web.de
thank you
gallus
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!