SQL Server

Required Files and Locations

  • When creating a new Instance, you’ll be able to choose a specific Integration Code.  Certain files will be loaded into the Instance based on this Code.  For this ‘How To’ I’ll be using Dynamics AX as an Integration Code, but the instructions are essentially the same for all other Integrations.
  • Integration Files consist of…
    • SQL Scripts for Creating Publish and Import Databases
    • APSInterfaceSetting.xml
  • Location of Files
    • Publish Script – C:\ProgramData\PlanetTogether\AX 2014.4.8.2 (instance name)\IntegrationFiles
      • Publish Location
    • Import Script and APSInterfaceSettings.xml – C:\ProgramData\PlanetTogether\AX 2014.4.8.2\IntegrationFiles\DynamicsAX
      • Import and mappings location

Create Import and Publish DB with SQL Scripts (or mdf file)

  1. Open Publish or Import Script in SQL Server (NOTE: if using older versions of PlanetTogether there will be MDF files you’ll need to attach to SQL Server instead of running scripts)
  2. Modify Database Name (only if name change desired) and MDF/LDF File Locations
      • Database Name is the name in brackets [name], and has to be change in all locations in Script (easiest to use Replace All Function)
    CREATE DATABASE [PlanetTogether_AX]
     CONTAINMENT = NONE
     ON  PRIMARY 
    ( NAME = N'PlanetTogether_AX', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\PlanetTogether_AX.mdf' , SIZE = 38912KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'PlanetTogether_AX_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\PlanetTogether_AX_log.ldf' , SIZE = 47616KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
  3. Execute Script

Create SQL user with Admin Rights

  1. Can use SA account or create SQL user with Admin Privileges for PlanetTogether to use

Enter Connection Information in Instance Manager

  1. Open Instance Manager
  2. Select Instance and “Stop Services”
  3. Select Instance and click “Edit” to open
  4. Import DB Settings are entered on “ERP Database” Tab…Publish DB Settings are entered on “Extra Services” Tab
    • ERPDatabase Tab
    • Import Connection Type
      • SQL Server (select when connecting to SQL Server)
      • ODBC (select when using ODBC connector)
      • Oracle (select when connecting to Oracle)
      • OLE DB (select when using OLE DB)
    • Import Database Connection Info
      • Server Name: SQLServer Instance Name
      • Database Name: Import Database Name
      • User Name: SQL Server user name
      • Password: SQL Server user password
    • Use Custom String

      • When checked…will be able to manually enter a connection string (useful when connecting to DB’s other than SQL Server)
    • Run SQL Before Import
      • When checked…will run syntax in “SQL to run before import” area
    • Extra Services Tab
  5. Restart Services

Load Mappings File

  1. Open “Instance Manager”
  2. Select Instance and “Stop Services”
  3. Move APSInterfaceSettings.xml (Mappings File) UP one level to IntegrationFiles Folder
    • From: C:\ProgramData\PlanetTogether\AX 2014.4.8.2\IntegrationFiles\DynamicsAX
    • To: C:\ProgramData\PlanetTogether\AX 2014.4.8.2\IntegrationFiles
    • move mappings file
  4. Select Instance and “ReStart Services”

Test Connection

  1. Run APS Client Program and Login to Instance
  2. Open “Data Mappings” (Data Tab)
  3. Using the Jump Menu…navigate to “Job Mappings” (although any page will work)
  4. Enter Valid Table Name or View Name in SELECT FROM text area
  5. Press “Browse Table”
  6. If no error message then Connection was Successful
    • Test Connection

Leave a Reply