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
– Aaron Bertrand, Commented Jul 21, 2013 at 19:29

Aaron Bertrand · Accepted Answer · 2013-07-21 19:38:23Z

1

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

answered Jul 21, 2013 at 19:38

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Code Maverick · Accepted Answer · 2014-02-05 21:07:00Z

1

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 [email protected] where the STATE is one of the following (KY,OH,NY,PA,VA,DC):

UPDATE L 
SET L.CONTACT_EMAIL ='[email protected]'
FROM OPENQUERY(MyLinkedServer, 'SELECT * from MyTable')L
WHERE L.STATE IN('KY','OH', 'NY','PA','VA','DC')

OPENQUERY method works perfectly.

edited Feb 5, 2014 at 21:07

Code Maverick

20.5k12 gold badges65 silver badges117 bronze badges

answered Feb 5, 2014 at 20:45

Oscar O

111 bronze badge

Collectives™ on Stack Overflow

SQL Linked Server Update Openquery syntax error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related