I can't seem to find a worthwhile answer in the V7 documentation

Our current architecture:

Server A with DB A and DB B (Win 2008 R2 with SQL2008)

Server B with DB Z (Win 2008 R2 with SQL2008)

Is it possible to:

From ProIV on Server A, connect to DB Z whilst still connected to DB A and exchange data between the 2 DB's?

One option:

Setup a linked server via SQL. Then in embedded SQL statement you can refer to the table by server.database.owner.table in your query. I just ran a quick test on V7 and it looked to work selecting data from another server into a shell of a file definition.

See Topic ID: 720114 in the PROIV V7 documentation.

Follow the links from there.

MS SQL gives you two options, distributed (four part) queries, as has already been suggested, and OPENQUERY.

With a distributed query, the local server may be tempted to request a lot more data in order to do more processing locally, to generate the end result set, and the query might run slower than expected. With OPENQUERY, you stand a better chance of the remote server doing all the work for that part of the query.

Using a distibuted query is probably easier, in PROIV terms, as it can be accomplished by using the logical database name (from pro4.ini) in the physical file name. It's transparent, but that it obfuscates the fact that that logical file is actually a remote resource might not be a good thing, when it comes to later maintenance and the uninitiated. Side effects can involve SOSHOST_MUTEX waits under heavy load, while the local instance gathers statistics from the remote server to factor into the execution plan, or the wrong execution plan entirely, resulting in full table scans, and orders of magnitude more network traffic, if the account running the query can't access the histogram(s) http://msdn.microsof...y/ms175537.aspx

If the remote files are only being referenced in PROIV within full function SQL, or wholly native MS SQL objects, like stored procedures, have a look at OPENQUERY.

Edited by Neil Barber, 17 February 2014 - 01:34 AM.

Hi ..

I have tried calling sp_linkedservers(for reading) from OPENQUERY function(for updating),the process was slow and resource intensive on our side.

The ff. is what I have done:-

I created the sp_linkedsevers and opened the database ports and also permission.

Step 1. I created the sp_linkedservers and and wrote away to another file.

Step 2. I then wrote an update which updates the aliased sql DB table.

below is my sql example :-

Every nested query in the FROM clause must have an alias:
 

SELECT Company, Branch,
Name,oc_ManagerID,oc_ManagerName,SalesBudget1,SalesBudget2, SalesBudget3
from(
SELECT 'Company1' AS Company, Branch,
Name,oc_ManagerID,oc_ManagerName,SalesBudget1,SalesBudget2, SalesBudget3
FROM ProCoyA.dbo.SalSalesperson
UNION
SELECT 'Company2' AS Company, Branch,
Name,oc_ManagerID,oc_ManagerName,SalesBudget1,SalesBudget2, SalesBudget3
FROM ProCoyB.dbo.SalSalesperson
UNION
SELECT 'Company3' AS Company, Branch,
Name,oc_ManagerID,oc_ManagerName,SalesBudget1,SalesBudget2, SalesBudget3
FROM ProCoyP.dbo.SalSalesperson
) AS Merged
WHERE Company IN (:companies)

This works on our side.Tested on MSSQL2008 and MySql database.I hope this helps.

Matthews Estrice

Thanks all.

Ended up going with the logical DB/s within proiv.ini route

Watch out for SP_SERVEROPTION "collation_compatible". If it is not set to "true", a remote column referenced in the WHERE or ORDER BY clauses could result in the parent table being dragged back, in its entirity, to be processed locally.