INSERT INTO statement throwing permission errors for a speci

Posted by mbougie on 13-Sep-2017 10:41

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?

All Replies

This thread is closed