Could not start a transaction in OLE DB provider

[mooki]

Im copying sql table into Oracle table using Linked server.
(Insert into LINKEDSERVER..USER.TABLE
select * from SQLTABLE)


If im not setting the XACT_ABORT to ON and put "begin transaction" then I cannot do the insert, getting the "error 7392: Could not start a transaction...."

The problem is that im Doing that insert from VB code using ADO and MTS, so i cannot set this parameter.

WHAT CAN I DO ?????

 

[TomSark]

Hi,

You can set xact_abort on, just send this string to the server, then execute your insert afterwards:

ADO Command Execution #1:
cnn.Execute "SET XACT_ABORT ON",,adCmdText + adExecuteNoRecords

ADO Command Execution #2:
cnn.BeginTrans
cmd.Execute "Insert yadda yadda yadda...",",,adCmdText + adExecuteNoRecords
cnn.CommitTrans

Have you tried this?

Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[jonscott8]

Could not start a transaction

Not all OLE DB providers support transaction processing. I have never worked with Oracle, so maybe someone else will know if it does or you may check the MSDN or with Oracle.

i cannot set this parameter.

oConnection.Execute("SET XACT_ABORT ON&quot Jon Hawkins
jonscott8@yahoo.com

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[mooki]

Thanx you all, I Was able to set the XACT_ABORT from the VB code just as you said.
Thanx.