Access linked Server / tables results in wrong queries / SDAC 7.3.16

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
witzl
Posts: 14
Joined: Fri 21 Apr 2017 10:01

Access linked Server / tables results in wrong queries / SDAC 7.3.16

Post by witzl » Fri 21 Apr 2017 10:39

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

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Access linked Server / tables results in wrong queries / SDAC 7.3.16

Post by azyk » Tue 25 Apr 2017 07:49

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]

witzl
Posts: 14
Joined: Fri 21 Apr 2017 10:01

Re: Access linked Server / tables results in wrong queries / SDAC 7.3.16

Post by witzl » Tue 25 Apr 2017 10:00

Hi Azyk,

your solution works great!

Thanks a lot,

Cheers,
Dietrich

Post Reply