Unable to begin a distributed transaction

Question

Post reply

Unable to begin a distributed transaction

G Foster-238992

SSC Veteran

Points: 234
More actions
November 16, 2005 at 8:10 am

#90017

I get the following error message when I run a query to do an insert from a linked Oracle 9i database to a SQL Server 2000 database.

Server: Msg 7391, Level 16, State 1, Line 1

The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].

The query that I am running is as follows.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

ALTER   proc sp_populateInd_con

AS

DELETE FROM Ind_Con

INSERT INTO Ind_Con    (CONSIGNEE_ID, CONSIGNEE_TYPE, DATA_SOURCE, DELIVERY_ADDRESS1, DELIVERY_ADDRESS2, DELIVERY_ADDRESS3,

   DELIVERY_ADDRESS4, DELIVERY_ADDRESS5, PHONE_NUMBER, POSTCODE_DISTRICT, POSTCODE_SECTOR, POSTCODE_BRICK,

   BRICK_ID, PC1_TERRITORY_ID, PC2_TERRITORY_ID, PC3_TERRITORY_ID, SP1_TERRITORY_ID, SP2_TERRITORY_ID,

   SP3_TERRITORY_ID, ZENECA_AREA_ID, ZENECA_REGION_ID, CONTACT_NAME, WHOLESALER_CODE, SHORT_NAME, FULL_FORMATTED_ADDRESS,

   MF_ACCOUNT, IGNORE_CUSTOMER, BRICK_WITH_OVERRIDE, OVERRIDE_BRICK, DATA_SOURCE_ID, PLACE_IDENTITY, BUSINESS_UNIT_IDENTITY

   /*** CR: FG 20/06/2005 Audit Trail - Include Date ***/

   , CreatedOn

   /*** END CR: FG 20/06/2005 ***/

  &nbsp

SELECT    CONSIGNEE_ID, CONSIGNEE_TYPE, DATA_SOURCE, DELIVERY_ADDRESS1, DELIVERY_ADDRESS2, DELIVERY_ADDRESS3,

   DELIVERY_ADDRESS4, DELIVERY_ADDRESS5, PHONE_NUMBER, POSTCODE_DISTRICT, POSTCODE_SECTOR, POSTCODE_BRICK,

   BRICK_ID, PC1_TERRITORY_ID, PC2_TERRITORY_ID, PC3_TERRITORY_ID, SP1_TERRITORY_ID, SP2_TERRITORY_ID,

   SP3_TERRITORY_ID, ZENECA_AREA_ID, ZENECA_REGION_ID, CONTACT_NAME, WHOLESALER_CODE, SHORT_NAME, FULL_FORMATTED_ADDRESS,

   MF_ACCOUNT, IGNORE_CUSTOMER, BRICK_WITH_OVERRIDE, OVERRIDE_BRICK, DATA_SOURCE_ID, PLACE_IDENTITY, BUSINESS_UNIT_IDENTITY

   /*** CR: FG 20/06/2005 Audit Trail - Include Date ***/

   , GETDATE()

   /*** END CR: FG 20/06/2005 ***/

FROM OpenQuery   (LOADINGAREA_DWAREHOUSE_PRD, 'SELECT CONSIGNEE_ID, CONSIGNEE_TYPE, DATA_SOURCE, DELIVERY_ADDRESS1, DELIVERY_ADDRESS2, DELIVERY_ADDRESS3,

   DELIVERY_ADDRESS4, DELIVERY_ADDRESS5, PHONE_NUMBER, POSTCODE_DISTRICT, POSTCODE_SECTOR, POSTCODE_BRICK,

   BRICK_ID, PC1_TERRITORY_ID, PC2_TERRITORY_ID, PC3_TERRITORY_ID, SP1_TERRITORY_ID, SP2_TERRITORY_ID,

   SP3_TERRITORY_ID, ZENECA_AREA_ID, ZENECA_REGION_ID, CONTACT_NAME, WHOLESALER_CODE, SHORT_NAME, FULL_FORMATTED_ADDRESS,

   MF_ACCOUNT, IGNORE_CUSTOMER, BRICK_WITH_OVERRIDE, OVERRIDE_BRICK, DATA_SOURCE_ID, PLACE_IDENTITY, BUSINESS_UNIT_IDENTITY FROM MV_INDIRECT_CONS')

WHERE Consignee_ID NOT LIKE '[a-z]%'

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

In the test environment the Distributed Transaction Coordinator is turned on for the SQL Server. But it is not on in the production environment. If I run this query in production it works.

This query was working before in both the production and test environment without the added field CreatedOn in the Insert into table Ind_Con. This new field and the updated query were added to the test environment and now I get an error message.

If I turn off the Distributed Transaction Coordinator in the test environment then I get another error because it is not started and the query needs it.

Here is the previous query.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

ALTER proc sp_populateInd_con

AS

DELETE FROM Ind_Con

INSERT INTO Ind_Con    (CONSIGNEE_ID, CONSIGNEE_TYPE, DATA_SOURCE, DELIVERY_ADDRESS1, DELIVERY_ADDRESS2, DELIVERY_ADDRESS3,

   DELIVERY_ADDRESS4, DELIVERY_ADDRESS5, PHONE_NUMBER, POSTCODE_DISTRICT, POSTCODE_SECTOR, POSTCODE_BRICK,

   BRICK_ID, PC1_TERRITORY_ID, PC2_TERRITORY_ID, PC3_TERRITORY_ID, SP1_TERRITORY_ID, SP2_TERRITORY_ID,

   SP3_TERRITORY_ID, ZENECA_AREA_ID, ZENECA_REGION_ID, CONTACT_NAME, WHOLESALER_CODE, SHORT_NAME, FULL_FORMATTED_ADDRESS,

   MF_ACCOUNT, IGNORE_CUSTOMER, BRICK_WITH_OVERRIDE, OVERRIDE_BRICK, DATA_SOURCE_ID, PLACE_IDENTITY, BUSINESS_UNIT_IDENTITY)

SELECT    CONSIGNEE_ID, CONSIGNEE_TYPE, DATA_SOURCE, DELIVERY_ADDRESS1, DELIVERY_ADDRESS2, DELIVERY_ADDRESS3,

   DELIVERY_ADDRESS4, DELIVERY_ADDRESS5, PHONE_NUMBER, POSTCODE_DISTRICT, POSTCODE_SECTOR, POSTCODE_BRICK,

   BRICK_ID, PC1_TERRITORY_ID, PC2_TERRITORY_ID, PC3_TERRITORY_ID, SP1_TERRITORY_ID, SP2_TERRITORY_ID,

   SP3_TERRITORY_ID, ZENECA_AREA_ID, ZENECA_REGION_ID, CONTACT_NAME, WHOLESALER_CODE, SHORT_NAME, FULL_FORMATTED_ADDRESS,

   MF_ACCOUNT, IGNORE_CUSTOMER, BRICK_WITH_OVERRIDE, OVERRIDE_BRICK, DATA_SOURCE_ID, PLACE_IDENTITY, BUSINESS_UNIT_IDENTITY

FROM OpenQuery   (LOADINGAREA_DWAREHOUSE_PRD, 'SELECT CONSIGNEE_ID, CONSIGNEE_TYPE, DATA_SOURCE, DELIVERY_ADDRESS1, DELIVERY_ADDRESS2, DELIVERY_ADDRESS3,

   DELIVERY_ADDRESS4, DELIVERY_ADDRESS5, PHONE_NUMBER, POSTCODE_DISTRICT, POSTCODE_SECTOR, POSTCODE_BRICK,

   BRICK_ID, PC1_TERRITORY_ID, PC2_TERRITORY_ID, PC3_TERRITORY_ID, SP1_TERRITORY_ID, SP2_TERRITORY_ID,

   SP3_TERRITORY_ID, ZENECA_AREA_ID, ZENECA_REGION_ID, CONTACT_NAME, WHOLESALER_CODE, SHORT_NAME, FULL_FORMATTED_ADDRESS,

   MF_ACCOUNT, IGNORE_CUSTOMER, BRICK_WITH_OVERRIDE, OVERRIDE_BRICK, DATA_SOURCE_ID, PLACE_IDENTITY, BUSINESS_UNIT_IDENTITY FROM MV_INDIRECT_CONS')

WHERE Consignee_ID NOT LIKE '[a-z]%'

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

I am confused as to why the query needs the DTC in the test environment but not production. And what do I need to do to get it to work in test?

Thanks in advance for your help.

P

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

cmille19 SSCertifiable Points: 5960 More actions · Answer 1

I've experienced two problems with MS DTC:

1. In Windows 2003 ensure MS DTC is enabled for network access. It is off by default:

http://support.microsoft.com/default.aspx?scid=kb;en-us;816701

2. If you have Windows 2003 Service Pack 1, Microsoft changed the default security, so you'll need to modify it. This can cause a problem when using MS DTC between an SP1 and a server not running SP1 or running a server running a previous version of the Windows server:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/html/8287866d-1944-4b47-85ce-b80a01af1555.asp

G Foster-238992 SSC Veteran Points: 234 More actions · Answer 2

G Foster-238992

SSC Veteran

Points: 234

November 22, 2005 at 2:07 am

#605156

Sorry, I failed to mention that I have Windows 2000. Is there any information relating to changes that need to be made in the Windows 2000 environment?

G Foster-238992 SSC Veteran Points: 234 More actions · Answer 3

On Microsoft support, I have found a document: 'how to set up and troubleshoot a linked server to Oracle in Sql Server'. It outlines the registry settings depending on the Oracle version. The server I am working with has the registry settings for Oracle version 7.x, but Oracle version 8.1 is installed. Also, some of the files identified in the registry settings for Oracle verision 7.x were not found when I did a search of the hard drives, but all the files for Oracle version 8.1 were found. When the machine is free I am going to update the registry settings with the Oracle version 8.1 files and see if that solves the problem.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI] "OracleXaLib"="oraclient8.dll"

"OracleSqlLib"="orasql8.dll"

"OracleOciLib"="oci.dll"

Catherine Saunders Valued Member Points: 63 More actions · Answer 4

Catherine Saunders

Valued Member

Points: 63

March 7, 2007 at 7:50 am

#693580

Has anyone solved this problem.

I currently have the same issue.

My database is SQL SERVER 2000 , OS -Win 2003 and we are accessing an Oracle 9i DB.

Pls im desperate.I tried one of MS articles , but that caused MSDTC to behave very oddly.

ThaNKS

Thinh Tran-448654 Valued Member Points: 63 More actions · Answer 5

Thinh Tran-448654

Valued Member

Points: 63

June 5, 2007 at 3:53 pm

#710964

Same here..

I am having the same problem

SQL SERVER 2000 - OS win xp and accessing an oracle 10g DB

I've tried the MS article and it does not work.

Thanks!!

Robert Akira Watts SSC Enthusiast Points: 141 More actions · Answer 6

I suppose its not entirely helpful to say that I recognize this error and that I know I've dealt with it in the past, but that I can't seem to remember exactly how I dealt with it.

I did spend a good amount of time going back and forth from SQL to 9i, and one thing I fell back on, if things just didn't seem to work, was using Access as an intermediate step. Which is embarassingly crude, yes, and not all that useful if you're looking at vast amounts of data, but I had more than a few situations where it got me past a typically opaque SQL error.

PBarry SSC Enthusiast Points: 187 More actions · Answer 7

You probably have found the answer (since it is a rather old post) but just in case, here are the Registry info needed for Oracle 9i:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]

"OracleXaLib"="oraclient9.dll"

"OracleSqlLib"="orasql9.dll"

"OracleOciLib"="oci.dll"

Also, don't forget to enable XA transactions on MSDTC and change security settings on the Oracle directories (ie Add "Read/Execute" for the account running MSDTC - usually "Network Service").

Patrick