Error updating AS400 table

SQL2000 SP3a
I have a statement as follows which attempts to insert data into an AS400
table using a linked server.
[b]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[/b]
I get the following error:
[b]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.].[/b]
Journaling is set on on the AS400 tables. As far as I can make out, the
provider supports distributed transactions - question is how? The user in
the LS has correct permissions on AS400.
Any help much appreciated.The error does imply Access Denied. Use your AS400 query interface, log on
as the user this job is running under, and manually test the SQL
Jeff
"Kendo Nagasaki" <Kendo Nagasaki@.discussions.microsoft.com> wrote in message
news:034E693C-0530-4719-B771-A472C9E83D9B@.microsoft.com...
> SQL2000 SP3a
> I have a statement as follows which attempts to insert data into an AS400
> table using a linked server.
> [b]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[/b]
> I get the following error:
> [b]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.].[/b]
>
> Journaling is set on on the AS400 tables. As far as I can make out, the
> provider supports distributed transactions - question is how? The user in
> the LS has correct permissions on AS400.
> Any help much appreciated.|||I cannot log into the green screen using this profile as it has been set up
to boot you off immediately you log on, but I can link to the table from
Access using the same system DSN and user profile, and I can add/edit data
through that, so that to me means permissions are ok.
"Jeff Dillon" wrote:
> The error does imply Access Denied. Use your AS400 query interface, log on
> as the user this job is running under, and manually test the SQL
> Jeff
> "Kendo Nagasaki" <Kendo Nagasaki@.discussions.microsoft.com> wrote in message
> news:034E693C-0530-4719-B771-A472C9E83D9B@.microsoft.com...
> > SQL2000 SP3a
> >
> > I have a statement as follows which attempts to insert data into an AS400
> > table using a linked server.
> >
> > [b]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[/b]
> >
> > I get the following error:
> >
> > [b]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.].[/b]
> >
> >
> > Journaling is set on on the AS400 tables. As far as I can make out, the
> > provider supports distributed transactions - question is how? The user in
> > the LS has correct permissions on AS400.
> >
> > Any help much appreciated.
>
>|||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 :
[b]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].
[/b]
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?
"Jeff Dillon" wrote:
> The error does imply Access Denied. Use your AS400 query interface, log on
> as the user this job is running under, and manually test the SQL
> Jeff
> "Kendo Nagasaki" <Kendo Nagasaki@.discussions.microsoft.com> wrote in message
> news:034E693C-0530-4719-B771-A472C9E83D9B@.microsoft.com...
> > SQL2000 SP3a
> >
> > I have a statement as follows which attempts to insert data into an AS400
> > table using a linked server.
> >
> > [b]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[/b]
> >
> > I get the following error:
> >
> > [b]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.].[/b]
> >
> >
> > Journaling is set on on the AS400 tables. As far as I can make out, the
> > provider supports distributed transactions - question is how? The user in
> > the LS has correct permissions on AS400.
> >
> > Any help much appreciated.
>
>