Hello,
I am having an issue I hope somebody can help with. We have an older Progress system in place and we wanted to hookup ODBC drivers to communicate with the database from a .NET application we are writing. The drivers work fine for doing SELECT. The drivers work fine when we want to do an UPDATE on a table which is alphanumerically named. But then we have the tables which have hyphens in the name. We are using the ODBC 10.2B ODBC drivers, yet we get errors when we try to update a table with a hyphen saying I don't have permission to write to the table.
Any ideas?
NOTE: This is an issue with the table name, not the columns containing hyphens.
I have a stackoverflow if you wanted to answer there (there is more information on the error too).
https://stackoverflow.com/…/unable-to-update-tabl…/28071599…
When I execute this:
UPDATE OPENQUERY(TESTCON, 'select * from CONTACT.PUB."tbl-mast" where "mast-id" = ''A12''') set "col-name" = 'tom'
I end up getting this 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.Much Appreciated,
Tom Loya
Sounds like this bug.... knowledgebase.progress.com/.../P138294
There is a workaround in the KB, which is basically creating a simple view for the base table without the "-" in the column names.
Thanks for the answer, but that is not the same thing. The issue I am seeing is that the table name contains the hyphen. I have been able to use the workaround you posted for the tables which don't have a hyphen in their table name but contain columns which contain hyphens.
Same thing... create the view name without the "-" in the table name just like you would for a column.
So tblmast or tbl_mast instead of tbl-mast.
Appreciated, but I tried that and it didn't work. It works great for the tables which do not contain a hyphen in the table name. So, I know my formatting is correct because I got it to work with the other tables... just not those containing a hyphen in the table name.
I will try again tho, you never know if something was messed up along the way. Trying now.
Ok, so I tried and I get a different error. This error does not show up for the tables which do not contain hyphens.
OLE DB provider "MSDASQL" for linked server "testcon" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Cannot open file 'C:\Users\SQL200~1\AppData\Local\Temp\'.".
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".
From the KB:
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;
Thanks. I think I discovered the err of our ways. Before we updated the drivers, we couldn't see any user created views. When we saw this article, we installed the views on Sql Server and it actually worked for the tables which did not contain a hyphen. Then we updated the drivers. We still have the same issues.
So, I had the progress developer add the views again thinking that the updated drivers will allow us to view the drivers. Perhaps the issue resides with how the progress guy is creating the driver and with what user is creating it. Thanks for your help, I'll let you know what happens when he gets the view installed. I have a feeling it will be a different issue trying to get those to be visible (maybe), but at least we are hopeful.
Are you creating the views under PUB?
It would help to know the version of the OpenEdge db that you are
trying to update from MS sql server.
If error persist, you might want to configure the ODBC driver to
create log files, so you could see the exact UPDATE statement
created by MS and the exact response from OE.
hope this helps a bit, .....steve pittman [OE
sql software architect]
Reply by bunggoThanks. I think I discovered the err of our ways. Before we updated the drivers, we couldn't see any user created views. When we saw this article, we installed the views on Sql Server and it actually worked for the tables which did not contain a hyphen. Then we updated the drivers. We still have the same issues.
So, I had the progress developer add the views again thinking that the updated drivers will allow us to view the drivers. Perhaps the issue resides with how the progress guy is creating the driver and with what user is creating it. Thanks for your help, I'll let you know what happens when he gets the view installed. I have a feeling it will be a different issue trying to get those to be visible (maybe), but at least we are hopeful.
Stop receiving emails on this subject.Flag this post as spam/abuse.
the original error was "The user did not have permission to write to the column. Have you verified that you have write privilege on the column?
I assume there may be something wrong what you can not see (in ODBC, in .NET...).
May be there is something with codepade in the game.
I did statements like this in the past for views and selects - So using " works :)
CREATE VIEW dp_OMCC
AS
SELECT
Ord."Send-ID" order_detail_key...
I suggest to use the OpenEdge _SQLExplorer_ to have the most "native" connection. Do there a select with 1 field from 1 table. That must work.
I not, export one table structure from a small table and I can import and try.
And when your select fails in SQLExplorer, post your select, then I can try.
Klaus