How do I update the databases for CCH® Fixed Assets Manager?

The following should be performed by your SQL Database Administrator.
  1. Verifying your current user data (fadata) database version.
    • The Fixed Assets Manager databases contain a System table where the version information is stored.
    • To view the version information for a database:
      1. Open Microsoft® SQL Server® Management Studio.
      2. In Management Studio, expand the database server where the databases reside.
      3. Open the Databases folder and expand fadata.
      4. From the left pane, double-click Tables.
      5. Right-click dbo.System table, and select Select top 1000 rows.
      6. The database version displays in the Version column.
Warning: If you do not have 2014.03 as the database version, more than one script will need to be ran to update the database to 2015.01. For Instance, if the database you are running is 2014.01, then a script needs to be ran for each version in between the current and new release so therefore a script for 2014.02, 2014.03 need to be ran and then 2015.01 using the same steps provided below.
  1. Updating the user data (fadata) database version.
    • Network Installations can have multiple user databases. If you have multiple user databases, you must update all databases.
    • The following script needs to be applied to all existing Fixed Assets Manager user data databases.
    • The script is located in the FAPROG sub folder where FAM was installed.
      • FADATA_Schema_Upd_201501.sql
    • Run this script once for each user database:
      1. Open Microsoft® SQL Server® Management Studio.
      2. In Management Studio, expand the database server where the databases reside and select the Fixed Assets Manager user database (fadata) that needs to be updated.
      3. Select File > Open > File... and then open FADATA_Schema_Upd_201501.sql
        • Note: Verify the database name is selected in the database name list. If the user database is not selected, select the user database (fadata) before proceeding to the next step. Do not run these scripts on the system (fasys) database. If the user database name was changed to something other than FADATA, the second line of the update script applied needs to be modified or removed.
        • For example: if the user database name was changed to TAXFADATA, the second line of each update script needs to be removed or modified as follows:
          • Use taxfadata
      4. After the SQL file opens, click Execute on the toolbar.
  2. Drop and recreate the system database (fasys).
    • You must drop and recreate your fasys database:
      1. Open Microsoft SQL Server Management Studio.
      2. In Management Studio, expand the database server where the FASYS database resides.
      3. Expand the Databases folder on the database server.
      4. Select the fasys database and right-click.
      5. Select Properties.
      6. Select the Files page.
      7. Record the Path of the fasys.mdf data file.
      8. Close the Properties dialog.
      9. Right click the fasys database again, select Delete.
      10. Click OK on the Delete Object dialog.
    • Now you attach the populated fasys database:
      1. Copy the system data file, fasys.mdf from the installation image under /FAM/DATABASES to the location noted in step 7 above.
        • Note: Before attempting to attach the database, remove the read-only attribute from fasys.mdf.
      2. Open Microsoft SQL Server Management Studio.
      3. In Management Studio, expand the database server where the database will reside.
      4. Select the Databases folder on the database server and right-click.
      5. From the list, select Attach.
      6. In the 'Databases to attach' field, click Add and browse to the location where your system data file (fasys.mdf) is located.
        • Note: The 'Attach as' field displays the default database name. Do not change the system data filename.
      7. In "fasys database details" section, highlight the fasys_log.ldf file and click Remove.
      8. Click OK.
  3. Grant rights to the fasys database.
    • User rights must be re-assigned to the fasys database.
      1. Open Microsoft® SQL Server® Management Studio.
      2. Expand the database server where the databases reside, select Security > Logins.
      3. Right click on each Fixed Assets Manger user login, or the FAM Group if you setup a users group containing the logins of all user of the application (Recommended) and select Properties.
      4. Go to the User Mapping page.
      5. In the 'Users mapped to this login' section, select fasys. In the 'Database roles for FASYS', select db_datareader.
      6. Remember if you are not assigning rights to a User Group, you must repeat steps 3 through 6 for each user of the application.
  Solution Tools
  Attachments
 Solution Id 000172540/sw46334
 Direct Link
To provide feedback on this solution, please login.

Your feedback about this article will help us make it better. Thank you!

Need more help? Visit our online support to submit a case.