How to update or set up MonTel to use SQL Master Client Database

This topic applies to MonTel Client Version 2.9.52 onwards.

Since this is a one time effort it is best to do this under the supervision or advice of Netwiz support staff.

Before attempting the upgrade make sure that you have a suitable SQL Server working, and available on the network, set up with the correct protocols and user permissions. The use of user permissions will vary with the type of server, and the network administration policies. At the time this topic was written only one SQL server is supported, that is MS SQL Server and it's derivative MSDE. In the future support of other servers using ODBC Drivers, such as Oracle, ProgreSQL and others may be introduced. For details on setting up MSDE see: How to see up MSDE to run MonTel on your network. See also: How to prepare MS SQL Server for MonTel and other SQL Server issues.

For the MS SQL Server and MSDE you will need to decide weather to use NT Authentication or SQL Server Authentication. Microsoft prefer you to use NT Authentication, because it is more secure. From a MonTel administration perspective SQL Server authentication is easier to maintain, since NT Authentication requires the additional step of placing each MonTel user into a group on the Network File Server. This group may be an existing group, such Everyone, or a group created specifically for MonTel Users.

Secondly, make sure that MDAC is installed correctly on a test machine. MDAC 2.6 is installed by default with the operating system on Windows 2000, and 2.7 on Windows XP. Windowx 9X machines will require MDAC to be install. MDAC 2.1 is supplied with the Client Setup files, and will be installed if the correct settings found in the PABX.INI file. DCOM95 is required for MDAC on Windows 95 computers.

On the test computer check that a connection can be made to the SQL Server, and that you know the 'sa' administrator password or have a login with administrator access on the SQL Server. One way of checking that a suitable server is available is to attempt to create a DSN on a client computer using the DATA Sources Applet and attempt to connect to the server using one of the demonstration databases, or as the SQL Administrator to the Master database in MS SQL Server.

If you are importing an existing Master Client Database you should allow an hour or two for this process.

Now the steps.

• Install the new MonTel software (if this has not already been done) using InstMon, and test as normal (re-enable AllowOnlyAdminToUSeMonTel=1) and make sure any Master Client Database updates are complete.
• Check that the SQL Server is available (see above).
• Force all users to exit MonTel. See How to force all users out of MonTel for administration purposes.
• Backup the existing Master Client Database (if any).
• Make the following changes in the PABX.INI file using EdIni32.

UseSQL = 1

ODBCDriver = "SQL Server"

DatabaseName = "CLIENT32"

DatabaseServerName = {Network Computer Name of SQL Server eg: FS1}

For more detail see: How to prepare MS SQL Server for MonTel and other SQL Server issues.

• If you are using Windows NT Authentication mode then also set:

WindowsNTAuthenticationMode = 1

MonTelAdminGroup = "{Name of MonTel Admin group on the Network file server}"

MonTelUsersGroup = "{Name of MonTel users group on the Network file server}"

For more detail see: How to prepare MS SQL Server for MonTel and other SQL Server issues.

• You do not need to make nay changes to the JetODBC settings.
• Set AllowOnlyAdminToUSeMonTel=1.
• On a MonTel Administrators computer, delete the existing Local Client database, and set up the client (again), to make sure that MDAC is installed.
• Run MonTel32.exe to create the new master client database on the SQL Server, and to transfer existing data (if required). To do this you will need access to the administration account on the SQL server, to be able to create new users and databases. MonTel needs this information only once to do the set up, and does not store this information elsewhere.
• Follow the prompts below
• This is the first message you should see. If this is not, check that the local client database has been deleted.
  
• Check that the Database name and the SQL Server computer name is correct and then press yes:
• The updating Client Database screen should appear along with the prompt to create the master client database.
  @
• Confirm the creation process:
  
• You should now be presented with the SQL Login screen.
  For the MS SQL Server the prompts above are the default, and in this example assume that Windows Authentication has NOT been selected in the PABX.INI file, and that there is an existing jet master client database, form which you want to import data. If you are importing data the last two settings are recommended. Firstly, if you don't allow MonTel to fix data con traint errors where it can, there is a risk that much data won't be imported due to the cascading effect of refferntial errors. Removing notes from imports is also recommended to save space.
• After pressing OK on the previous screen you receive the following prompt:
  
  Then you probably have the wrong password for the 'sa' account, or if you are using NT Authentication, you do not have administrator permission on that Server.
• At about 66% the Master client database will have been created, and the importing will commence. This may take some time depending on the size of the database. Allow a few hours.
• And hopefully at the end you will see:
  
• This will be followed by messages to create a local client database.

Any errors that occurred in transferring the data will be found in SQLERR.TXT file in the same folder as the MasterClient Database from which the import was generated. It will contain a series of messages such as:

This is the error log for exporting C:\PABXSD\CLIENTDB\CLIENT32.MDB to 'CLIENT32' using SQL Server on NETWIZ1 on 07/11/2002 13:01:32.

Import Error: 3146 - 'ODBC--call failed.'.

SQL statement:

INSERT INTO TypeOfAddress (TypeOfAddressID, Description ) VALUES( 2, 'Street' ) ;

2627: [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'TypeOfAddressIDIndex'. Cannot insert duplicate key in object 'TypeOfAddress'.

3621: [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.