I am trying to insert a basic text string into a table field using a linked server connection from SQL to a progress 10.2C database. Until now, I have not had any issues.... but I have never had to insert data into a field that is more than one column.
So, the table that I am referencing is a NOTES table, and the field i can't seem to update is CHAR[16] x(60).
In my SQL statement, I can't seem to find a way to select just the 1st row of that field like I would normally do in ABL (Meaning I would normally just display notes.noteln[1]).
Its a simple statement, but no matter what I do I am unable to update / insert into that one specific field....
INSERT OPENQUERY ([LINKED_SERVER], 'SELECT PUB.notes.cono, PUB.notes.notestype, PUB.notes.primarykey,PUB.notes.printfl, PUB.notes.transdt, PUB.notes.transtm, PUB.notes.operinit, PUB.notes.requirefl, PUB.notes.noteln, PUB.notes.pageno FROM PUB.notes') VALUES (1,'p','PRODUCT',0,'09/13/17','1202','xxx',0,'testing',5);
Here is the returned error:
OLE DB provider "MSDASQL" for linked server "[LINKED_SERVER]" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "[LINKED_SERVER]" could not INSERT INTO table "[MSDASQL]" because of column "noteln". Could not convert the data value due to reasons other than sign mismatch or overflow.
I have also tried setting the datatype to a string and limiting the amount of characters...
INSERT OPENQUERY ([LINKED_SERVER], 'SELECT PUB.notes.cono, PUB.notes.notestype, PUB.notes.primarykey,PUB.notes.printfl, PUB.notes.transdt, PUB.notes.transtm, PUB.notes.operinit, PUB.notes.requirefl, substring(PUB.notes.noteln,1,20), PUB.notes.pageno FROM PUB.notes') VALUES (1,'p','PRODUCT',0,'09/13/17','1202','xxx',0,substring('testing',1,20),5);
And I get this error...
OLE DB provider "MSDASQL" for linked server "LINKED_SERVER" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "LINKED_SERVER" could not INSERT INTO table "[MSDASQL]" because of column "substring(noteln,1,20)". The user did not have permission to write to the column.
Has anyone had a similar issue? Any suggestions?