sql jdbc: modification affects multiple base tables

[Former Member]

Hi

I have this error when using jdbc receiver with mode "UPDATE_INSERT" for view "EmployeeSalary" even I have allow all the max permission, include the dependent base table. Please advise.

Delivery of the message to the application using connection AFW failed, due to: Error processing request in sax parser: Error when executing statement for table/stored proc. 'EmployeeSalary' (structure 'test_row'): java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]View or function 'EmployeeSalary' is not updatable because the modification affects multiple base tables..

//Jack

[Former Member]

Hi

Thanks for all the update.

I found some guide on the restriction.

http://msdn2.microsoft.com/en-us/library/ms177523.aspx

//Jack

[Former Member]

is 'EmployeeSalary' a view?

if it is a view based on multiple tables,it wont allow you to change the data to avoid violation of integrety constraints.

Ideally , if you want to update the data from multiple tables, the XML that goes to JDBC adapter should take care of doing this , by updating data from different tables but not using a VIEW...

[Former Member]

Jack

Normally you cannot insert or update a multitable view if the view has no with check option clause or ina scenario where all columns being inserted or updated belong to the same base table.

This is a TSQL restriction.Check on the same..

[bhavesh_kantilal]

Jack,

can you go to a DB client and try to run the same UPDATE_INSERT query that you are attempting to run from XI using JDBC adapter and see if it executes fine.

I think the UPDATE_INSERT is not being allowed <b>not because of the configuration you have done is incorrect, but because the UPDATE_INSERT is violating some CONSTRAINT on the Database.</b>

Regards,

Bhavesh