MSQuery on Views

[Aufhauser]

Is there a way, to force MSQuery to use the view in an update statement, not the underlying table?
My view has instead of triggers so it is important, to use the view and not the underlying table in update-statements. Using dbmonitor I see, that the statements of MSQuery always use the underlying table even if I set the UpdatingTable property.
I think using the underlying table is bad design because in most cases there is a good reason for using a view, not a table.
Thanks for help
stefan

[azyk]

Yes, there is a way to make TMSQuery use the view name instead of underlying table name when auto-generating an UPDATE SQL query. For this, it is required that the view is created using WITH VIEW_METADATA. For example:

Code: Select all

CREATE VIEW HumanResources.EmployeeHireDate
WITH VIEW_METADATA
AS
SELECT p.FirstName, p.LastName, e.HireDate
FROM HumanResources.Employee AS e JOIN Person.Person AS  p
ON e.BusinessEntityID = p.BusinessEntityID ;

GO

To generate an UPDATE SQL query, we use the meta-information about the database object, which possesses each field. If the view was created without using WITH VIEW_METADATA, then SQL Server returnes just the name of the table, which possesses the field, and doesn't return the view name. Therefore, in such cases, we are unable to use the view name when auto-generating SQL queries.

[Aufhauser]

Thank you for answer, changing the views works.
One question: SDAC don't use UpdatingTable property, why?

[azyk]

SDAC uses the UpdatingTable property. If in your case SDAC doesn't use this property correctly, please describe in details the steps to reproduce the case using the SDAC demo project. If the problem can't be reproduced on our demo, try to compose a small sample to demonstrate the problem, including a script to create test tables and send it to andreyz*devart*com .

Note: SDACDemo project is in the "Demos\SDACDemo\" folder relatively to the SDAC demo projects installation path.