I have a Linked Server I am attempting to access through vbscript, to perform a DELETE command. The current syntax for my query string is:
DBQUERY = "SELECT * FROM OPENQUERY(" & strLinkedServer & ",
'DELETE FROM pacts.acct_posting where posting_number = " & intPostingNumber & "');"
This gives a query string of:
SELECT * FROM OPENQUERY(PACTSTEST, 'DELETE FROM pacts.acct_posting
WHERE posting_number = 170026');
However this is returning the error:
Cannot process the object "DELETE FROM pacts.acct_posting where posting_number = 170026". The OLE DB provider "MSDASQL" for linked server "PACTSTEST" indicates that either the object has no columns or the current user does not have permissions on that object.
Permissions for accessing the Linked Server is not at issue.
A quick Googling of the error message reveals a comment from MS that "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.". They provide some work-arounds, but as I'm not an SQL expert I don't understand the advice they give.
This is at https://support.microsoft.com/en-us/kb/270119
Could somebody please advise me what I need to do given the advise at the linked page provided to make my query work?
