asked
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.*
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
