...
Sage50_Connections.exe
This is to set up the credentials being used for SQL Server and Sage 50
Sage50_ETL.exe
This is the process that will copy the data from Sage 50 and move this to SQL Server making it relational and accessible by pi
Sage50_Repos.sql
This is the preconfigured dashboard repository that is to be restored into pi
Sage50_Script.sql
This is an SQL file that the ETL process uses each time it runs
Sage50_Views.sql
These are SQL views that have been created for some of the more complex calculations required for the dashboard and are checked each time the ETL process is run
Exclude_Tables.txt
This is a config file containing a list of tables from Sage 50 that are excluded from the ETL process as not required in the default build. If a table is required then it can just be removed from this file, alternatively a table can be added to this file if you require it to be excluded
PIDataSources.txt
This is used to list the data source name of the ODBCs that are used for the data transfer
SQL Configuration
| Info |
|---|
The following explains a default setup. If you already have an instance of SQL Server, please check with an SQL administrator before making any changes. |
The following explains how to:
Set SQL Server Authentication Mode
Create a new database
Create a new user
Set the TCP Port for inbound connections
...
Firstly, open SQL Server Management Studio (SSMS) and login to the server you require
If this was set up as default it should look something like this:
...
Make SQL Server authentication mode available
Right click on the Server name at the top left and select Properties
Select Security on the left-hand side, choose SQL Server and Windows Authentication mode and click OK
You may get asked to restart the server. Please check that this server is not in use by any other users before completing this action
Create a new user:
...
Select Server Roles on the left-hand side, tick sysadmin and click OK
...
Create a new database:
...
Right click on Databases and select New Database…
...
Set the TCP Port for inbound connections:
Open Computer Management from the start menu and find SQL Configuration Manager
Expand SQL Server Network Configuration
Select Protocols for SQLEXPRESS
Right click on TCP/IP and select Properties
On the Protocol Tab, make sure this is set to Enabled
On the IP Addresses tab, scroll right to the bottom and set the TCP Port as 1433
You will be asked to restart the SQL Server (this is JUST the SQL Server, not the machine server). Please check that this server is not in use by any other users before completing this action
The Server can be restarted by Selecting SQL Server Services on the left-hand side, right-click the SQL Server in the central console and click Restar
...
| Child pages (Children Display) |
|---|
Setting up the Sage ODBC connections (adding a company)
...