Delete using a linked server

Hi,

I am trying to delete records in a file on the AS400 using a linked server
and I am getting the following error message:

Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"catalog"."schema"."table"'. There was a recoverable, provider-specific error, such as an RPC failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

The linked server is configured as follows:
"server type": other data source
"provider name": Microsoft OLE DB provider for ODBC drivers
"data source" references a system DSN name that uses the driver
"Client Access ODBC driver(32 bit)"
linked server options: "Data access" and "Use remote collation" boxes checked
provider options: "Dynamic parameters", "Nested queries", "Allow in process",
"Non transacted updates" boxes checked

The file on the AS400 is journaled and has a unique key.

The Select and Insert statements work like a charm but I can not for the life of me get the Delete or Update statements to work.

I have been struggling with this problem for over a month, so any help whatsoever would be tremendously appreciated.

Thanks!On the properties page for the linked server, are the RPC and RPC OUT check boxes checked?

That said, I never tried manipulating data on a linked server; only selects. Also, there is a different driver for AS/400s, I' racking my brain for the name, but it's not provided by IBM.

Regards,

hmscott|||Thanks for the reply.

I tried checking the RPC and RPC out boxes and I get the same error message.

As for the other driver, you're probably thinking of OLE DB provider for DB2.
I've have also tried using this driver without success.

My hunch is that there is an ODBC provider property(such as DBPROPSET_PROVIDERROWSET) that's not configured to handle updates or deletes, but I don't know how to display this information.

Are you familiar with OLE DB providers for ODBC or can you direct me to a
resource?|||Can you post the delete and update statements.|||Here's the statement I'm using in query analyzer.

DELETE FROM AS400LINKEDSERVER.SYSTEMNAME.LIBRARYNAME.FILENAME

The four part names have been changed here for privacy.

I have also tried OPENQUERY. Again, same error message.

Thanks.

DELETE transaction with SNAPSHOT isolation level - conflicts another table

Hi,

we are executing the following query in a stored procedure using snapshot isolation level:

DELETE FROM tBackgroundProcessProgressReport
FROM tBackgroundProcessProgressReport LEFT OUTER JOIN
tBackgroundProcess ON
tBackgroundProcess.BackgroundProcessProgressReportID =tBackgroundProcessProgressReport.BackgroundProcessProgressReportID LEFTOUTER JOIN
tBackgroundProcessProgressReportItem ON
tBackgroundProcessProgressReport.BackgroundProcessProgressReportID =tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportID
WHERE (tBackgroundProcess.BackgroundProcessID IS NULL) AND
(tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportItemID IS NULL)

The query should delete records from tBackgroundProcessProgressReport which are not connected with the other two tables.
However, for some reasone we get the following exception:

System.Data.SqlClient.SqlException:Snapshot isolation transaction aborted due to update conflict. Youcannot use snapshot isolation to access table 'dbo.tBackgroundProcess'directly or indirectly in database 'RHSS_PRD_PT_Engine' to update,delete, or insert the row that has been modified or deleted by anothertransaction. Retry the transaction or change the isolation level forthe update/delete statement.

The exception specifies that we arenot allowed to update/delete/insert records in tBackgroundProcess, butthe query indeed deletes records from tBackgroundProcessProgressReport,not from the table in the exception.
Is the exception raised because of the join?

Has someone encountered this issue before?

Thanks,

Yani

Hi,

it looks like this forum is not the best place to ask, since it's dedicated to asp.net

So anybody with idea where i could ask for a solution for my problem?

Thanks in advance!

delete rows by checking conditions in more than one column

Hi All,

I have tables in three hierarchy...
I need to delete rows from the table which in 3rd level of hierarchy.

For ex: Table 1 has primary key col1.
Table 2 has primary key Col2 and Foreign key COL1 and
Table 3 has primary key Col3 and Foregn key Col2.

Now I have to delete all the rows in table three where Table3.Col2=Table2.Col2
and Table2.Col1=Table1.Col1

I could insert successfully using this condition but I am unable to delete.
I tried nested queries but it fails as the sub query returns more than one row.

Please help me how to overcome this.
An example is more helpful.

Thank you
Madhavi

Quote:

Originally Posted by madhavi123

Hi All,

I have tables in three hierarchy...
I need to delete rows from the table which in 3rd level of hierarchy.

For ex: Table 1 has primary key col1.
Table 2 has primary key Col2 and Foreign key COL1 and
Table 3 has primary key Col3 and Foregn key Col2.

Now I have to delete all the rows in table three where Table3.Col2=Table2.Col2
and Table2.Col1=Table1.Col1

I could insert successfully using this condition but I am unable to delete.
I tried nested queries but it fails as the sub query returns more than one row.

Please help me how to overcome this.
An example is more helpful.

Thank you
Madhavi

Please don't post questions in the articles section.
Moved to the forum.