Versions Compared

Version Old Version 2 New Version Current
Changes made by

Bill Eastgate (Unlicensed)

Bill Eastgate (Unlicensed)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Requirements:

  • Set up a gtuck datasource and load the Exact Dental SQL template

  • The Teamviewer login has to have Administrator permissions so that it’s possible to install Software

  • The soe administration tool has to be installed usually under

    • C:\Program Files (x86)\Software of Excellence\EXACT Server\SoeAdmin.exe

Installation:

Setup dump script:

  • Create a new folder c:\SoeDump

  • create a new file dump.bat with the following content:

    • REM Run this command to create required database
      REM SQLCMD -S "localhost\SQLEXPRESS" -Q "CREATE DATABASE SoeDumpUpsize"
      REM SQLCMD -S "localhost\SQLEXPRESS" -Q "CREATE DATABASE SoeDumpTemp"
      REM Exact > db
      REM
      "C:\Program Files (x86)\Software of Excellence\EXACT Server\SoeAdmin.exe" AutoSQLUpsize "c:\SoeDump\SoeAdminCfg.xml"
      Sample file:

      View file
      namedump.bat

...

  • Make sure that the path to SoeAdmin.exe in dump.bat

  • Make sure the <ISAMDBLocation> matches the Exact data location on the server and the <SQLServer> matches the instance name of the sql server.

  • To get the the Exact data location execute SoeAdmin.exe and click on “Practice” on the left. The “Directory” property on the right shows the data folder. “Practice” is also the <ISAMDB> value in SoeAdminCfg.xml and could be different.

Create databases for upsize:

  • Open a PowerShell

  • execute SQLCMD -S "localhost\SQLEXPRESS" -Q "CREATE DATABASE SoeDumpUpsize"

  • execute SQLCMD -S "localhost\SQLEXPRESS" -Q "CREATE DATABASE SoeDumpTemp"

Test dump script

  • execute c:\SoeDump\dump.bat

  • wait until finished (commandline window is closed, can take a while)

  • soeAdmin creates a log file in the same directory where it lives usually
    C:\Program Files (x86)\Software of Excellence\EXACT Server\LogConfigParseSummary.html
    and
    C:\Program Files (x86)\Software of Excellence\EXACT Server\PracticeLog.html
    while “Practice” is the Soe instance name (see <ISAMDB> value in SoeAdminCfg.xml)

  • sqlcmd -E -S localhost\SQLEXPRESS -Q "sp_databases" (PowerShell) should show the “SoeDumpUpsize” database growing in size.

  • If the ! Database conversion failed. Error 6. Slyncy (LogConfigParseSummary.html) and Reporting database population failed
    Could not find stored procedure 'spSOEI_RunMain'. (PracticeLog.html) appears in the logs means that the reporting view was not installed on Soe. The upsize should have worked anyway (also see PracticeLog.html) so this can be ignored.

  • If the upsize is working you should also be able to see this in PracticeLog.html:
    eg:
    Share mode DB connecting test...
    Starting incremental upsize...
    14:38:44 DEBTOR4 method: Full - 15765 records updated [Processed]
    14:39:03 DSERV4 method: Full - 1078 records updated [Processed]
    14:39:04 TRANS4 method: Full - 325853 records updated [Processed]
    14:41:55 INVLDNT5 method: Full

Create daily task:

  • Open the “Task Scheduler” and create a new task “SoeDump” and set

  • “Trigger” to 2 o’clock daily

  • “Action” to start c:\SoeDump\dump.bat

Setup gtruck connector:

  • open the “Overview” section for the connector (reports-admin) and set the “Database URL” to jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;databaseName=SoeDumpUpsize (Make sure the instance name is correct)

  • set “Username” to ‘sa’ and “Password” to the password set during instalation

  • Now you should be able to request and import the dump

Additional information and troubleshooting:

The database configuration is usually done with the “SQL Server Management Studio” but the installation requires a restart which is not possible in most cases. As an alternative SQLCMD (PowerShell) can be used when some configuration steps are not done probably during installation.

...

  • Enable TCP:

    • Open the “SQL Server Configuration Manager” and go to “SQL Server Network Configuration” > Protocols for SQLEXPRESS and enable TCP/IP

    • Restart SQL Server service

  • Set authentication to “mixed mode”

    • In PowerShell use SQLCMD: sqlcmd -E -S localhost\SQLEXPRESS

    • Code Block
      USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
  N'Software\Microsoft\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQLServer',
  N'LoginMode', REG_DWORD, 2
GOIn PowerShell use SQLCMD: sqlcmd -E -S localhost\SQLEXPRESS

    • This actually sets a registry key so regedit can als be used to change this setting:

      • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQLServer\LoginMode => 2 (for mixed mode), 1 (server login mode), 0 (Windows authentication)

    • It’s also possible that the instance name (MSSQL14.SQLEXPRESS) might be different. The correct instance name can be found here:

      • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\SQLEXPRESS

  • Activate ‘sa’ user

    • In PowerShell use SQLCMD: sqlcmd -E -S localhost\SQLEXPRESS

    • Code Block
      ALTER LOGIN sa ENABLE ;  
GO  
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;  
GO

Related information: