Hello,

I have a BO (my first, yea!) that grabs the data I'm looking for and all is well in the universe.

Now, I have a requirement to combine data from multiple data sources (same table structure, but the data is living on different SQL Servers) and I'm figuring a UNION would give me what I need.  This is a read-only scenario, so I don't need to worry about updates being sent back to the different servers (yet).

I have not gone through the process of adding additional data sources to my project yet, but I will review the documentation on how to set that up. After that, I guess the trick is how to select data from different servers into the BO dataset...

Any suggestions?

Thanks.

Hi William,

If your SQL servers are defined as Linked Servers in SQL than I presume you could also create your data using a sigle view and fully qualified table names for each Select in the UNION query.

Cheers, Peter

Yes, that was my original design intent, but I could not get it to work... Maybe I'm not formatting the query properly.

The data in each server is itself coming from a Linked Server (a VFP database). The intent is to use the SQL server on either side of a WAN connection to query the VFP data locally before sending the results over the WAN.

In this example, I'll reference SQL Server 1 as the "local" server and SQL Server 2 as the "remote" server.

I found that when I added the remote server as a linked server in the local server, I could not query the remote servers linked servers.

Meaning, this query executed on the local server in SSMS returns an error:

SELECT *

FROM [NameOfLinkedServerInLocalServer].[NameOfLinkedServerInRemoteServer]..[NameOfVFPTableInRemoteLinkedServer]

I've tried formatting the FROM in different ways, but everything comes back with an error. For example:

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI10" for linked server "NameOfLinkedServerInLocalServer" does not contain the table ""NameOfLinkedServerInRemoteServer"."NameOfVFPTableInRemoteLinkedServer"". The table either does not exist or the current user does not have permissions on that table.

Is this type of query possible? If so, how would I format the FROM clause?

 

Thanks.