SQL Server Performance Forum – Threads Archive

MSDTC problem

Hi ! , We have a stored procedure which joins two tables on two separate servers in the same domain .
We use linked server logins for this . It runs fine on most of our environments . But on one environment it is giving the following error : Msg 7391, Level 16, State 1, Server EDDATAMG, Procedure WarehouseMigration, Line 81
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]. Please let me know if anybody has a resolution to the problem . The SP code is as follows (Problem is with the highlighted portion of the script ):
CREATE Procedure WarehouseMigration
AS /******************************************************************************
**File: WarehouseMigration.PRC
**SP Name: Warehouse
**DB Name: Migration
**
**Desc: Migrates all of the data in the Warehouse table
**
** Assumption: linked server login should be established between
**the source and destination servers for this to work
**
**Parameters:
**InputOutput
**———————
**
*******************************************************************************/ PRINT ‘Migrating Warehouse data’ INSERT Location..Warehouse
(WhsNo
, Name
,WhsType
, Addr1
, Addr2
, Addr3
, City
, State
, Zip
, Country
, Phone
, Manager
, OpenDate
, CloseDate
, DisplayOnWeb
, B2BMinDeliveryAmt
, CreateDate
, ModifiedDate)
SELECTw.WHNumber
, w.WHName
,1
, w.WHAddressLine1
, w.WHAddressLine2
, NULL
, w.WHCity
, NULL
, w.WHPostalCode
, c.CountryDescription
, NULL
, w.WHManagerName
, w.WHOpenDate
, NULL
, w.WHDisplayOnWeb
, cast(w.B2BMinDeliveryAmt as money)/100
, getdate()
, NULL
FROMOldSite_08.Location.dbo.Warehouse w
JOIN OldSite_08.Location.dbo.StateOrProv sp ON (w.StateOrProvKey = sp.StateOrProvKey)
JOIN OldSite_08.location.dbo.Country c ON (sp.CountryKey = c.CountryKey)
WHEREWHAddressLine1 IS NOT NULL
ORDER BY w.WHNumber
PRINT ‘Updating Warehouse.State column’ UPDATE w2
SET State = s.StateName
FROM OldSite_08.location.dbo.warehouse w
JOIN OldSite_08.location.dbo.StateOrProv sp ON (w.StateOrProvKey = sp.StateOrProvKey)
JOIN Location..State s ON (sp.StateOrProvDescription = s.StateName)
JOIN Location..Warehouse w2 ON (w.WHNumber = w2.WhsNo) PRINT ‘Updating Warehouse.Phone column’ UPDATE w2
SET Phone = wt.WHTelephoneNumber
FROM Location..Warehouse w2
JOIN OldSite_08.location.dbo.warehouse w ON (w.WHNumber = w2.WhsNo)
JOINOldSite_08.location.dbo.WHTelephone wt ON (w.WarehouseKey = wt.WarehouseKey)
WHERE TelephoneTypeKey = 1
PRINT ‘Updating Warehouse.WhsType column for BD warehouses’ UPDATELocation..Warehouse
SETWhsType = 2
WHEREWhsNo in (11,17,115,401,822,823,827)
GO Pranab

---

From BOL: Error 7391
Severity Level 16
Message Text
The operation could not be performed because the OLE DB provider ‘%ls’ was unable to begin a distributed transaction. Explanation
This error can occur while processing an INSERT, UPDATE, or DELETE statement inside an explicit or implicit transaction. This indicates that the OLE DB provider does not support distributed transactions, which is needed for data modification statements inside an explicit or implicit transaction. A data modification statement can be executed against such a provider only in the case where the statement is a transaction by itself. Action
Verify that the OLE DB provider specified supports distributed transactions. If the provider does not support distributed transactions, rewrite the data modification statement not to use distributed transactions.
See Also Distributed Queries HTH Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

---

Hi Pranab, Which versions of SQL and Operating System?
Any firewalls between the two?
does DTCPing work between those two servers? Cheers
Twan

---

.. and also refer to the following threads :
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2155&SearchTerms=7391
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1367&SearchTerms=7391
… with similar issue to resolve. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

]]>