Setup SQL Server and Import Data

These intructions are technical and designed for use by IT administratorss. Contact us if you would like Palace IT to do this for you.

Please ensure you have the following before proceeding:

• A Stock Tracker 4 database in version 30 format (check the Database Version on the Stock Tracker 4 login screen).
• The Stock Tracker 4 database password (contact Palace IT to obtain).

Install SQL Server and SQL Server Management Studio

• Download and install SQL Server Express edition using the default settings.
• Download and install SQL Server Management Studio (SMSS) using the default settings.

Configure SQL Server Authentication

• Open SQL Server Management Studio and connect using Windows Authentication.
• Right click the server name (tree root) in the Object Explorer and select Properties.
• Goto the Security section and enable SQL Server and Windows Authentication mode.
• Press OK.

Configure SQL Server for Networking

• Open SQL Server Configuration Manager (Start -> type "SQL Server Configuration".
• Goto SQL Server Network Configuration, expand Protocols for SQLEXPRESS.
• Double-click TCP/IP.
• In the Protocol tab set Enabled to to "Yes".
• In the Protocol tab set Listen All to to "Yes".
  

  

• Scroll down to IPAll, clear TCP Dynamic Ports and set TCP Port to "1433" (or port of your choice).
  

  

• Press OK to save TCP/IP settings.
• In the SQL Server Services right click and restart the SQL Server and SQL Server Browser services.

Setup Firewall

• Allow incoming connections on TCP port 1433 (or your chosen port) for private and/or domain connections.
• Allow incoming connections on UDP port 1434 (or your chosen port) for private and/or domain connections.

Create Stock Tracker 5 Database

• Open SQL Server Management Studio and connect using Windows Authentication.
• Right click Databases -> New Database… -> Database name = "PST5".
• Press OK.

Create User

• Expand SQL Root\Security\Logins -> right click New User….
• Login name: "PST"
• SQL Server Authentication
• Password: (contact Palace IT for password)
• Uncheck Enforce password policy, Enforce password expiration and User must change password at next login.
• Default database: "PST5"
• User Mapping (left side): Tick PST5 | User: PST | Default Scehma: dbo
• Press OK to save user.

Import Stock Tracker 4 Database

• Open SQL Server Management Studio and connect using Windows Authentication.
• Expand to SQL Root\Databases\PST5 -> right click on PST5 -> Tasks -> Import Data...
  On some versions of SMSS the "Import Data..." is greyed out. In this situation run "SQL Server 2017 Import and Export Data (32-bit)" from the Windows Start menu.
• Welcome to SQL Server Import and Export Wizard window
• Next ->
• Choose a Data Source window
• Data Source: Microsoft Access (Microsoft Jet Database Engine)
• Browse... to your file (do not import from a file in the desktop folder, it won't work)
• Press Advanced...
• In Data Link Properties goto the All tab.
• Find "Jet.OLEDB:Database Password" in the list. Double click and enter the old database password (contact Palace IT for the password).
• Press OK
• Next ->
• Choose a Destination window
• Destination: "SQL Server Native Client 11.0"
• Authentication: Use SQL Server Authentication
• User name: "PST"
• Password: (PST user password from Palace IT)
• Database: "PST5"
• Next ->
• Specify Table Copy or Query window
• Highlight "Copy data from one or more tables or views"
• Next ->
• Select Source Tables and Views window
• Check the topmost box in the column header to select all tables
• Press Edit Mappings...
• In the Transfer Settings check "Enable Identity Insert"
• Press OK to close the Transfer Settings window
• Next ->
• Save and Run Package window
• Run Immediately should be checked
• Next ->
• Complete the Wizard window
• Finish
• Performing Operation window
• Wait for the import to finish
• Close

Back up Database Before Proceeding

The database should be backed up running the conversion scripts in case there is a problem.

• Expand to SQL Root\Databases\PST5 -> right click on PST5 -> Tasks -> Back Up...
• Press OK
• When finished press OK to close the completed confirmation dialog

Install and Setup Palace Migration Tool

The conversion scripts change the contents of the database to work with Stock Tracker 5.

• Download and install the Palace Migration tool from the download page.
• Run the Palace Migration Tool
• Press the Connect to Database toolbar button.
• Enter the settings to connect to SQL Server using the "PST" user account to connect to the "PST5" database.
  
• Press Test Connection to ensure that the connection works.
• Press OK to close the Database Properties.

Run Conversion Scripts using Palace Migration Tool

The conversion scripts change the contents of the database to work with Stock Tracker 5.

• Download the SQL Server Scripts from the download page and extract the folder tree to a temporary location.
• Open Palace Migration Tool.
• Press the Add SQL Files toolbar button.
• Browse to (extracted scripts folder)\PST5_SQLScripts\ConvertFromAccess\
• Press Ctrl+A to select all files (from _info.sql to weights_tweaked.sql).
• Press Open
• Once the files have been added to the list press the Execute SQL Files toolbar button. A command prompt window will appear, press any key when it has finished.
• Press the Remove all Files toolbar button (3 blue circles) to clear the files list.
• Press the Add SQL Files toolbar button again.
• Browse to (extracted scripts folder)\PST5_SQLScripts\StoredProcs\
• Press Ctrl+A to select all of the files sp_*.sql files.
• Press Open
• Press the Execute SQL Files toolbar button once more to add the stored procedures to the database.

If there were no errors the database is ready to be used.