Kbase P138294: MS SQL Server linked server returns permission denied error when inserting and updating OpenEdge 10
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/08/2009 |
|
Status: Verified
SYMPTOM(s):
MS SQL Server linked server returns permission denied error when inserting and updating OpenEdge 10 Database fields that have dash in its name
The following errors are displayed on the MS SQL Server 2005 when ran from SQL query analyzer tool:
OLE DB provider "MSDASQL" for linked server "<linked server name>" 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 name>" could not INSERT INTO table "[MSDASQL]" because of column "fld-a". The user did not have permission to write to the column.
The same above errors are apparent when running the data transfer process from MS SQL Server DTS tool
FACT(s) (Environment):
The SELECT and DELETE linked server queries runs successfully
The user is used on the connection is a default DBA - creator of the OpenEdge database
The same MS SQL Server linked server query work when used Progress 9.1x Database
The log file, resulted from running the "SET PRO_SERVER LOG ON;" on sqlexp, does not show the insert or update query parsed or executed on the server side when field name has dash.
The SELECT/INSERT/UPDATE/DELETE queries runs fine from winsql tool regardless of the dashes on the fields name using the same user used on the MS SQL Server linked server
OpenEdge 10.x
OpenEdge 10.1x
OpenEdge 10.2A
OpenEdge SQL 92
MS SQL Server
CAUSE:
Bug# OE00178437
FIX:
The workaround is to create view with table and fields name without the dashes on the Progress/OE database and refresh the linked server created on the MS SQL Server and use insert or update again.
For instance:
CREATE VIEW vmyTable (fldA, fldB) AS
SELECT "fld-a", "fld-b"
FROM PUB."my-table"
WITH CHECK OPTION;
COMMIT;
The view will be created on the user schema. Thus, when you use select/ insert/update/delete records using view, you need to qualify the view with the user who created the view.
For instance, if the above view is created by user alpha the select statement should contain alpha.vmyTable.
SELECT * FROM alpha.vmyTable;