Integrating Southern Software
with PageGate
Send Text and SMS Messages from Southern Software
Follow these steps to use Southern Software with
PageGate
Instructions for software versions:
PageGate v7.0
Windows 2008 R2 (64-bit)
MS SQL Server 2014
MS Access 2010 (64-bit)
PageGate version 7.0 should be installed
and running 'normally' before taking these steps.
Make sure PageGate is setup to run as a service.
Procedure:
1. Stop PageGate
a. Be sure to stop ALL of the PageGate programs
from running (i.e. PG Server, PG Admin, PG Monitor,
PG GUI Clients, etc.)
2. Backup
a. If you have existing data in your PageGate
program, be sure to make a backup of your PageGate
database. The database file is named 'pagegate.mdb'
and is found in the c:\PageGateData\Database\
folder by default.
3. Create
a user for the PageGate services and give
it rights to the SQL server a. Create a new user
named something like pguser. The new user must
have local administrator rights
b. Run the Microsoft SQL Server Management
Studio
c. Expand the 'Security' object
d. Right-click on 'Logins' and choose 'New
Login'
e. Select the 'General' object on the left
f. Add the new pguser account to the 'Login
name' field (use the Search button to get the
full user name)
g. Select the 'Server Roles' object on
the left
h. Check the 'ssadmin' box f. Hit 'OK'
i. Close out of the Microsoft SQL Server
Management Studio
4. Update the PageGate services
to use the new user account
a. Start the Windows Service Manager (Start->Administrative
Tools->Services)
b. For each PageGate service that is listed
(they all start with the letter 'p'), do the following:
1. Right-click on the service name
and choose 'Properties' from the menu
2. Select the 'Log On' tab
3. Select the 'This account' option
4. Enter (or use the 'Browse' button) the pguser
account and password
5. Hit 'OK' - the first time you do this you should
get a popup windows saying that the user has been
granted the Log On As A Service right
c. Close out of the Service Manager
5. Log in with the newly created
user account
a. Log out as the current user
b. Log back in using the new pguser account
6. Import PageGate Database Structure
and Data into SQL server a. Run the SQL Server
2014 Management Studio
b. Enter necessary information to connect
to your SQL server
c. Go to 'Tools'->'Options'->'Designers'
and make sure the 'Prevent saving changes that
require table re-creation' checkbox is not checked
d. Right-click on 'Databases' and choose
'New Database'
e. Name the database 'pgsqldatabase' or
something similar and hit 'OK'
f. Expand the 'Databases' object to show
the newly created database
g. Right-click on the new database object
and go to 'Tasks'->'Import Data', this will start
the Import Wizard
h. Hit 'Next' to get past the wizard's
welcome screen
i. For 'Data source' choose 'Microsoft
Access (Microsoft Jet Database Engine)', and hit
'Next'
j. Use the 'Browse' button to find the
PageGate database file (by default: c:\pagegatedata\database\pagegate.mdb)
k. Unless you have setup a password on
the PageGate database file (not typical), leave
the 'User name' and 'Password' fields blank
l. Hit 'Next'
m. For 'Destination' choose 'Microsoft
OLE DB Provider for SQL Server'
n. Leave all the default values, unless
you need to use SQL Server Authentication, in
which case a User name and Password must be supplied
o. Hit 'Next'
p. Choose the 'Copy data from one or more
tables or views' option and hit 'Next'
q. Select all the tables as source tables
and hit 'Next'
r. Leave 'Run immediately' checked and
hit 'Next'
s. Hit 'Finish'
t. After all the tables are finished being
imported successfully, hit 'Close'
7. Modify Database Structure
- some changes to the new SQL database need to
be made after the import
a. While still in the SQL Server 2014 Management
Studio expand the'Tables' object under the new
PageGate database object
b. Right-click on the dbo.lookup table
and choose 'Design'
c. Select the 'id' field
d. At the bottom of the screen under 'Column
Properties', expand the 'Identity Specification'
object
e. Change 'Is Identity' to 'yes'
f. Right click on the 'id' field and choose
'Set Primary Key'
g. Close the table's design view and hit
'Yes' when prompted to save changes
h. Right-click on the dbo.messages table
and choose 'Design'
i. Select the 'message_id' field
j. At the bottom of the screen under 'Column
Properties', expand the 'Identity Specification'
object
k. Change 'Is Identity' to 'yes'
l. Right click on the 'message_id' field
and choose 'Set Primary Key'
m. Close the table's design view and hit
'Yes' when prompted to save changes
n. Follow these steps for all of the remaining
tables
1. Right-click on the next table
and choose 'Design'
2. Select the blank field at the end of the list
of column names
3. Enter 'record_id' for the Column Name
4. Enter 'int' for the Data Type
5. At the bottom of the screen under 'Column Properties',
expand the 'Identity Specification' object
6. Change 'Is Identity' to 'yes'
7. Right click on the 'message_id' field and choose
'Set Primary Key'
8. Close the table's design view and hit 'Yes'
when prompted to save changes
o. Close out of the SQL Server
2014 Management Studio
8. Create a DSN (data source
name) for the new pagegate sql database
a. Run c:\windows\syswow64\odbcad32.exe
(the 32-bit ODBC Data Source Administrator)
b. Select the 'User DSN' tab (the 64-bit
version of MS Acces can only see 64-bit System
DSNs, but can see both 32-bit and 64-bit User
DSNs - since PageGate can only use 32-bit odbc
drivers, User DSNs are the only option)
c. Hit 'Add'
d. Select the 'SQL Server' driver from
the list and hit 'Finish'
e. Use 'pgdatasource' for both the 'Name'
and 'Description' fields
f. Enter the SQL server in the 'Server'
field and hit 'Next'
g. Make any necessary changes to the authentication
settings and hit 'Next'
h. Check the 'Change the default database
to:' box and select the PageGate database from
the list (pgsqldatabase in this example)
i. Hit 'Next'
j. Leave all the defaults on this next
screen and hit 'Finish'
k. Test the data source if you want, and
then hit 'OK' when done.
l. You will see your new User DSN listed.
Hit 'OK' to close out of the ODBC Data Source
Administrator
9. Create linked tables in MS
Access
a. Run MS Access 2010 and open the PageGate
database (default: c:\pagegatedata\database\pagegate.mdb)
b. Choose 'External Data'->'ODBC Database'
from the toolbar
c. Choose 'Link to the data source by creating
a linked table' and hit 'OK'
d. Select the 'Machine Data Source' tab,
choose pgdatasource from the list and hit 'OK'
e. Select all the dbo.* tables (there should
be 15 of them)
f. Check the 'Save password' box
g. Hit 'OK'
h. Each time you are prompted about the
password, hit the 'Save Password' button
i. Rename all the existing pagegate tables
(the ones without a dbo_ prefix). For example
ahm can be renamed to original_ahm.
j. Remove the dbo_ prefix from all the
sql tables (by renaming them) so their names match
the original pagegate table names
k. Select the 'Linked Table Manager' from
the toolbar
l. Hit the 'Select All'
m. Hit 'OK' n. After MS Access is done
relinking the tables, you can quit out of the
program
10. Try it out
b. Start PG Monitor
c. Start PG Admin
d. Start the PageGate server
Documentation
for Writing Directly to the Database
|