Msg 7391 and distributed transactions

Hi,
We are wondering what setting needs to be changed to get a large distributed
transaction to run.
We have a W2K3 server, upgraded from W2K with SQL2000 SP3A applied that can
run a large distributed transaction to another W2K3 server, upgraded from
W2K with SQL2000 SP3A applied. When we try the same transaction to a newly
created W2K3 server with SQL2000 SP3A applied we get the 7391 message. We
did notice a difference in a setting for MSDTC that we made the same but
this has not worked.
Is there something that we missed because the new server is not an upgraded
W2K3 server from W2K?
Thanks
Chris Wood
Alberta Department of Energy
CANADA
See "How to enable network DTC access in Windows Server 2003"
http://support.microsoft.com/default...b;en-us;817064
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OsrpVxPvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We are wondering what setting needs to be changed to get a large
> distributed
> transaction to run.
> We have a W2K3 server, upgraded from W2K with SQL2000 SP3A applied that
> can
> run a large distributed transaction to another W2K3 server, upgraded from
> W2K with SQL2000 SP3A applied. When we try the same transaction to a newly
> created W2K3 server with SQL2000 SP3A applied we get the 7391 message. We
> did notice a difference in a setting for MSDTC that we made the same but
> this has not worked.
> Is there something that we missed because the new server is not an
> upgraded
> W2K3 server from W2K?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>

Msg 7391 and distributed transactions

Hi,
We are wondering what setting needs to be changed to get a large distributed
transaction to run.
We have a W2K3 server, upgraded from W2K with SQL2000 SP3A applied that can
run a large distributed transaction to another W2K3 server, upgraded from
W2K with SQL2000 SP3A applied. When we try the same transaction to a newly
created W2K3 server with SQL2000 SP3A applied we get the 7391 message. We
did notice a difference in a setting for MSDTC that we made the same but
this has not worked.
Is there something that we missed because the new server is not an upgraded
W2K3 server from W2K?
Thanks
Chris Wood
Alberta Department of Energy
CANADASee "How to enable network DTC access in Windows Server 2003"
http://support.microsoft.com/default.aspx?scid=kb;en-us;817064
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OsrpVxPvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We are wondering what setting needs to be changed to get a large
> distributed
> transaction to run.
> We have a W2K3 server, upgraded from W2K with SQL2000 SP3A applied that
> can
> run a large distributed transaction to another W2K3 server, upgraded from
> W2K with SQL2000 SP3A applied. When we try the same transaction to a newly
> created W2K3 server with SQL2000 SP3A applied we get the 7391 message. We
> did notice a difference in a setting for MSDTC that we made the same but
> this has not worked.
> Is there something that we missed because the new server is not an
> upgraded
> W2K3 server from W2K?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>

Msg 7391 and distributed transactions

Hi,
We are wondering what setting needs to be changed to get a large distributed
transaction to run.
We have a W2K3 server, upgraded from W2K with SQL2000 SP3A applied that can
run a large distributed transaction to another W2K3 server, upgraded from
W2K with SQL2000 SP3A applied. When we try the same transaction to a newly
created W2K3 server with SQL2000 SP3A applied we get the 7391 message. We
did notice a difference in a setting for MSDTC that we made the same but
this has not worked.
Is there something that we missed because the new server is not an upgraded
W2K3 server from W2K?
Thanks
Chris Wood
Alberta Department of Energy
CANADASee "How to enable network DTC access in Windows Server 2003"
http://support.microsoft.com/defaul...kb;en-us;817064
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OsrpVxPvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We are wondering what setting needs to be changed to get a large
> distributed
> transaction to run.
> We have a W2K3 server, upgraded from W2K with SQL2000 SP3A applied that
> can
> run a large distributed transaction to another W2K3 server, upgraded from
> W2K with SQL2000 SP3A applied. When we try the same transaction to a newly
> created W2K3 server with SQL2000 SP3A applied we get the 7391 message. We
> did notice a difference in a setting for MSDTC that we made the same but
> this has not worked.
> Is there something that we missed because the new server is not an
> upgraded
> W2K3 server from W2K?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>

MSDTC Problem

Hi,

I recently moved & upgraded a database from 7.0 to 2000 that makes use of linked servers. After upgrading, a number of queries started failing with the error below. These linked servers with this problem either had a stopped MSDTC or were behind a firewall. I've read the articles on how to configure MSDTC to work through a firewall, but before I do this I'm wondering why this is even happening. There were no problems when the database was at SQL 7.0. Does anyone know why sql 2000 would need to use MSDTC, but SQL7.0 would not? I don't need to use a distributed transaction, so ideally I'd like to somehow tell SQL not to use MSDTC at all. Any ideas?

Thanks

Server: Msg 7391, Level 16, State 1, Line 1
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].Thanks in advance for any help you can offer.

I have same problem as cpyne.

I recently upgraded a SQL server 7.0 box to SQL server 2000.
(Standard edition)

I had several stored procs selecting data from linked servers and inserting into local tables.

They all ran fine for the last 2 years but after the upgrade they all fail.
Linked servers include SQL Server, ORACLE and INFORMIX databases.

Error report indicates that MSDTC is now unable to begin distributed transaction.

Anybody know why MSDTC is suddenly failing after I switched from version 7 to 2000?|||On most of our servers that had this problem, MSDTC was stopped on the remote server. Starting it and setting it to automatic fixed the issue. There were a few queries that didn't even appear to be distributed transactions. They were just local tables with joins to linked servers. I spoke with MS about this and they said that SQL 2000 makes more use for MSDTC in places where SQL 7.0 didn't. The only servers where we had difficulty getting this working were ones where there was a firewall between the master server and the linked ones. In the end we dicided to chang the logic of our application, but MS suggested some articles that listed which ports needed to be opened. I don't have them handy, but if you have this problem, search on Firewall and MSDTC.

Charlie

MSDTC

We just upgraded from SQL 2000 to SQL 2005. To do so we had to uninstall SQL
and install 2005 from scratch.
The problem now is we get this error:
Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.
When trying to Exececute a SP on a remote server where that SP has code
accessing the original server. Example:
ServerA has a Stored Procedure called SP1 in which there is a select to
serverB
ServerB execute SP1 has ServerA.MyDB.Dbo.SP1.
Any help would be greatly appreciated.
Thanks
Hartco schrieb:
> We just upgraded from SQL 2000 to SQL 2005. To do so we had to uninstall SQL
> and install 2005 from scratch.
> The problem now is we get this error:
> Msg 3910, Level 16, State 2, Line 1
> Transaction context in use by another session.
> When trying to Exececute a SP on a remote server where that SP has code
> accessing the original server. Example:
> ServerA has a Stored Procedure called SP1 in which there is a select to
> serverB
> ServerB execute SP1 has ServerA.MyDB.Dbo.SP1.
> Any help would be greatly appreciated.
> Thanks
>
You should debug the SP and see which exact line causes the error.
There is also an older DTS Bug in SQL Server 7/8:
http://support.microsoft.com/?scid=k...d=2852&sid=150
(that does not directly apply to your problems, but maybe its usefull..)
hth
Gregor Stefka
|||Thanks for your answer Gregor
This problem is Not in DTS nor SSIS.
The problem exist only since SQL 2005.
The SP is debuged. Here's more details:
On ServerA create a StoreProcedure called SP1 that Simply does Select name
From ServerB.Master.Sys.Sysdatabases
On ServerB
Forgot to mention (Sorry it's very important) To get the error you have to
do:
Create Table #MyTempTable (Name Varchar(100))
Insert into #MyTempTable
Exec ServerA.FileUploader.Dbo.GateProc

MSDTC

We just upgraded from SQL 2000 to SQL 2005. To do so we had to uninstall SQL
and install 2005 from scratch.
The problem now is we get this error:
Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.
When trying to Exececute a SP on a remote server where that SP has code
accessing the original server. Example:
ServerA has a Stored Procedure called SP1 in which there is a select to
serverB
ServerB execute SP1 has ServerA.MyDB.Dbo.SP1.
Any help would be greatly appreciated.
ThanksHartco schrieb:
> We just upgraded from SQL 2000 to SQL 2005. To do so we had to uninstall S
QL
> and install 2005 from scratch.
> The problem now is we get this error:
> Msg 3910, Level 16, State 2, Line 1
> Transaction context in use by another session.
> When trying to Exececute a SP on a remote server where that SP has code
> accessing the original server. Example:
> ServerA has a Stored Procedure called SP1 in which there is a select to
> serverB
> ServerB execute SP1 has ServerA.MyDB.Dbo.SP1.
> Any help would be greatly appreciated.
> Thanks
>
You should debug the SP and see which exact line causes the error.
There is also an older DTS Bug in SQL Server 7/8:
http://support.microsoft.com/?scid=...id=2852&sid=150
(that does not directly apply to your problems, but maybe its usefull..)
hth
Gregor Stefka|||Thanks for your answer Gregor
This problem is Not in DTS nor SSIS.
The problem exist only since SQL 2005.
The SP is debuged. Here's more details:
On ServerA create a StoreProcedure called SP1 that Simply does Select name
From ServerB.Master.Sys.Sysdatabases
On ServerB
Forgot to mention (Sorry it's very important) To get the error you have to
do:
Create Table #MyTempTable (Name Varchar(100))
Insert into #MyTempTable
Exec ServerA.FileUploader.Dbo.GateProc