Hi,
when updating a view pointing to a table on a linkerd server, an error occurs:
1) I set up a linked server [MyLinkedServer] in SQL-Server
2) I created a view in SQL-Server pointing to a table [MyTable] of the linked server:
create view MyView as
select ID, MyField from [MyLinkedServer]...[MyTable]
3) in Delphi XE10 I used MSConnection, MSQuery and MSDatasource to access [MyView]. with "select ID, MyField from MyView". Executing (select) works fine.
5) But when I try to make an update, MSQuery resonds with an error: "Table MyTable" not found.
6) Also MSQuery SQL-Generator seems to analyze MyQuery and says "Table MyTable not found"
Why is MSQuery analyzing the view and why is the component replacing [MyLinkedServer]...[MyTable] with [MyTable] which is wrong?
Updating MyView in SQL-Managent-console works fine!
Due to design I cannot manually edit the corresponding SQl-Strings of MSQuery (SQLUpdate, SQLSelete ...)
Thanks for help,
Dietrich
Access linked Server / tables results in wrong queries / SDAC 7.3.16
Re: Access linked Server / tables results in wrong queries / SDAC 7.3.16
To generate INSERT/UPDATE/DELETE SQL queries UniDAC uses meta-information about the name of the database object that owns each field. SQL Server returns only the name of the table that owns the field, and doesn't return the name of the view. To solve the problem, use the VIEW_METADATA option when creating the view. For example:
Code: Select all
create view MyView
WITH VIEW_METADATA
as
select ID, MyField from [MyLinkedServer]...[MyTable]
Re: Access linked Server / tables results in wrong queries / SDAC 7.3.16
Hi Azyk,
your solution works great!
Thanks a lot,
Cheers,
Dietrich
your solution works great!
Thanks a lot,
Cheers,
Dietrich