Exact SQL connector setup Help file

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:

• Download and install MSSQL EXPRESS

  • https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port?view=sql-server-2017

  • Configure

    • Authentication mode: mixed

    • Set a strong password for user ‘sa’

  • During the installation the connection string is shown. It contains the name of the server instance, usually localhost\SQLEXPRESS

• Download and install the connector from PD

• Make sure that the following services are running and startup type is set to Automatic:

  • SQL Server (SQLEXPRESS)

  • SQL Server Browser

  • Slyncy Agent

• Make sure that the “sqlcmd” (try run in Poweshell) command line tool is installed. If not it can be downloaded from https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility

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:
    

• create a new file SoeAdminCfg.xmlwith the following content:

  • <?xml version="1.0" encoding="ISO-8859-1"?> <ReportingViewsConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="ReportingViewsConfigurationSchema.xsd"> <databaselist> <database> <name>Slyncy</name> <parameters> <ISAMDB>Practice</ISAMDB> <ISAMDBLocation>C:\ProgramData\Software of Excellence\EXACT Server\data</ISAMDBLocation> <ISAMServerName>localhost</ISAMServerName> <UseDbServer>true</UseDbServer> <SQLServer>localhost\SQLEXPRESS</SQLServer> <SQLUpsizedDB>SoeDumpUpsize</SQLUpsizedDB> <SQLReportingDB>SoeDumpTemp</SQLReportingDB> <UIDCOTMapping>false</UIDCOTMapping> <CheckData>false</CheckData> <IncrementalISAMToSQL>true</IncrementalISAMToSQL> <IncrementalISAMToSQLMethod>CreatedUpdatedTimeStamp</IncrementalISAMToSQLMethod> <IncludeDeletedRecords>true</IncludeDeletedRecords> </parameters> <phase> <ISAMToSQL>true</ISAMToSQL> <ReportingDBPopulation>false</ReportingDBPopulation> </phase> <tables> <table>DEBTOR4</table> <table>DSERV4</table> <table>TRANS4</table> <table>INVLDNT5</table> <table>EMAILAD</table> <table>APPT6</table> <table>PROVHOUR</table> </tables> </database> <!-- add further <database> elements if needed --> </databaselist> </ReportingViewsConfiguration>

    Sample file:
    

• 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.

The “SQL Server Configuration Manager” can be used to change the network settings of the SQL Server.

To test the database connection directly on the server without gtruck a .udl file can be used:

• Create a new text file c:\SoeDump\test-db-connection.udl and execute it

• Select the server name (usually localhost\SQLEXPRESS)

• Set username to ‘sa’ and the password from the installation

• Press “Test Connection”

• If successful you should be able to select the “SoeDumpUpsize” database

If it fails, the might have been something gone wrong during the setup. Make sure the following is set:

• 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

  • 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

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

Related information:

• https://beefreesoftware.atlassian.net/wiki/spaces/SI/pages/144113665

• https://beefreesoftware.atlassian.net/wiki/spaces/SI/pages/557547521

• https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode?view=sql-server-2017

• https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017