Hoe kopieer ik AccountView tabellen naar SQL Server database?

Dit topic beschrijft hoe de inhoud van AccountView vastgelegd in de FoxPro tabellen gekopieerd kan worden naar een on-premise SQL Server database.

Hoe slaat AccountView gegevens op?

AccountView slaat gegevens op door FoxPro tabellen te gebruiken:

  • Financiële gegevens per boekjaar en administratie in een map.
  • In de map staan FoxPro tabellen (meestal één bestand).
  • Metadata staat in twee aparte mappen: DataDict en Sys_vfp.

De tabellen vormen gecombineerd de “AccountView database”. De lijst van AccountView-tabellen en kolommen is te vinden in:

Inleiding

AccountView heeft uitgebreide mogelijkheden voor het opslaan van de boekhouding.

Om mee te beginnen is het aanbevelenswaardig om AccountView af te sluiten, Invantive op te starten op de AccountView PC en geen afwijkende instellingen te gebruiken bij het aanmelden. Dan werkt het uitlezen eigenlijk altijd meteen.

Voor structureel gebruik is het verstandig om te werken met een back-up kopie en deze kopie te benaderen vanaf een PC waar geen AccountView op staat.

Tussenvormen zijn mogelijk, maar vereisen zorgvuldige kennisname en gebruik van onderstaande uitleg over de AccountView opslagstructuur.

Leestoegang AccountView database

De Invantive SQL driver voor AccountView werkt gelijktijdig op alle gekozen administraties en jaren. Dit levert geweldige prestaties op in grote omgevingen. Echter, de AccountView SQL driver moet hiervoor wel leestoegang hebben op de gegevens. Dit kan niet als de administratie nog geopend is door een gebruiker. Voor back-ups wordt vaak in de nacht een backup gemaakt via het Windows copy statement. De back-ups kunnen ook gebruikt worden om de gegevens uit te lezen.

Opslagstructuur AccountView Boekhoudingen

AccountView maakt per boekjaar en financiële administratie een complete reeks van FoxPro tabellen die samen een soort “database” vormen voor de combinatie. Elke database is identiek. AccountView zorgt dat de mappen- en tabellenstructuur van alle databases bijgehouden wordt. Dit onderhoudt gebeurt bijvoorbeeld als de AccountView software wijzigt door een nieuwe versie. De gegevens van elke tabel worden vastgelegd in de FoxPro tabel DCT_FILE (zie verder).

Het is sterk aan te raden om de AccountView tabellen per boekjaar/administratie in een map te plaatsen onder een hoofdmap. Het juist structureren van de AccountView mappenstructuur is werk voor een ervaren Visma AccountView consultant.

Opslag AccountView Metadata

Naast de mappen met FoxPro tabellen voor de boekjaren/administraties, gebruikt AccountView nog twee andere mappen voor de metadata over de omgeving:

  • DataDict: AccountView data dictionary.
  • Sys_vfp: Visual FoxPro systeemstructuur.

In deze twee mappen staat metadata en horen geen bestanden voor de boekhouding te staan.

Tabellen

AccountView gegevens worden opgeslagen in FoxPro tabellen. De vertaalslag van een AccountView tabelstructuur naar de bijbehorende FoxPro tabellen (1 per combinatie van boekjaar en administratie) staat in de tabel DCT_FILE onder DataDict met daarin een regel per AccountView tabel. De tabelnaam staat in DBF_NAME en de omschrijving in DBF_DESC. Deze tabelnamen worden omgezet in de view SystemTables@DataDictionary en zijn terug te vinden onder bijvoorbeeld “Beschikbare Objecten” in het Query Tool.

Velden

De afbeelding van velden staat in DCT_FLD onder DataDict. De naam staat in FIELD_NAME en het type in FIELD_TYPE. De lengte staat in FIELD_LEN en het aantal decimalen in FIELD_DEC. Een eventuele omschrijving staat in FLD_DESC. De velddefinities worden omgezet in de view SystemTableColumns@DataDictionary en zijn terug te vinden onder bijvoorbeeld “Beschikbare Objecten” in het Query Tool.

Partities (administraties en boekjaren)

De lijst van administraties en boekjaren wordt automatisch gelezen uit de tabel ADM_LIST onder de map Sys_vfp. Alle regels met een waarde voor de map (veld ADM_DIR) en administratiecode (veld ADM_CODE) vormen de partities voor Invantive SQL. De map uit ADM_DIR wordt absoluut gemaakt door er eventueel de map met systeemtabellen voor te zetten. Indien de map niet bestaat, dan wordt gezocht naar een submap onder de map opgegeven bij de SQL driverattribuut database-search-directories. De submap moet dezelfde naam hebben als de laatste submap uit het veld ADM_DIR.

Mappen zoeken

De mappen DataDict en Sys_vfp worden gezocht onder de map voor systeemtabellen. De map voor systeemtabellen kan ingesteld worden via de driverattribuut system-tables-directory bij het aanmelden.

Als op een AccountView PC gewerkt wordt en de gebruiker heeft bovendien leestoegang, dan kan de driver automatisch de map voor systeemtabellen uitzoeken. Hij leest daarvoor eerst de waarde van het item Path uit onder de registersleutel

HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\Avwin.exe

en als die niet bestaat onder:

SOFTWARE\WOW6432Node\Microsoft\Windows\CurrentVersion\App Paths\Avwin.exe

In het pad waar Path naar wijst wordt de system.ini uitgelezen en de systeemtabellenmap staat dan onder system_tables_loc.

Hoe kopieer ik de AccountView database?

Voor de meest gangbare AccountView administraties is het kopiëren van AccountView naar SQL Server goed te doen met alleen Invantive Data Hub of Invantive Query Tool.

Let op: de AccountView SQL driver van Invantive werkt alleen op het Microsoft Windows platform. Het is dus niet mogelijk om op iOS, MacOS of Linux om AccountView gegevens te verwerken met Invantive SQL.

Voer de volgende stappen uit om gegevens te kopiëren van AccountView naar SQL Server:

  • Definieer een database met twee datacontainers: AccountView en SQL Server.
  • Meld je aan op deze database.
  • Selecteer de administraties/boekjaren met use.
  • Maak een kopie met create table doel@sqlserver.

Hieronder worden deze stappen verder uitgewerkt:

Database met AccountView en SQL Server

Voor het kopieren van de gegevens wordt een Invantive SQL database gedefinieerd met twee datacontainers:

  • AccountView (on-premise)
  • SQL Server (on-premise)

SSL encryptie tussen Invantive SQL en SQL Server staat uit omdat de SQL Server on-premise draait en dergelijke instances zelden juist ingericht zijn qua SSL.

Definieer deze database door een settings-acme.xml bestand te plaatsen in de map %USERPROFILE%\invantive op basis van dit voorbeeld (meer uitleg over het formaat is te vinden in Settings.xml XML-formaat voor virtuele SQL databases):

<?xml version="1.0" encoding="UTF-8"?>
<settings xmlns:xsd="http://www.w3.org/2001/XMLSchema"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          VersionUpdateDate="2021-04-07T07:44:19.9095979Z"
          version="5">
  <group 
   name="ACME"
   sortingOrder="0">
    <connection name="avsql"
                authentication="Default"
                sortingOrder="10"
                description="AccountView plus SQL Server verbinding.">
      <database order="10"
                alias="av"
                provider="AccountView"
                userLogonCodeMode="Hidden"
                passwordMode="Hidden"
                AllowConnectionPooling="false"
                connectionString="system-tables-directory=c:\folder\system;include-inaccessible-databases=False;database-search-directories=c:\folder\years;text-encoding=Windows-1252;ignore-memo-value-errors=True" />
      <database order="20"
                alias="sql"
                provider="SqlServer"
                userLogonCodeMode="Hidden"
                passwordMode="Hidden"
                AllowConnectionPooling="false"
                connectionString="Server=SERVER\INSTANCE;Database=AccountViewCopy;User Id=john_doe;Password=secret;Encrypt=false"
                AllowConnectionStringRewrite="false" />
    </connection>
  </group>
</settings>

De instelmogelijkheden voor AccountView en SQL Server zijn te vinden in de documentatie.

Aanmelden op database

Vervolgens kan aangemeld worden op de database via bijvoorbeeld Invantive Query Tool en Invantive Data Hub. Als de databasegroep “ACME” niet verschijnt, dan is de XML ongeldig. Controleer hem zorgvuldig.

Bij het aanmelden hoeven geen verdere gegevens opgegeven te worden; alle instellingen zijn verwerkt in de connectionString voor zowel AccountView als SQL Server.

Selecteer administraties/boekjaren

Voor elke combinatie van boekjaar en administratie wordt een partitie gedefinieerd. De combinaties kunnen expliciet gekozen worden met bijvoorbeeld:

use [2020BEDRIJF1]@av, [2020BEDRIJF2]@av, [2021BEDRIJF1]@av, [2021BEDRIJF2]@av

Ook kunnen meerdere partities gekozen worden door use te combineren met een select:

use
select code
,      'av'
from   SystemPartitions@DataDictionary
where  ( code like '2020%'
         or
         code like '2021%'
       )

Maak AccountView Kopie

Tenslotte kunnen de AccountView gegevens gekopieerd worden met een eenvoudig statement:

create or replace table ledger@sql
as
select *
from   ledger@av

De tabel ledger wordt gedurende het kopiëren verwijderd indien hij reeds bestond. Daarna wordt de tabel aangemaakt met de juiste kolomnamen en datatypes. Het vullen gebeurt op SQL Server met een bulk insert, waardoor honderdduizenden rijen binnen enkele seconden geladen kunnen worden.

Het create table statement kan herhaald worden voor elke extra tabel, zoals ART_HDR.

Als alternatief kan eigen code geschreven worden of kan het Invantive SQL synchronize statement gebruikt worden. Meer hierover is te vinden in bijvoorbeeld Synchroniseer uw gegevens met één SQL-statement over meerdere cloudplatformen heen.

Alle AccountView Tabellen

Een ander alternatief is een loop gebruiken zoals bijvoorbeeld met:

begin
  for r 
  in
  ( select 'create or replace table ' 
           || name 
           || '@sql as select * from ' 
           || name 
           || '@av'
           stmt
    from   SYSTEMTABLES@DataDictionary
    where  provider_name = 'AccountView'
    order
    by     name
  )
  loop
    begin
      execute immediate r.stmt;
    exception
      when others
      then
        --
        -- Plaats hier eigen logica in welke errors onderdrukt moeten worden.
        --
        null;
    end;
  end loop;
end;

Schedulen

Indien het SQL-script voor het kopieren automatisch moet draaien, dan kan Invantive Data Hub gebruikt worden. Data Hub kan gestart worden vanuit de Task Scheduler met het SQL script via een batchbestand zoals:

set INVANTIVE_PRG=C:\Program Files (x86)\Invantive Software BV\Invantive Data Hub 20.1.444\Invantive.Producer.QueryEngine.exe

set INVANTIVE_CONN=ACME\avsql

set INVANTIVE_SQL_FILE=C:\jobs\batch\sql\av2sql.sql

set INVANTIVE_LOG_FILE=C:\jobs\batch\log\av2sql-%%Y%%m.log

set INVANTIVE_LOG_FILE_OVERWRITE=False

set INVANTIVE_INTERACTIVE=False

"%INVANTIVE_PRG%" /verbose /database:"%INVANTIVE_CONN%" /file:"%INVANTIVE_SQL_FILE%" /logfile:"%INVANTIVE_LOG_FILE%" /logoverwrite:"%INVANTIVE_LOG_FILE_OVERWRITE%" /interactive:%INVANTIVE_INTERACTIVE%

IF %ERRORLEVEL% NEQ 0 (
  echo Failed Data Hub job with exit code %ERRORLEVEL%.
  exit /b %ERRORLEVEL%
)

echo Completed Data Hub job with exit code %ERRORLEVEL%.