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

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

Postby 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
witzl
 
Posts: 2
Joined: Fri 21 Apr 2017 10:01

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

Postby 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]
azyk
Devart Team
 
Posts: 820
Joined: Fri 11 Apr 2014 11:47

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

Postby witzl » Tue 25 Apr 2017 10:00

Hi Azyk,

your solution works great!

Thanks a lot,

Cheers,
Dietrich
witzl
 
Posts: 2
Joined: Fri 21 Apr 2017 10:01


Return to SQL Server Data Access Components