CODEWITHSUNDEEP
odbcopenquery4d-database
David Chase
David Chase

Reputation: 31

How can I update a 4D record from SQL Server, over ODBC?

I've got a 4D database running in 4D Server, with 4D Sql Server running. On another Windows machine, I've got an an ODBC data source set up for it. On that 2nd machine, in SQL Server Management Studio, I've created a Linked Server using that ODBC data source. I can successfully query 4D records via that Linked Server, using OPENQUERY, but I can't UPDATE or DELETE those same 4D records.

This outputs the expected 4D data:

SELECT * FROM OPENQUERY([4DTest2], 'SELECT UserID, FirstName, LastName FROM [Users] WHERE UserID = 23990')

This gets an error:

UPDATE OPENQUERY([4DTest2], 'SELECT UserID, FirstName, LastName FROM [Users] WHERE UserID = 23990') SET FirstName = 'Doug'

The error is:

OLE DB provider "MSDASQL" for linked server "4DTest2" returned message "Data provider or other service returned an E_FAIL status.". Msg 7343, Level 16, State 4, Line 2 The OLE DB provider "MSDASQL" for linked server "4DTest2" could not UPDATE table "[MSDASQL]".

Upvotes: 1

Views: 573

Answers (1)

Umberto Migliore
Umberto Migliore

Reputation: 327

I resolved a similar problem with MySQL using this tip. The trick was to uncheck the ODBC option "Force Forward Only Cursor"

Source: issue with INSERT into linked server

Upvotes: 2

Related Questions