x

Cannot delete from table in DB2 using openquery

Hi all, Can someone please help???

I am trying to delete all data from table in DB2 using openquery in SP: EXEC(' DELETE FROM OPENQUERY(DMFRC1,'' SELECT * FROM USRMNT.UNIQ_AH_ACCT '') ')

Receiving the following error: Cannot execute the query " SELECT * FROM USRMNT.UNIQ_AH_ACCT

" against OLE DB provider "IBMDADB2.DB2COPY1" for linked server "DMFRC1". The provider could not support an interface required for the UPDATE/DELETE/INSERT statements.The provider indicates that conflicts occurred with other properties or requirements.The provider could not support deletion on this table.The provider indicates that conflicts occurred with other properties or requirements.
more ▼

asked Dec 27, 2010 at 09:36 AM in Default

FlexAZ gravatar image

FlexAZ
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You probably want to do this using a pass-through query against DB2 instead.

Have a look at EXECUTE AT ([http://msdn.microsoft.com/en-us/library/ms188332.aspx][1] ) which is a way to use EXECUTE with a DELETE-statement, passing the name of a linked server as a parameter.

Like this:

EXECUTE('DELETE FROM USRMNT.UNIQ_AH_ACCT') AT DMFRC1
[1]: http://msdn.microsoft.com/en-us/library/ms188332.aspx
more ▼

answered Dec 27, 2010 at 10:39 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x26
x19

asked: Dec 27, 2010 at 09:36 AM

Seen: 2224 times

Last Updated: Dec 27, 2010 at 09:36 AM