Unable to begin a distributed transaction.

Question

Post reply

Unable to begin a distributed transaction.

Chris Kitchen

Hall of Fame

Points: 3090
More actions
November 14, 2006 at 3:38 am

#115058

Hi,
I have a table called [tbl_ExecuteInsert] in SQL Server 2000 that contains two rows:

Row#1:
insert into [MyServerName1].[MyDatabaseName1].dbo.[MyTableName1] select [Col1], [Col2] from [MyServerName2].[MyDatabaseName2].dbo.[MyTableName2]

Row#2:
set identity_insert [MyDatabaseName1].dbo.[MyTableName1] on insert into [MyServerName1].[MyDatabaseName1].dbo.[MyTableName1] select [Col1], [Col2] from [MyServerName2].[MyDatabaseName2].dbo.[MyTableName2]

I then have a basic cursor that loops through the table [tbl_ExecuteInsert] and executes each row (using sp_executesql). When I run the cursor I get the following error message:
(0 rows affected)
Msg 7391, Level 16, State 1, Server MyServerName1, Line 2
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].

However, if I print out the rows in the cursor (rather than executing them) and copy the printed out insert statements into a new query analyzer window and execute the statement, it works fine.
All triggers on both tables are disabled and the MS DTC is running.
I've scanned the net and read through quite a few articles but nothing seems to work, so any advise would be greatly appreciated.

Thanks in advance.

www.sqlAssociates.co.uk

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

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

Couple of things.

1. Could you post all of your code so we can see where the BEGIN TRANSACTION, etc statements are?

2. What if you have just a single row in your table so only a single statement is executed? Do you get an error?

3. What if you have a couple of statements that just do a select rather than an insert? Do you get an error?

I once had a similar problem when trying to retrieve two separate sets of data from an Oracle linked server (linking them in a single query via a JOIN in SQL Server - long story). The only way I could make it work was to

create a temp table to store the results

start one transaction, read some data, end transaction

start another transaction, read the next bit of data, end transaction

start transaction, do what I originally intended, end transaction

I guess the idea is to try to work out which of the many pieces of your code are causing the issue...

Jo Pattyn SSC-Dedicated Points: 32344 More actions · Answer 2

Jo Pattyn

SSC-Dedicated

Points: 32344

November 14, 2006 at 5:25 am

#671573

Both tables have a primary key?

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 3

Hi,

Thanks for getting back to me, to answer your questions ....

1. Could you post all of your code so we can see where the BEGIN TRANSACTION, etc statements are?

I have tried using BEGIN TRANSACTION, BEGIN DESTRIBUTED TRANSACTION, and also just the straight forward insert statement and they all fail with the same message.

2. What if you have just a single row in your table so only a single statement is executed? Do you get an error?

The table will always contain records, there would never be an occassion where the table would be empty.

3. What if you have a couple of statements that just do a select rather than an insert? Do you get an error?

I can perform the "select" part of the statement, it is just when I add the insert part that the error message is returned.

I've just been doing somemore testing and if I use:

"select top 1 [Col1] from [tbl_ExecuteInsert]"

rather than

"select [Col1] from [tbl_ExecuteInsert]"

in the cursor, the cursor executes successfully. So I tried writing a custom loop that would just select the top 1 from the table, execute the statement, and then remove this row, and then select the next top 1 from the table, but this also failed. I've even tried writting out [Col1] from [tbl_ExecuteInsert] to a text file and then using OSQL in a batch file to execute the insert statements but I get the same error message.

To answer the other question, the tables may or may not have PKs.

Thanks again for your help on this, its much appreciated.

www.sqlAssociates.co.uk

Jo Pattyn SSC-Dedicated Points: 32344 More actions · Answer 4

The tables must have some unique index/primary key, which will act as row-identifier, to perform a distributed transaction (like inserting in another server)

If one of the servers runs on Windows 2003, you may need to tweak MS DTC behaviour, see http://support.microsoft.com/kb/839279

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 5

Hi,

I have just created a Primary key on the table where the data is being selected from, and also the table where the data is being inserted into, thus creating a row-identifier on each table, and it still returns the same error message:

Msg 7391, Level 16, State 1, Server MyServerName1, Line 2

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.

www.sqlAssociates.co.uk

Jo Pattyn SSC-Dedicated Points: 32344 More actions · Answer 6

Have you checked http://support.microsoft.com/kb/839279?