Support
Microsoft SQL Server and Timesheet

 

Journyx provides an "Internal Database" call MSDE (Microsoft SQL Server Desktop Edition.) MSDE is based on SQL Sever 2000 and is functionally equivelent for most purposes. You may use either the provided MSDE database or you may use your own external SQL Server database. The rest of this document describes how to set up your external SQL Server database.

The Journyx Internal Database can coexist with another SQL Server 2000 installation or another MSDE installation, but there are some known issues with conflicting service pak levels. If your existing SQL Server 2000 or MSDE installation is on a service pak level other than the latest, then you should install the latest service pak before installing Timesheet with the Internal Database.

External SQL Server instructions

  1. Make sure you have read the general instructions here.
  2. Install Microsoft SQL Server if not already installed.
    • Install SQL Server 2000. Make sure TCP network support is included in the setup.
    • If installing for the first time, you may wish to change the default collation (sorting order) to 'case sensitive' (SQL_Latin1_General_Cp1_CS_AS). If you have already installed SQL Server, you may change the collation for individual databases when you create them. (See below.)
    • That assumes you are using the Windows-1252 character set (which Microsoft calls Latin-1.) This is appropriate for Western Europe and the Americas. If your users require a different character set, please choose it now. Make sure you select the Case Sensitive (CS) version. You may wish to also select an "Accent Sensitive" (AS) collation in addition to CS. See the main external database page for more details about chosing a character set.
  3. Create the Journyx Timesheet Database
    • Open the Microsoft SQL Server Enterprise Manager program.
    • Expand "Microsoft SQL Servers" icon in the left-hand pane to display sub-tree for this DB server. Find the database instance you wish to use and expand its icon. Typically this may be called "(local) (Windows NT)".
    • Select 'Databases' and right-click on it.
    • select 'New Database'.
    • Choose a name for the Journyx Timesheet database. Usually you will want to use journyx.
    • Important: If your users are in the Americas or Western Europe, select SQL_Latin1_General_Cp1_CS_AS for the "Collation name" option. You must choose the correct Collation name or the Journyx Timesheet database setup script will fail with a message like: "Your current database does not seem to support case-sensitive sorting order." You cannot change the collation after the database is created. If your users require a different character set other than Latin-1, make sure to pick the case-sensitive version of your desired character set. Accent-sensitive (AS) is optional but recommended.
    • In the second tab (Data Files), make the initial size of the main database file at least 50 mb. The exact initial size is not important as long as the database is set to "autogrow."
      Initial size can be estimated by multiplying 1.2MB by the number of users who will be keeping Time that you will have over the course of the year and adding 10MB to that number. This does not take into account the size of expense or mileage records, so if your paradigm is to have 1:1 time to expense or mileage records, for example, you will want to double the size.
    • If you wish, you may wish to change the options on the third tab ("Transaction Log"). Typically an administrator will place the transaction log file on a different disk and controller than the actual database, to improve performance. This is completely optional.
    • Default values are preferred for the rest of the options on this screen.
    • Click OK to create the database
  4. Create Database User
    Please Note: Journyx strongly recommends that you do not use the default 'sa' account that is built in to SQL Server.
    • Expand server icon to display sub-tree for this DB server.
    • Expand the SECURITY folder
    • Select 'Logins'
    • Right-click and select 'NEW LOGIN'.
    • Fill in the desired login name. Usually you will want to use journyx, or the same name as the database space you created above.
    • Under Authentication, be sure that GRANT ACCESS is selected
    • Change the method to SQL Server Authentication and fill in the desired password.
      Please Note: Special characters, such as ~!@#$%^&*(){}_+-={}|[]\:";'<>?,./ are not supported within the SQL DB user account. If you use one of these characters in the password then you will see an error message that your database collation is incorrect, even if your database collation is correct. Please only use letters (uppercase or lowercase) and numbers in your password.
      Later, you will give this password to the Journyx Timesheet database setup program.
      Note: SQL Authentication is required. Journyx Timesheet does not currently support connecting to the database via "NT Authentication."
    • Change the Default Database to be journyx or whatever you named the database space that you created above.
    • Click the Database Access tab
    • Select the journyx database (or whatever you named it) by checking the box to the left of the name
    • Grant the user db_owner right to this database
    • Click OK to add this user. You will be prompted to confirm the password.
    • At this point the database is setup and ready to be used by Journyx Timesheet. You can exit the enterprise manager program.
  5. Follow the rest of the general setup instructions here.