Writing to UniVerse Linked Server from SQL Server via ODBC

Question

I have an ODBC connection to UniVerse on AIX working well as a linked server on SQL Server to read data using OPENQUERY but have not been able to write back.

I can’t send a UniVerse SQL Native Update query via ODBC because OpenQuery expects returned data which I can’t provide in the same expression, as UniVerse ODBC only supports one command at a time.

I can issue a valid T-SQL Update command via the OPENQUERY syntax to either an ODBC or Native UniVerse Select but it won’t perform the update.

SQL Server is talking to UniVerse and UniVerse is happy with the syntax and permissions for updating. I can tell it is negotiating because when introducing an invalid column name or wrong data type, UniVerse will respond with the expected error messages.

The deal falls over on the last hurdle when they have agreed over the negotiation and the update is commanded.

OLE DB provider "MSDASQL" for linked server "servername" returned message "Insufficient base table information for updating or refreshing."

I think this means UniVerse ODBC is not telling the provider that my DICT item, ID (or @ID, Universe’s default key which I also tried) is the Primary key of the table.

I'm hoping someone has succeeded another way or has any suggestions, however wild. It is frustrating to be apparently so close to success but so far away.

Answer 1

For non-selects you can use the EXECUTE(...) AT SERVER syntax, something like:

EXECUTE('update table set somevalue = ''something else''') AT yourLinkedServer

You can read more info at Microsoft remote query documentation site