Upgrading Journyx Timesheet 5.0 Internal Database to Service Pack 2 - Journyx, Inc.

Support
Upgrading Journyx Timesheet 5.0 Internal Database to Service Pack 2

 

This guide explains how to upgrade the Internal Database of Journyx Timesheet 5.0 to the equivalent of Microsoft SQL Server 2000 Service Pack 2. The benefit of upgrading is to obtain fixes for certain security vulnerabilities in the database; there are no new features or performance enhancements.

Please read these directions thoroughly before you begin. Direct any questions to the Journyx support team at support@journyx.com before you begin this procedure.

These instructions are for all versions of Timesheet 5.0, including 5.0m1 and 5.0m2. These instructions work with all Windows operating systems.

Before you begin

Please create a directory named MSDESP2data on the root level of the C:\ drive on your Timesheet server. This directory will be used to store critical data and install images.

Throughout these instructions there are DOS commands that you will need to enter exactly as they appear. These commands often contain quotation marks, and you must include these quotation marks exactly as they appear.

Please Note: This document is written such that every command you will need to enter at a DOS prompt is formatted with a dot in front of it, like this:

  • a sample command

If you have installed Timesheet into the default directory and created the recommended folder (c:\MSDESP2data), then you can copy and paste each of those commands into your DOS prompt.

You may select the command after the dot and right-click on it and select "Copy".

Then in your DOS command box you can right-click on the blue toolbar and scroll down to "Edit" and over and down and select "Paste".

The command will appear where your cursor is. Often it will appear to have an extra space after the command, you can ignore it. You can simply hit the [enter] key to execute the command.

If you have installed to a different location, then you can paste the command into the DOS prompt as shown. But you will need to use the left arrow to go back in to the command and change the path.

Please Note: You must be logged in as the same Windows user who originally installed Journyx Timesheet. Please follow these directions to find out which Windows user that is:

  1. From the Start menu, click Programs -> Journyx Timesheet -> Timesheet Command Line Prompt
  2. At the C:\Program Files\Journyx\jwt\bin> command prompt, type: notepad "%WTHOME%\config"
    Notepad will appear on your screen. In notepad, locate the following line: WT_INSTUSER=username
    In that line, after the equals sign, is the name of the Windows user that originally installed Timesheet. You must be logged in to Windows as that user in order to perform this update.

In Notepad, in the toolbar at the top, click |File| -> |Exit|.

Upgrading Timesheet to MSDE Service Pack 2

  1. The first step is to download the new MSDE installer. You can obtain this from Microsoft, at the following URL:
    http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/default.mspx

    You will need to download a file called SQL2KDeskSP2.exe. That file is 26.3 MB. You can read the installation instructions, but do not follow the installation instructions on that page. We will install it as part of step 5, below.

    Save this file to c:\MSDESP2data. This should be the first file in that directory.

  2. Backup Journyx Data (backupdb)

    Run Journyx backupdb command to save your Timesheet data. From the Start menu, click Programs -> Journyx Timesheet -> Timesheet Command Line Prompt. At the C:\Program Files\Journyx\jwt\bin> command prompt, enter:

    • backupdb -v "C:\MSDESP2data\before_msde_upgrade.jx"

    This will save a file named before_msde_upgrade.jx to the C:\MSDESP2data directory. This will be the second file in that directory. It should be a rather small file because it is zipped.

  3. Backup Journyx MSDE files

    After the backupdb completes, you will be returned to a DOS prompt in the C:\Program Files\Journyx\jwt\bin directory. Enter each of the following commands:

    • net stop "Journyx Timesheet"
    • net stop "MSSQL$journyxmsde"

    The command to stop MSSQL$journyxmsde may return the following error:
    The MSSQL$journyxmsde service is not started.
    More help is available by typing NET HELPMSG 3521.

    This is fine, since it means the service is already stopped.

    Continue with these commands:

    • copy "%WTHOME%\database\MSSQL$journyxmsde\Data\journyx.mdf" c:\MSDESP2data\journyx.mdf
    • copy "%WTHOME%\database\MSSQL$journyxmsde\Data\journyx_log.LDF" c:\MSDESP2data\journyx_log.LDF
    • copy "%WTHOME%\config" c:\MSDESP2data\config

    This will make copies of three critical Timesheet database files in the c:\MSDESP2data directory. These will be the third, fourth, and fifth files in that directory.

  4. Extract MSDE SP2

    After the files have been copied you will still be in the c:\Program Files\Journyx\jwt\bin directory. Type the following command to perform the extraction:

    • c:\MSDESP2data\SQL2KDeskSP2.exe

    A Windows Installation box will appear, click "yes" and follow the other prompts to perform the installation. The default installation location is c:\sql2ksp2. If you accept this default directory you will be able to proceed normally, otherwise you will need to adjust the following instructions to point to the directory you choose.


    This process could take a minute or two. It often does not give any confirmation message when it completes, it simply closes the window box where it has been showing you the installation status.
  5. Install MSDE SP2

    After the extraction you will still be in the c:\Program Files\Journyx\jwt\bin directory. Enter the following command to perform the installation:

    • notepad C:\sql2ksp2\msde\Setup.ini

    You will see a file that is nearly empty, as show below:


    You will need to add the following lines to this file:

    INSTANCENAME=journyxmsde
    TARGETDIR=C:\Program Files\Journyx\database\
    SECURITYMODE=SQL
    USEDEFAULTSAPWD

    After you have completed this process, the setup.ini file should look like the following:


    In Notepad, in the toolbar at the top, click |File| -> |Save|, and then |File| -> |Exit|.

    After the saving these changes to setup.ini you will still be in the c:\Program Files\Journyx\jwt\bin directory. Install MSDE SP2 by typing this command:

    • c:\sql2ksp2\msde\setup /p c:\sql2ksp2\msde\setup\sqlrun01.msp /L*v c:\sql2ksp2\msde\sp2.log

    Click "yes" to any prompts. You should be prompted to reboot, which you should do whether or not you are prompted.

    After the reboot, from the Start menu, click Programs -> Journyx Timesheet -> Timesheet Command Line Prompt. Test to see if SP2 was properly installed by typing this command:

    • python -c "import wtdb;db=wtdb.wtdb();print db.query('SELECT @@VERSION')"

    This should return information similar to the following:
    [('Microsoft SQL Server 2000 - 8.00.534 (Intel X86) \n\tNov 19 2001 13:23:50 \n\t Copyright (c) 1988-2000 Microsoft Corporation\n\t Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 3)\n',)]

    The value 8.00.534 is what you need to check. If your number is lower than this number then the SP2 installation did not complete correctly. After you install the hotfixes (step VI, below) that number will be slightly higher, which is acceptable.

  6. Apply any appropriate MSDE SP2 security fixes

    At this time the new SP2 version of MSDE is fully installed. This version of MSDE is equivalent to Microsoft SQL Server 2000 Service Pack 2. Microsoft has since released several hotfixes, or security updates. Unfortunately, these must be applied by hand, closely following the instructions for copying files contained in each patch. Journyx always recommends that you stay current with Microsoft's security patches; however, if you choose to not install these patches you may skip to the next step.

    At the time of this writing, two relevant hotfixes are available from Microsoft:

    MS02-038

    MS02-039

    Please check Microsoft's SQL Server 2000 web page for any new security updates.

    Download the patch files from those links and closely follow the instructions contained in their README.TXT files.

    Important notes for installing the hotfixes:

    • In both hotfix installation instructions there are two sets of installation instructions, you should use the "Standard Hotfix Installation Steps".
    • You will be told to run query analyzer by connecting as 'sa'. The 'sa' password is blank at this time. It will be changed by the Timesheet command in step VII, below. Do not change this password at this time or you will not be able to complete step VII.
    • The hotfix installation instructions refer to "<installation path for this SQL Server instance>". If you have installed Timesheet in the default location of c:\Program Files\Journyx, then the path you should use is c:\Program Files\Journyx\database\MSSQL$journyxmsde.
    • The hotfix installation instructions refer to "<%ProgramFiles%>". This is usually c:\Program Files. However under some circumstances that may be different. You may have to use the Windows Search tool to find the files that you need to replace.
    • The hotfix for MS02-038 will tell you to run . This step is not necessary because the Timesheet database has been uninstalled. The function that Servpriv.exe performs will be performed by a command that you will run in step VII.
    • If you are going to install more than one hotfix (MS02-038, MS02-039, and perhaps a subsequent hotfix), then you should install them in numerical order. MS02-039 specifically replaces a file from MS02-038, so if you install MS02-39 first then you will essentially be uninstalling that hotfix when you install MS02-038 on top of it.
    • There are files and directories mentioned in the instructions that do not appear in your MSDE installation -- it is safe to ignore those files.

     

  7. Log In And Finish

    After you install the hotfixes you should reboot your Timesheet server. Microsoft sometimes omits that suggestion, but Journyx has found it to be a best practice and highly recommended.

    You should be able to open a web browser and log in to Timesheet normally. At this point you have completed the installation of the MSDE SP2 and appropriate hotfixes, and you are done with the upgrade process.

    Journyx strongly recommends that you make a copy of the c:\MSDESP2data and C:\sql2ksp2 directories in a safe place before remove them from the Timesheet server.

    If you encounter any errors or strange behavior in the Timesheet application after this procedure, please contact support@journyx.com. Please mention in that email that you have recently completed this MSDE SP2 installation.

Important Note about Uninstalling

When you uninstall this Timesheet installation you will not be able to follow the standard uninstall process. The new uninstallation process is outlined below:

MSDE SP2 -> Timesheet uninstallation process

  • Backup your database using the backupdb command at the Journyx Timesheet Command Line Prompt.
  • Store that backup file in a safe place off of the server.
  • Use the Add / Remove Programs in Windows Control Panel to remove these applications in the following order:
    • Microsoft SQL Server Desktop Engine(JOURNYXMSDE)
    • Journyx Timesheet

You may receive the following harmless warning when you uninstall Timesheet: "This action is only valid for products that are installed." This happens when the uninstall process attempts to uninstall the original version of MSDE which has been replaced by the upgrade process.