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
>

Msg 7391 / JoinTransaction returned 0x8004d00a

I'm having trouble doing distributed transactions in a stored procedure. It
boils down to this example.
This works in Query Analyzer:
--begin distributed transaction
insert into [39.80.0.26\x3].[sm].dbo.events
select * from events
where time = @.time and seq = @.seq
--commit
But it does not work in Query Analyzer when I uncomment the distributed
xaction and commit.
I get this error:
Server: Msg 7391, Level 16, State 1, Line 12
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].
I'm using sqlserver 8.0.818 on Win2003 server on both sides.
I tried several things to fix it, but to no avail:
@.@.servername matches the %computername% on each machine.
I enabled DTC Network Access using dcomcnfg.exe (all four options now
enabled). I then restarted DTC and sqlserver.
I verified DTC runs as NetworkService.
I even used telnet to verify the two machines can connect to each other's
port 135.
** WHAT HAVE I MISSED ? **
How do I get distributed transactions to work on these servers?
--MikeHi
have you checked out:
http://groups-beta.google.com/group...=UTF-8&oe=UTF-8
John
"Mike" wrote:

> I'm having trouble doing distributed transactions in a stored procedure.
It
> boils down to this example.
> This works in Query Analyzer:
> --begin distributed transaction
> insert into [39.80.0.26\x3].[sm].dbo.events
> select * from events
> where time = @.time and seq = @.seq
> --commit
> But it does not work in Query Analyzer when I uncomment the distributed
> xaction and commit.
> I get this error:
> Server: Msg 7391, Level 16, State 1, Line 12
> 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].
> I'm using sqlserver 8.0.818 on Win2003 server on both sides.
> I tried several things to fix it, but to no avail:
> @.@.servername matches the %computername% on each machine.
> I enabled DTC Network Access using dcomcnfg.exe (all four options now
> enabled). I then restarted DTC and sqlserver.
> I verified DTC runs as NetworkService.
> I even used telnet to verify the two machines can connect to each other's
> port 135.
> ** WHAT HAVE I MISSED ? **
> How do I get distributed transactions to work on these servers?
> --Mike

MSDTC, Active Diretory, DAO, And Transactions

If this is in the wrong forum, my apologies.
Here is the situation. We have a .Net application that someconsultants developed several years ago. They made use of DAO fordatabase access, using some inbuilt classes like ContextUtil to get atransation environment.
Until last weekend, the envorinment was:
Web application on IIS5 / Windows 2000 Advanced Server
Database on SQL2000 on Windows 2000 Advanced Server
We switched over to a new database server environment - a cluster oftwo Windows 2003 servers wth SQL Server 2000. This requiredActive Directory to be set up on the two database server machines.
Everything worked but this particular application - where it goes toupdate the database. It appears (but since I haven't yet beenable to rebuild the .Net solution) that the error - "Object referencenot set to an instance of object." is in fact a spurious error causedwhen the transation tries to roll back.
I have spent about 10 hours on this, and have come to the conclusionthat the fact I have MSDTC involved on a database server that is in anAD domain, while the web server that is issuing the transaction isoutside that domain is the cause of the problem. Now, I don'twant to put AD on the IIS 5 web server - we are migrating this to a newWin2k3/IIS6 server in 5 or 6 weeks time.
Any suggestions? If I have to, I can move the database for thisparticular app back to the non-clustered Windows 2000 server (which isalso outside the AD domain).
Thanks for your indulgance.
Microsoft requirement and good practice is a database Server is just a member server, it should not have anything to do with AD users. If your application is using DAO(data Access object) which was obselete in 1998 you may need MSDTC, the error could be you have not properly configured distributed transaction in SQL Server. Run a search for distributed transaction in the BOL(books online). The advice keep your application from none database users and AD. Hope this helps.

MSDTC fails on Windows 2003 but security has been reset so its not that.

I am running SQL Server 2000 (clustered server) with Windows 2003. For some reason MSDTC is failing on distributed transactions. I can run code that begins and commits a transaction from query analyzer, or run a stored procedure from Query Analyzer with the same code in it and both work great, but if I call the stored procedure from a DTS job, then the job fails with the following error:

DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14) Error string: The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction. Error source: ..... Error string: OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a]. Error source: Microsoft OLE DB Provider for SQL Serve. The step failed.

Any help would be appreciated!Ensure MSDTC is started on the machine.
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3451 for your reference.

MSDTC and Delete transactions

Hello
I had a question reagrding MSDTC usage. I know that MSDTC will enlist
each connection transactions as it's own (take ownership) and
commit/abort accordingly.

However I'm not sure what would happen where I have a stored proc with
input parameters and 2 Delete commands on 2 different tables. In this
case, would MSDTC also enforce ACID rules when 1 cmd succeeds and 2nd
aborts ? In the script for the Stored Proc, there is no checking for
@.@.ERROR value. There are just DELETE statement and a plain RETURN.

thanks
Sunitsjoshi (sjoshi@.ingr.com) writes:

Quote:

Originally Posted by

I had a question reagrding MSDTC usage. I know that MSDTC will enlist
each connection transactions as it's own (take ownership) and
commit/abort accordingly.
>
However I'm not sure what would happen where I have a stored proc with
input parameters and 2 Delete commands on 2 different tables. In this
case, would MSDTC also enforce ACID rules when 1 cmd succeeds and 2nd
aborts ? In the script for the Stored Proc, there is no checking for
@.@.ERROR value. There are just DELETE statement and a plain RETURN.

In a normal stored procedure if you have:

BEGIN TRANSACTION
DELETE tbl WHERE ...
DELETE tbl2 WHERE ...
COMMIT TRANSACTION

you could very well end up with only one of the DELETE statements
being carried out, becuase many errors in SQL Server aborts the
current statment only.

A distributed transaction may be different. At least in some
situations there is a requirement that SET XACT_ABORT is ON. With
this settings all errors but compile errors abort the batch and
rollback the transaction.

But exactly what happens, I will have to admit that I don't know. The
best advice I can give is to test a scenario where one of the DELETE
statement fails. No, wait, the best is to either add checks on @.@.error
or make sure XACT_ABORT is ON.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx