SQL Database

Connect Apptoto to an on-premises SQL database using the Apptoto Sync Agent to sync appointment and contact data automatically.

Apptoto offers a Sync Agent that runs on Windows desktops to synchronize calendar and address book information between Apptoto and your SQL database.

Requirements

• An on-premises database (cloud-hosted databases may require a VPN or hosting the Sync Agent within the cloud provider’s infrastructure)
• A Windows computer running continuously with network access to your SQL database at least 90% of the time (automatic hourly syncing requires the machine to be powered on)
• The connection string for your database
• Your Apptoto username, password, and API key (found at Settings > Integrations > Developer Tools > API Keys

Note

If you are not using Microsoft SQL Server, configure a 64-bit ODBC Data Source on the machine where you will install the Sync Agent before proceeding. If you plan to run the Sync Agent as a Windows Service, set up the ODBC data source under System DSN, not User DSN.

Installation

1. Download and run the installer. Contact support@apptoto.com for instructions on obtaining the setup.exe file. Run the installer and allow it to complete.

2. Launch the Sync Agent. From the Windows Start menu, open Apptoto > Apptoto Sync Agent. If the shortcut doesn’t appear, run C:\Program Files (x86)\Apptoto\ApptotoAgent\ApptotoTray.exe directly.

3. Log into Apptoto. Use your API Key instead of your password for better stability. Find your API Key at Settings > Integrations tab > Developer Tools section > API Keys.

   

   

   

4. Configure SQL database connection. Select SQL – ODBC as the connection type and enter the appropriate connection string.

   For Microsoft SQL Server:

   Server=<HOST>;User Id=<DBUSERNAME>;Database=<DATABASE>;

   Enter the database password in the Password field.

   

   For ODBC connections: Keep SQL Provider as ODBC and use:

   DSN=<ODBCDSNNAME>;Uid=<DBUSERNAME>;

   Enter the database password in the Password field. If no username is required:

   DSN=<ODBCSOURCENAME>;

   

5. Test connection and save. Click Test Connection. If successful, click Save. Consider checking Launch at Startup to ensure the agent runs after restarts.

6. Configure calendar. Contact support@apptoto.com to request assistance. An Apptoto support representative will configure calendars based on your SQL schema.

Running as a Windows Service

If the “Run as Windows Service” checkbox is not greyed out, click the checkbox and click “Save.” You can skip the steps below.

If the checkbox remains grey, to run the sync agent as a Windows Service (allowing operation without logged-in users), follow the steps below:

1. Close the Apptoto Sync Agent in the system tray.
2. Rerun it as Administrator.
3. Check Run as Windows Service and click Save, then follow prompts.

If the checkbox remains greyed out:

1. Close the Sync Agent.
2. Open Command Prompt and navigate to c:\Program Files (x86)\Apptoto\ApptotoAgent.
3. Run ApptotoService.exe -install.
4. Click Yes when prompted.
5. Open the Services console and set Apptoto Sync Agent Service to Automatically (Delayed Start).
6. Manually start the service initially.

To update the settings, run the Apptoto Sync Agent tray application again. Save the settings, and then follow the steps above to copy the settings over to the correct profile.

Running multiple sync agents (on the same machine)

To run multiple agents on one machine for different calendars or databases:

1. Create a shortcut to c:\program files (x86)\Apptoto\ApptotoAgent\ApptotoTray.exe.
2. Set command line arguments to --profile <PROFILENAME> (use only alphanumeric characters). Replace <PROFILENAME> with the 2nd Apptoto’s username, or the 2nd database name. Only use alpha-numeric characters.
3. Launch using that shortcut and sign in again.
4. Running as a Windows Service (recommended) will create a new service with the profile name.

You can create as many shortcuts with different profile names as you like.

Read query

Once configured, Apptoto support sets up calendars using SQL queries. Queries run hourly, syncing appointments (using the designated Start Time field) 7 days in the past and 30 days into the future in weekly chunks. That time range and chunk size can be adjusted.

Standard fields include:

• Unique identifier for the appointment (external_id) [REQUIRED]
• Title of the appointment (title)
• Created date/time - datetime field (created)
• Updated date/time - datetime field (updated)
• Description/body/notes of the appointment (content)
• Start time - datetime field (start_time) [REQUIRED]
• End time - datetime field (end_time)
• Participant name (participant_0_name)
• Participant phone (participant_0_phone) [PHONE OR EMAIL REQUIRED]
• Participant email (participant_0_email) [PHONE OR EMAIL REQUIRED]

Multiple phone/email addresses are supported; the system prioritizes mobile, then home, then work numbers.

For separate date and time fields, combine them:

CAST(Date as DATETIME) + CAST(Time AS DATETIME)

Custom fields from your database can be stored as event custom fields and used in messages or conditions. E.g., if you have a field for the appointment type, we can stash it in event.custom.appt_type and use it in messages or conditions within Apptoto.

Note

Multiple participants can be handled, but the query must return all participants in each row. If there isn’t a “title” field, then Apptoto can be set to create appointments in Apptoto with a default title (e.g., Appt with {{ participant_0_name }}).

Update query (optional)

Apptoto can write back to your database when clients respond. Support will create UPDATE statements to modify specific columns when an event occurs (such as a confirmations or cancellations), using the appointment ID in the WHERE clause.

Troubleshooting

The Sync Agent won’t log into Apptoto

Verify that your firewall is not blocking outbound connections from the Apptoto Sync Agent process.

For more, see Exchange/SQL Sync Agent Troubleshooting.