Hello,
Is there a simpler way to read tables from multiple SQL servers in, for example, "for ... next" loops, than this rudimentary one bellow?
Imagine you have X legal entities using the same ERP package but in X different geographical locations spread across EMEA-APAC regions. Years back, we have set up a near real-time replication within the MS SQL DBs to replicate needed tables onto one QV DB server and multiple schemas, and that worked extremely well. Doing so we have relieved the burden of constant reading from the transactional databases to refresh a hundred QV apps. Yes, QVDs have been created further down the road.
Now, we are facing the ERP system upgrade, which is going to take a year, before all locations are upgraded to the same version. And, we need to ensure up-to-date data are shown in QVapps.
That would not be an issue if we are not forced to upgrade our SQL databases from 2005 to 2014 (due to end of support), which can't be replicated to SQL 2008 anymore because, according to Microsoft, we can replicate only within two major SQL releases.
So, I would need to edit every script and add the "if...then...else" statement as in this example, etc.:
for I = 1 to $(vNoOfComp)
vComp = lookup('CO_ID', 'CompSeq', I, 'Companies');
vDBSchema = lookup('DBSchema', 'CO_ID', '$(vComp)', 'Companies');
if I = 3 then
$(Must_Include=..\ConsolidatedExcel\ERPConnection31.txt);
else
$(Must_Include=..\ConsolidatedExcel\ERPConnection.txt);
endif
Br_Map$(vComp)$(vLastFY):
mapping load
'$(vComp)$(vLastFY)'&GL03002 as BrID,
GL03002&'-'&GL03003 as BrDesc;
sql select GL03002, GL03003 from $(vDBSchema)GL03$(vComp)$(vLastFY) where GL03001 = 'B' and GL03014 = 0;
...
...
next
Thank you
