Delete from a Linked Server

Question

Post reply

Delete from a Linked Server

Fred Pannebaker

SSC Veteran

Points: 239
More actions
August 11, 2004 at 11:13 am

#109371

I am have extreme problems getting a delete command to work with an OLE DB linked server within MS SQL 2000 Service Pack 3.
I can get selects to work with syntax as follows:
select * from openquery(LINKEDSERVER, 'select * from schema.table')
However, I cannot get a delete command to work with either of these syntaxes:
select * from openquery(LINKEDSERVER, 'delete * from schema.table')
/*
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'delete from SYSUSER.test_tccom001'. The OLE DB provider 'seeMoreProvider' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='seeMoreProvider', Query=delete from SYSUSER.test_tccom001'].
*/
or
delete from openquery(LINKEDSERVER, 'select * from schema.table')
/*
Server: Msg 7390, Level 16, State 1, Line 1
The requested operation could not be performed because the OLE DB provider 'seeMoreProvider' does not support the required transaction interface.
OLE DB error trace [OLE/DB Provider 'seeMoreProvider' IUnknown::QueryInterface returned 0x80004002].
*/
or
delete from openquery(LINKEDSERVER, 'delete * from schema.table')
/*
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'delete from SYSUSER.test_tccom001'. The OLE DB provider 'seeMoreProvider' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='seeMoreProvider', Query=delete from SYSUSER.test_tccom001'].
*/
The errors received are also listed. Can someone provide the proper syntax for this type of operation?
Thanks, Fred Pannebaker

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Peter Tillotson Hall of Fame Points: 3325 More actions · Answer 1

You can use the four part name for a linked server:

DELETE FROM linkedserver.databasename.schema.table where .....

Good luck

Peter

Kbrown SSCommitted Points: 1794 More actions · Answer 2

Proper syntax is:

delete openquery (LINKEDSERVER,'select cols from table where 1=1)

This article explains the original posters error message and proper syntax:

http://support.microsoft.com/kb/270119

OpenQuery requires a result set to be returned, but UPDATE, DELETE, and INSERT statements that are used with OpenQuery do not return a result set.

WORKAROUND

You can work around this problem in the following ways:

1.	Use four-part names (linked_server_name.catalog.schema.object_name) to do insert, update, or delete operations.
2.	As documented in SQL Server Books Online, reference the OpenQuery function as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. The following queries demonstrate proper usage with the SQL Server OLE DB provider: