Error updating AS400 table

Last Post 20 Oct 2004 04:55 AM by Kendo_SQL. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Kendo_SQL
New Member
New Member

--
20 Oct 2004 02:56 AM
SQL2000 SP3a

I have a statement using 'INSERT OPENQUERY(LS_linked_server, SELECT .etc...' which attempts to insert data into an AS400 table using a linked server. I get the following error:

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

At start of SP I put SET XACT_ABORT ON and I have BEGIN DISTRIBUTED TRAN before the statement. Journaling is set on on the AS400 tables. As far as I can make out, the provider supports distributed transactions - question is how?

Any help much appreciated.
Kendo_SQL
New Member
New Member

--
20 Oct 2004 04:55 AM
Commit Immediate (*NONE)

Have tried 'Read Uncommitted (*CHG)' but no joy.
Kendo_SQL
New Member
New Member

--
20 Oct 2004 06:32 AM
I have set up a single field table to simplify my SQL statement:

SET XACT_ABORT ON
GO
BEGIN DISTRIBUTED TRAN
INSERT OPENQUERY(LS_HMVCASW1,
'SELECT
FIELD
FROM
WARDOUR1.PCUPDDTA.KENNY
WHERE 1=0') --ADD WHERE 1=0 TO STOP QUERY RETURNING ANY VALUES
SELECT
'X'

COMMIT TRAN
SET XACT_ABORT OFF
GO


I run it in QA and get the following error now:

OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: The provider did not give any information about the error.]. [


I'm at the end of my tether!
Kendo_SQL
New Member
New Member

--
20 Oct 2004 08:44 AM
After applying a Client Access SP, I've got it working .... sometimes. If I remove the BEGIN TRAN and COMMIT TRAN it works. Leave those in and I get :

The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].


But if I stop and restart SQL, the TRAN stuff then works.. but only once, which is bizarre. If I run it a second time it errors again. The field I'm updating doesn't need to be unique.

Any ideas?

Thanks


Acceptable Use Policy
---