I currently am using a Linked Server connection in Sql Server 2008 R2 to a Progress database using 10.2B ODBC drivers from Progress. I need to update a table, but the table name contains a hyphen and the fields all contain hyphens.
I see this very similar situation, but there is no answer which helps me, or I simply don't understand the first response. I am not a Progress guy, so if there are Progress settings, I would need help going through those.
I am trying to do the following:
UPDATE OPENQUERY(TESTCON, 'select * from CONTACT.PUB."tbl-mast" where "mast-id" = ''A12''') set "col-name" = 'tom'
This generates the error:
OLE DB provider "MSDASQL" for linked server "TESTCON" 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 2, Line 3
The OLE DB provider "MSDASQL" for linked server "TESTCON" could not UPDATE table "[MSDASQL]" because of column "col-name". The user did not have permission to write to the column.
I have tried this workaround suggested by progress and get a different error:
UPDATE contact_vwTblmast set col_name = 'tom' where mast_id = 'A12';
But, I end up with a different error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "MSDASQL" for linked server "testcon" reported an error. The provider did not give any information about the error.
Msg 7306, Level 16, State 2, Line 2
Cannot open the table ""contact"."PUB"."tbl-mast"" from OLE DB provider "MSDASQL" for linked server "testcon". Unknown provider error.
I am successfully able to query the database. The following will work:
SELECT * FROM OPENQUERY(TESTCON, 'select * from CONTACT.PUB."tbl-mast" where "mast-id" = ''A12''')
All the above work perfectly fine if the table does not include hyphens. Both methods, OPENQUERY or using the view properly updates the data. I just can't get it to work if there are hyphens in the table name. Are there progress settings or something else that needs done to get this to work?
The solution was to use a batch file, sqlexp.bat. There is a difference between Progress SQL Views and SQL-92 Views created in a progress database. Views must be created using the Progress SQL Explorer (sqlexp.bat).
For me, it was C:\Progress\OpenEdge\bin\sqlexp.bat and the command line parameters are connectivity settings. After you do that, a java command line screen will then pop up. That is where you specify the CREATE VIEW statement.
CREATE VIEW ODBC.contact_vwTblmast (...) AS SELECT ... FROM PUB."tbl-mast"
Note, there is a restriction on the 'owner', you can use in creating these views. You cannot specify an owner of PUB.