If the complete patch is to change from one individual to another, follow the KB for <Full Patch reassignment>.


Patch changes over 500 lines should be carried out out of hours. A scheduled job has now been set up in SSMS to run the update script at a set time. 

 

The changes are all made in the SQL back-end using SQL Server Management Studio.  You will need a .csv file containing the changes and a SQL script to load the changes into the CX database.

 

Checks are carried out through CX front-end.


Prerequisites: 

  • The requestor must provide a spreadsheet with the following details: (this can be created using the PROP01 report)
    • Asset Reference
    • Previous Patch Detail
    • New Patch Detail (e.g. Housing Officer name)
  • The new patch detail must exist as an item in the User Defined Lookup Type table. 


Steps:

  1. Create Import sheet:
    1. Add the Import sheet must contain 3 columns headed: AssetRef, AssetCharCode and AssetCharDesc - Use this template.
    2. Add the asset references to the AssetRef column. 
    3. Add the name of the individual taking over responsibility of the asset to the AssetCharDesc (this should match the Other Reference of the User Defined Lookup Type table). 
    4. Add the Description from the User Defined Lookup Type table, that corresponds to the Other Reference, to the AssetCharCode column. 
    5. Save the spreadsheet as a .csv and save it to your Import folder in your :C drive. Use a relevant name as this will form the temporary table name in the Database e.g. CHNXXXXPatchUpdate_20260101

Example of CSV: CR7111 Housing Officer Change.csv


2. Update the test database: (Must carry out test steps prior to making the change in live db).

  1. Open SSMS.
  2. Connect to CXTESTSQL01
  3. Right click on CxTest, select Tasks, select Import Flat File:
  5. Navigate to the import file in your :C drive, select it and click next.
  7. Click Next on Preview data then make sure that all the columns are type nvarchar(50)
  9. Click Next and Finish. The data will be loaded in to the table specified. 
  11. Open the SQL script and copy and past into a new query in SSMS. Open new query by right clicking on CXTest and selecting New Query. 
  12. Amend and run the script section by section following the instructions. 
  13. Spot check some of the assets in CX Test front end to ensure they have been updated correctly. 


4. Updating the Live Database.

  1. In SSMS Connect to CXPRODSQL01, open a new query and copy and paste your SQL from the test run into the Live query. We aren't going to run the whole script in live.
  2. Import your temp table again using the same steps as in Test database.
  3. Follow the script up to the point of Updating the Asset Characteristic table. Copy this final section without the Begin Tran, Rollback Tran and --commit and remove all comments. This should look like the below:
  4. In the object explorer of SSMS, open the SQL server agent, open up Jobs and double click on Patch Update. 
  6. Click on Steps in the Job Properties screen. 
  8. Double click on the Run the Update line and paste the asset characteristic table update section of the sql script into the command field. 
  10. Select the Schedules page. Update the schedule so that the schedule type so that it is one time and enabled. Add the current date and set the time to 20:30 so that the script runs out of hours. Select Ok until the screen closes. 


An email will be sent to the BS Analyst Email address to state whether the run was successful or not. In the morning you will need to carry out spot checks in CX live front end to ensure that the import worked correctly. You will also need to go back and drop your temporary table from the Database. 



Notifications

 

If the change relates to Income Officers, copy [email protected] in case it necessitates a change to Rents & Revenue areas.

No other individuals need to know about the change apart from the requestor.