Linked server insert error SQL server to Informix

HPUX 11.31 IDS 11.70.FC4 W2k8r2 SQL Server 2008 r2 CSDK 3.70 FC5DE One of our developers is getting an error on our SQL server that has a linked server connection setup to our Informix DB. The error is: OLE DB provider "Ifxoledbc" for linked server "CX" returned message "E42000: (-275) The Insert privilege is required for this operation. (gu_archived_ids)". Msg 7343, Level 16, State 2, Line 1 The OLE DB provider "Ifxoledbc" for linked server "CX" could not INSERT INTO table "[CX].[cars].[informix].[gu_archived_ids]". The data violated the integrity constraints for one or more columns. I've been trying to help and figure out why he is getting the error and can't find the cause. We have enabled the following in the Ifxoledbc provider properties: 'dynamic parameter', 'nested queries', 'allow inprocess', 'non transacted updates' and 'supports 'Like' operator'. Does anyone have a suggestion on how to resolve this? John Adamski Sr. Network Specialist Graceland University

In informix server, see the error message with finderr command: finderr 275 -275 The Insert privilege is required for this operation. The Insert access privilege on this table or column is not currently held by your account name, nor by the PUBLIC group, nor by your current role. The owner of the table or the DBA must grant the Insert privilege before you can insert a row into this table. This error is also returned when you attempt to insert a row into a view that is not updatable. A view is not updatable unless all of the following conditions are true: 1. All of the columns in the view are from a single table. 2. No columns in the projection list are aggregate values. 3. No UNIQUE or DISTINCT keyword is in the SELECT projection list. 4. No GROUP BY clause nor UNION operator is in the view definition. 5. The query selects no calculated values and no literal values. If only the first condition is false, and you hold the Insert and Select access privileges on all of the columns in the view, then you can define on the view an INSTEAD OF trigger whose triggered action inserts values into the base tables of the view, and use the trigger to perform the update. Just correct the issue, as suggested. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: adamski@graceland.edu > Subject: Linked server insert error SQL server to Informix [29036] > Date: Thu, 6 Dec 2012 10:24:36 -0500 > > HPUX 11.31 > IDS 11.70.FC4 > > W2k8r2 > SQL Server 2008 r2 > CSDK 3.70 FC5DE > > One of our developers is getting an error on our SQL server that has a linked > server connection setup to our Informix DB. The error is: > > OLE DB provider "Ifxoledbc" for linked server "CX" returned message "E42000: > (-275) The Insert privilege is required for this operation. > (gu_archived_ids)". > Msg 7343, Level 16, State 2, Line 1 > The OLE DB provider "Ifxoledbc" for linked server "CX" could not INSERT INTO > table "[CX].[cars].[informix].[gu_archived_ids]". The data violated the > integrity constraints for one or more columns. > > I've been trying to help and figure out why he is getting the error and can't > find the cause. We have enabled the following in the Ifxoledbc provider > properties: 'dynamic parameter', 'nested queries', 'allow inprocess', 'non > transacted updates' and 'supports 'Like' operator'. > > Does anyone have a suggestion on how to resolve this? > > John Adamski > Sr. Network Specialist > Graceland University > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

It seems we shot ourselves in the foot; the user the developer was using is a Read-Only user. I should have checked that before posting. Thanks for the help John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Alexandre Marini Sent: Thursday, December 06, 2012 10:03 AM To: ids@iiug.org Subject: RE: Linked server insert error SQL server to I.... [29037] In informix server, see the error message with finderr command: finderr 275 -275 The Insert privilege is required for this operation. The Insert access privilege on this table or column is not currently held by your account name, nor by the PUBLIC group, nor by your current role. The owner of the table or the DBA must grant the Insert privilege before you can insert a row into this table. This error is also returned when you attempt to insert a row into a view that is not updatable. A view is not updatable unless all of the following conditions are true: 1. All of the columns in the view are from a single table. 2. No columns in the projection list are aggregate values. 3. No UNIQUE or DISTINCT keyword is in the SELECT projection list. 4. No GROUP BY clause nor UNION operator is in the view definition. 5. The query selects no calculated values and no literal values. If only the first condition is false, and you hold the Insert and Select access privileges on all of the columns in the view, then you can define on the view an INSTEAD OF trigger whose triggered action inserts values into the base tables of the view, and use the trigger to perform the update. Just correct the issue, as suggested. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: adamski@graceland.edu > Subject: Linked server insert error SQL server to Informix [29036] > Date: Thu, 6 Dec 2012 10:24:36 -0500 > > HPUX 11.31 > IDS 11.70.FC4 > > W2k8r2 > SQL Server 2008 r2 > CSDK 3.70 FC5DE > > One of our developers is getting an error on our SQL server that has a linked > server connection setup to our Informix DB. The error is: > > OLE DB provider "Ifxoledbc" for linked server "CX" returned message "E42000: > (-275) The Insert privilege is required for this operation. > (gu_archived_ids)". > Msg 7343, Level 16, State 2, Line 1 > The OLE DB provider "Ifxoledbc" for linked server "CX" could not > INSERT INTO table "[CX].[cars].[informix].[gu_archived_ids]". The data> violated the integrity constraints for one or more columns. > > I've been trying to help and figure out why he is getting the error > and can't > find the cause. We have enabled the following in the Ifxoledbc > provider > properties: 'dynamic parameter', 'nested queries', 'allow inprocess', > 'non transacted updates' and 'supports 'Like' operator'. > > Does anyone have a suggestion on how to resolve this? > > John Adamski > Sr. Network Specialist > Graceland University > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.