November 3, 2004 at 5:49 pm
I am having a problem with a linked server to DB2. The connection is set up
using the Microsoft OLE DB for ODBC connection. I do not have a problem
selecting data from the DB2 tables from SQL Server, but I cannot update without receiving an SQL
error. Here is the update statement I am attempting to execute:
update openquery(dsntqa, 'select fiscal_day from dsntqa.da_flsh_sales_mstr') set
main_mod = 25
Here is the message I am getting:
Server: Msg 7344, Level 16, State 2, Line 1
OLE DB provider 'MSDASQL' could not UPDATE table '[MSDASQL]' because of column
'main_mod'. The user did not have permission to write to the column.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned
0x80040e21: Data status sent to the provider: [COLUMN_NAME=FISCAL_DAY
STATUS=DBSTATUS_S_OK]. Data status returned from the provider:
[COLUMN_NAME=main_mod STATUS=DBSTATUS_E_PERMISSIONDENIED]].
The DB2 rights are correct. I can update the table when I use DB2 connect
through the command line from the server to the host. I only encounter the
error when accessing through the linked server OLE/DB provider MSDASQL.
November 3, 2004 at 7:29 pm
You say that the "DB2 rights are correct" - I assume from this that they are correct for your windows account (i.e. you logon to the server as yourself and then do the update).
When you use the "OPENQUERY" from SQL, it will be SQL Server that is doing the update on the DB2 database. Check that the windows account that SQL Server is running as has the correct rights on the DB2 database.
You may need to check the query you are using as well. SQL will only see the columns that are listed in the select statement. i.e. it will only see fiscal_day) You are updating column main_mod but this is not in the Select statement that is part of the OPENQUERY
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply