linked server t-sql

Question

linked server t-sql

Sarah Wiseman

SSC Enthusiast

Points: 108
More actions
June 20, 2006 at 10:30 am

#366480

linked server t-sql
linked server is wksa000 (sql express 2005) catalog wks inside sql 2000 standard
WORKS GREAT..and returns all the records as requested
select * from openquery( wksa000, 'select * from wks.dbo.wtbl_goodpartdata' )
DOES NOT WORK
delete from openquery( wksa000, 'delete from wks.dbo.wtbl_goodpartdata' )
ERROR AFTER TRYING TO DELETE
Server: Msg 7357, Level 16, State 2, Line 3
Could not process object 'delete from wks.dbo.wtbl_goodpartdata'.
The OLE DB provider 'SQLNCLI' 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='SQLNCLI', Query=delete from wks.dbo.wtbl_goodpartdata'].
why does the selet* work and the delete not???
ANYONE HAVE ANY IDEAS.. PLEASE LET ME KNOW .... THANK YOU IN ADVANCE !!!!

Viewing 5 posts - 1 through 4 (of 4 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

>>The OLE DB provider 'SQLNCLI' indicates that the object has no columns.

That's your clue. A DELETE does not return a resultset. OPENQUERY() expects a resultset. You can't use something that expects a resultset with SQL that does not return records.

Why not:

DELETE FROM [wksa000].[wks].[dbo].[wtbl_goodpartdata]

stax68 SSChampion Points: 11711 More actions · Answer 2

Because openquery (1) returns a recordset (2) that is disconnected from the underlying data source.

So you can't specify a delete statement inside OPENQUERY because that doesn't produce a recordset, and even if you used a select, you couldn't delete from it because it is not the same thing as the underlying table or a view on it, but a completely separate freestanding entity.

You would either have to use a linked server with 4-part name, or a remote stored procedure. If possible I would recommend the latter (subject to any unusual circumstances).

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Sarah Wiseman SSC Enthusiast Points: 108 More actions · Answer 3

Makes complete sence.. i understand... I have things working properly now... thanks for your help!

Kbrown SSCommitted Points: 1794 More actions · Answer 4

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: