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 using SQL Server 7.0, please read this important note before continuing.
    • 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. See below for more information about why this is important. 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.

Microsoft SQL Server 7.0 notes

In general, it is OK to use SQL Server 7.0 instead of SQL Server 2000 or the Journyx Internal Database (MSDE) with the important caveat that you must make sure the 'collation name' is set correctly when you install SQL Server 7.0. The collation name cannot be changed after installation, and an incorrect collation will cause the Journyx Timesheet database initialization to fail with an error like "Your current database does not seem to support case-sensitive sorting order."

When you install SQL Server 7.0, you must set the Collation name (Sort Order) to a Case Sensitive sort order, such as SQL_Latin1_General_Cp1_CS_AS. The _CS_ means "case sensitive", so do not confuse it with the case insensitive collation SQL_Latin1_General_Cp1_CI_AS. If you have failed to set the correct collation, you must re-install SQL Server 7.0, or use a different database installation, such as the Journyx Timesheet Internal Database, which is based on SQL Server 2000. Journyx does not know of a way to force SQL Server 7.0 into a different collation without reinstalling the product.

Assuming you have selected the correct Collation name when installing, you can follow all of the above instructions, but you do not need to set the collation name when you create the journyx database.

The Collation Name is important because Timesheet is depending upon the database to provide query results in a certain order and with certain parameters. The most important parameter is that the query must be performed in a case-sensitive manner, but this is not the only parameter. Because SQL Server 7.0 is often set up to return queries in a non-case-sensitive manner some Timesheet customers have experienced database corruption or other issues. We now restrict our installation routine to only databases that will avoid these problems by using the correct Collation Name.