SQL Linked Server Update Openquery syntax error

Question

I am trying to update data in a PervasiveSQL backend (Sage ERP system) from SQL Server 2008 R2 via a Linked Server setup. Below are details and error msg returned... The kicker is that the update statement works on a development box just fine, very similar setup. Any help will be GREATLY appreciated!

Environment:

Windows Server 2008 Enterprise (Cloud server, Rackspace)
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) Express Edition with Advanced Services on Windows NT 6.1 (Build 7601: Service Pack 1) (WOW64) (Hypervisor)
Sage Timberline ERP running with Pervasive SQL v.10 backend

Setup:
We have a LINKED SERVER setup in SQL Server 2008, using a 32-bit Timberline Driver (OBDC) Linked server is using a MSDASQL provider with the following two properties set to TRUE, “Allow inprocess” and “Non Transacted Updates”

Issue:
We can successfully pull data, see query below, from linked server. We can successfully browse objects via the linked server in SSMS.

SELECT * 
FROM OPENQUERY (TLLINKSERVER, 'SELECT * FROM TABLE1 where JOBID = ''00-00-111111''')

However the UPDATE statement below returns the following error.

UPDATE OPENQUERY(TLLINKSERVER, 'SELECT * FROM TABLE1 WHERE JOBID = ''00-00-111111''')
SET DATEFIELD = '2013-07-15'

Error:

OLE DB provider "MSDASQL" for linked server "TLLINKSERVER" returned message "[Sage Timberline Office][Sage Timberline Office ODBC Driver]Syntax Error.". OLE DB provider "MSDASQL" for linked server "TLLINKSERVER" returned message "[Sage Timberline Office][Sage Timberline Office ODBC Driver]UPDATE "\SERVER1\Company Data\DATA\COMPANY1\"<< ??? >>."TABLE1" SET "DATEFIELD1"=? WHERE "JOBID"=? AND "DATEFIELD1"=?".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "TLLINKSERVER" could not UPDATE table "[MSDASQL]".

"Very similar" setup - could you enumerate how they're not identical? — Aaron Bertrand, Jul 21 '13 at 19:29

score 1 · Answer 1 · answered Jul 21 '13 at 19:38

Why not make things easier? Does PervasiveSQL not support direct updates via a linked server?

UPDATE TLLINKSERVER...TABLE1 
  SET DATEFIELD = '2013-07-15'
  WHERE JOBID = '00-00-111111';

Or maybe:

SELECT * FROM OPENQUERY(TLLINKSERVER, 'UPDATE TABLE1
  SET DATEFIELD = ''2013-07-15''
  WHERE JOBID = ''00-00-111111'');

While the UPDATE OPENQUERY syntax you're using is supported for SQL Server linked servers, I'm not sure that's true for other platforms such as PervasiveSQL...

score 1 · Answer 2 · edited Feb 05 '14 at 21:07

This is how I update table sin Oracle from SSMS.

The Linked server name in this case is MyLinkedServer.

I am updating the column CONTACT_EMAIL to MyEmail@MyDomain.COM where the STATE is one of the following (KY,OH,NY,PA,VA,DC):

UPDATE L 
SET L.CONTACT_EMAIL ='MyEmail@MyDomain.COM'
FROM OPENQUERY(MyLinkedServer, 'SELECT * from MyTable')L
WHERE L.STATE IN('KY','OH', 'NY','PA','VA','DC')

OPENQUERY method works perfectly.

SQL Linked Server Update Openquery syntax error

2 Answers2