Updating PTS

This page discusses the process around updating PTS 5 to a later version of PTS 5.

To arrange an update from PTS 4 to PTS 5, please contact us.

How to arrange a PTS 5 update

1. Review the changelog and make note of the changes in PTS since your installed version
   The changelog is available here: https://prescriptiontracking.co.uk/content/changelog.html
2. Consider, roughly, when you would like the update to take place
   There is typically around an hour of downtime involved when the system will be unavailable, but it's not impossible for the process to take longer if something comes up outside our control.
3. Contact us (we suggest raising a support ticket) to see if we can accomodate this date and time
   There is a small charge for out-of-hours updates.
4. Ensure that a remote access solution is in place for us to connect to your PTS server, and that our support accounts are in good working order
   We may already have instructions on file for remoting into your network and we will test this beforehand. Alternatively we have our own software (based on Teamviewer technology) that, if not blocked by IT, would work just fine. We are happy to use Zoom and similar software, but Microsoft Teams usually does not allow users from an external organisation to take remote control. We won't commit to any update schedule without verifying that the RAS and our support accounts are working.
5. Liaise with your IT department about the need to simultaneously update the PTS Comms Client software
   See the PTS Comms Client Installation page. IT departments can usually quite easily arrange for the software to be installed silently on a schedule. If not, someone with local admin access to the client PCs will need to perform this part of the update manually at roughly the same time as the main application update.
6. Communicate with pharmacy, and perhaps also any external users, that PTS will be unavailable at the agreed time
7. Remain available and contactable during the update in case the TMS technician has an urgent query
   In particular, it's not uncommon for IT to have revoked or reduced our level of access in some way that only becomes apparent during the update.
8. When complete, your technician may invite you to test the system yourself before giving the all-clear
   You should test booking in a prescription, verify that the printer works, progress it to completion using a barcode scanner, and then run an arbitrary report of some kind that includes said prescription. These steps should cover all possible points of failure.
9. Report any problems to your TMS technician as soon as possible
   It is much easier to resolve problems soon after an update than long after. Furthermore in the rare situation that your technician decides to roll back the update, you will lose less work if this decision can be made sooner rather than later.

The update process

This is the process that your TMS Insight technician will follow when updating your PTS application. We are documenting the process here purely for reference. End-user PTS 5 software updates are not covered by a standard support contract and are something we would only consider in very specific circumstances.

In summary, the upgrade will be comprised of updates to the PTS web/application files, an amendment to the PTS database via a SQL update script, updated and/or new report document templates, and optionally an update to the ePrescribing or EPMA service.

Update files

The files and folders included in a PTS update.

The relevant files and folders in a PTS update are:

• ePrescribingService
  The updated files for the ePrescribing or EPMA link.
• Report rdls
  New or updated report template documents.
• SSRS Update Datasource for Reports Script
  The tools for uploading the new reports to your SSRS server and registering them.
• DatabaseUpdate.sql
  The T-SQL script to update the PTS 5 database.
• PTS 5.x.x.x Update.zip
  The updated files for the PTS web application.
• PTSCommsSetup64.msi
  The matching PTS Comms Client installer for this version of PTS.
• Web.config
  The updated web.config file (may be included in the update zip file instead).

Update guide

The aim of this guide is to summarise the steps involved so that a server and/or database administrator could understand and follow them. It will not include enough detail for a novice.

Be sure to do the backups! Not only does it make good sense but you will actually use the backup files as part of the update.

1. Login to the SQL server using SQL Management Studio and locate the PTS 5 database
   Likely to be named "pharmacyDB".
2. TRUNCATE the ELMAH_Error table
   This table can be huge and can massively increase the backup file size.
3. Backup the database
4. Login to the application server and locate the PTSWeb IIS application folder
5. Delete everything from the Contents\Labels folder
6. Create a backup of the PTSWeb folder
   Consider omitting the Content\Sounds folder. It's not important to back this up, and it will be quite large.
7. Open IIS and locate the application pool that is handling the PTS 5 application and stop it
   The downtime starts now.
8. Copy everything from the PTS 5.x.x.x Update.zip folder and drop it into the live PTSWeb directory
   You should have to confirm overwrites; make sure you do so.
9. Copy the new web.config file and drop it into the live PTSWeb directory
   You should have to confirm an overwrite.
10. Locate the old copy of the web.config file in the backup directory and open it in Notepad
11. Locate the <connectionStrings> section and copy it to your clipboard
12. Locate the new web.config file in the live directory and open it in Notepad
13. Locate the <connectionStrings> section and paste the copy of the old <connectionStrings> section from your clipboard and save the changes
    This process is to replace the placeholder connection strings from the PTS update with the working connection strings from your existing installation. Make sure you replace the entire <connectionStrings> section cleanly.
14. Back on the database server, carefully execute the DatabaseUpdate.sql script against the PTS database
15. Monitor the output window for errors and deal with them
    For example older versions of PTS 5 allowed the same username to be assigned to multiple users. This behaviour was eventually changed, but the script cannot apply the fix while any duplicates still exist in the table. Monitor the output window for any affected records and change the username of one of them in the AspNetUsers table. It's better to do this manually, amongst other things to have the opportunity to choose which account to rename, but alternatively this script will automatically append a "B" character to any duplicated usernames:

    UPDATE AspNetUsers SET UserName=UserName+'B' WHERE id NOT IN (SELECT MIN(id) FROM AspNetUsers GROUP BY UserName);

16. Having fixed any issues, execute the update script again and monitor the output window for further errors
    Repeat this process until the script completes with no errors (warnings are expected and can be ignored). The script is failsafe and can be executed repeatedly without issue.
17. Restart the application pool
18. Test that you can login to the application and that it reports the new version number
19. Go to Application Settings in PTS, locate the "URL" setting and make sure it is set correctly
20. Have your contact in pharmacy test the application (not including reports) and sign off on the update
    If pharmacy report no issues they can resume using the system normally at this point.
21. Now is a good opportunity to implement the Database Maintenance Script as part of a SQL maintenance plan, or at least index the database

Updating reports

An example of the ReportSync application in use.

Unblocking a file. This prevents SmartScreen from interfering.

Determining the data source name and location.

1. Establish the Web Service URL
   If you don't have this information to hand you can glean it from the Application Settings within PTS; do a search for "report", and the Web Service URL will be equivalent to the "URL of SQL Reports Service" setting, minus the suffix. e.g. http://ReportServerName:80/ReportServer /reportservice2010.asmx
2. Establish the Web Portal URL
   
   • This typically takes the format of http://ReportServerName:80/Reports. You should be able to figure it out based on the Web Service URL, i.e. simply using a "/Reports" subdomain instead of "/ReportServer".
   • If that doesn't work, you will have to RDP in to the report server directly, and use the Reporting Services Configuration Manager to access the Web Portal URL.
3. Unzip the "Report rdls" and "SSRS Update Datasource for Reports Script" folders
4. Run ReportSync.exe
5. Under "Destination SSRS web service", connect to the reports web service
   
   • Use the Web Service URL you established earlier.
   • You can try the "SSRS Reports Service User" and "SSRS Reports Service Password" settings that PTS is using in its Application Settings. Occasionally however PTS will be configured to use a user account with "read-only" permissions, which aren't sufficient for this task. In this situation you should try domain administrator credentials.
   • It's also important to prefix the username with the domain/server name; you can find out the correct domain via the "SSRS Reports Service Domain" setting.
6. Click Load; you should see PTS's live report folders loaded in the right hand pane
   Note you don't have to use the left hand pane ("Source SSRS web service") for anything, we will be using the Local Path option.
7. At the bottom of the window, under Local Path, click the ellipses button (...)
8. Navigate to the "Report rdls" folder
9. Inside you will find a 2008 folder and a 2016 folder; open 2016
   The 2008 files are for legacy versions of SQL Server that are no longer officially supported by PTS.
10. Select the Prescription folder and click OK
11. In the right hand pane, tick the "PTS Reports" report folder
12. Click Upload, and wait for the process to finish
13. Repeat these steps for "PTS Activity Reports", "PTS CD Reports" and any other folders that have been made available to you
    See the next section if any folders are missing from the server before continuing.
14. You can now close ReportSync
15. Open the "SSRS Update Datasource for Reports Script" folder and locate the UpdatePTSReports.bat file
16. Right-click it and click Properties
17. At the bottom of the General tab, if you see an Unblock tickbox, tick it and then click Apply and OK
18. Repeat this for RS.exe and RS.exe.config
    If you don't do this, Windows SmartScreen may prevent you from proceeding, or the script, when run, may fail to connect to the report server.
19. Right-click UpdatePTSReports.bat and click Edit
20. At the top of the script you will see several variables being "set" with placeholder values, amend these to the correct values for your environment
    
    • The SSRSURL setting should be set to the Web Service URL.
    • You should try the same username and password settings you used with ReportSync earlier.
    • To determine the folder and data source settings, open a web browser and go to the Web Portal URL.
    • You should now see the various report folders listed in the portal. Make sure the folder name settings in the .bat file match the folder names as they exist here.
    • To determine the two data source settings, the data source location will be another folder listed alongside the report folders; make a note of it before clicking on it to reveal the data source name.
21. Save the changes and close Notepad, execute the .bat file and monitor it
    It's important NOT to run this file as an administrator. Doing so will make it unable to find the files based on their relative paths. The output from the batch file will report any issues, but be advised that the red-herring text "command completed successfully" may appear even after an error message. Monitor the output carefully and address any issues.

If a report folder is missing from the server (e.g. PTS CD Reports, PTS Labels)

The Reporting Services Configuration Manager.

The SSRS Web Portal. You will need to go here to implement a new report folder.

If the update includes a whole brand new report category, you won't see it in the right-hand pane and therefore won't be able to upload the new reports to it. To remedy this you simply need to create a new subfolder on the SSRS server. Depending on the version of PTS you're updating from you may already have this issue with the PTS CD Reports folder, which were introduced in PTS v5.0.0.57, or the PTS Labels folder, which were introduced in PTS v5.0.4.0.

1. Based on the report folders that have been supplied to you in the update, make a note of any report folders that seem to be missing from your report server
   e.g. "PTS CD Reports", "PTS Labels".
2. Login to PTS as an administrator and go to Setup > Application Settings
3. Search for "SSRS"
4. Find the settings that relate to the new report folders via their "SSRS Subfolder containing..." settings
   e.g. "SSRS Subfolder containing PTS CD Register Reports".
5. Click on each setting and make a note of its value; this is the name of your new report folder
6. Open a web browser and go to the Web Portal URL
7. If you are prompted to log in, use the same User and Password details found in Application Settings
8. Click the New button at the top right of the screen
9. Choose Folder
10. Enter the name that you established earlier
11. Click Create
12. Return to the updating reports section and retry ReportSync; it should now find the new report folder/s

Updating the ePrescribingService

This is of course only relevant if you are using an ePrescribing or EPMA link.

1. Open Windows Services and stop the "PTS ePrescribing Service"
2. Open Windows Explorer and navigate to the installation path for the service
3. If there are a significant number of log files in the directory now is a good time to delete them
4. Take a backup of the entire folder
   As with the main PTS update, you will use this backup folder later so do not skip this step.
5. Copy the new files into the live folder and confirm all overwrites
6. Navigate to the backup folder you took earlier and open PTSePrescribiing.exe.config in Notepad
   In Explorer you should select View > File name extensions to make this file easier to locate. If file extensions are hidden it may, confusingly, display as PTSePrescribing.exe.
7. Locate the <applicationSettings> section and copy it to your clipboard
   This section will probably be quite big and messy as it likely contains a large unformatted database query in it. Take care to locate the beginning and end of the applicationSettings section and copy it carefully.
8. Locate the new PTSePrescribiing.exe.config file in the live directory and open it in Notepad
9. Locate the <applicationSettings> section and paste the copy of the old <applicationSettings> section from your clipboard and save the changes
   This process is to replace the placeholder application settings from the update with the working settings from your existing installation. Make sure you replace the entire <applicationSettings> section cleanly.
10. Restart the "PTS ePrescribing Service" in Windows Services
11. Monitor the log files in the installation directory for any issues

Updating the PTS Comms Client

Follow the instructions on the PTS Comms Client Installation to update PTS Comms to the version that corresponds to your new PTS installation.

Officially, the PTS Comms Client must version match the main PTS application and as such there is a dedicated download link for each version of PTS. However we are often asked about the intricate details of this policy and whether it is strictly necessary. To answer truthfully the comms software will probably continue to work as it did before, especially if your update was minor. If it will save you a lot of time it's not unreasonable to test this out before committing to updating each PC. The risk of data loss is practically non-existent.

Resetting the PTS 5 Database

Occasionally, and especially as part of a go-live plan, pharmacy may request that the PTS database be reset, or cleared of junk data. What they mean by this is the removal of dummy prescriptions that they have previously created, but with the preservation of the application setup that is also stored in the database.

The following scripts will achieve this. A backup of the database is strongly advised!

TRUNCATE TABLE [PatientDrugs] TRUNCATE TABLE [Patients] TRUNCATE TABLE [PrescriptionNotes] TRUNCATE TABLE [Events] TRUNCATE TABLE [PrescriptionChangeAudits] DELETE FROM [Prescriptions]

Alternate script that provides a cut-off date, for preserving recent data:

DECLARE @CutoffDate AS DATE = '2000-01-01' DELETE FROM [PatientDrugs] WHERE [PatientID] IN ( SELECT [PatientID] FROM [Patients] WHERE [PrescriptionId] IN ( SELECT [PrescriptionID] FROM [Prescriptions] WHERE [Started] < @CutoffDate) ) DELETE FROM [Patients] WHERE [PrescriptionId] IN ( SELECT [PrescriptionID] FROM [Prescriptions] WHERE [Started] < @CutoffDate) DELETE FROM [PrescriptionNotes] WHERE [PrescriptionId] IN ( SELECT [PrescriptionID] FROM [Prescriptions] WHERE [Started] < @CutoffDate) DELETE FROM [Events] WHERE [PrescriptionId] IN ( SELECT [PrescriptionID] FROM [Prescriptions] WHERE [Started] < @CutoffDate) DELETE FROM [PrescriptionChangeAudits] WHERE [PrescriptionId] IN ( SELECT [PrescriptionID] FROM [Prescriptions] WHERE [Started] < @CutoffDate) DELETE FROM [Prescriptions] WHERE [Started] < @CutoffDate

Alternative script that only clears data that relates to inactive prescription types:

DELETE FROM [PatientDrugs] WHERE PatientDrugID IN ( SELECT PatientDrugs.PatientDrugID FROM Prescriptions INNER JOIN PrescriptionTypes ON Prescriptions.PrescriptionTypeID = PrescriptionTypes.PrescriptionTypeID RIGHT OUTER JOIN Patients ON Prescriptions.PrescriptionID = Patients.PrescriptionId RIGHT OUTER JOIN PatientDrugs ON Patients.PatientID = PatientDrugs.PatientID WHERE (PrescriptionTypes.Active = 0) ) GO DELETE FROM [Patients] WHERE PrescriptionId IN ( SELECT Prescriptions.PrescriptionID FROM Prescriptions INNER JOIN PrescriptionTypes ON Prescriptions.PrescriptionTypeID = PrescriptionTypes.PrescriptionTypeID WHERE (PrescriptionTypes.Active = 0) ) GO DELETE FROM [PrescriptionNotes] WHERE PrescriptionID IN ( SELECT Prescriptions.PrescriptionID FROM Prescriptions INNER JOIN PrescriptionTypes ON Prescriptions.PrescriptionTypeID = PrescriptionTypes.PrescriptionTypeID WHERE (PrescriptionTypes.Active = 0) ) GO DELETE FROM [Events] WHERE PrescriptionID IN ( SELECT Prescriptions.PrescriptionID FROM Prescriptions INNER JOIN PrescriptionTypes ON Prescriptions.PrescriptionTypeID = PrescriptionTypes.PrescriptionTypeID WHERE (PrescriptionTypes.Active = 0) ) GO DELETE FROM [PrescriptionChangeAudits] WHERE PrescriptionID IN ( SELECT Prescriptions.PrescriptionID FROM Prescriptions INNER JOIN PrescriptionTypes ON Prescriptions.PrescriptionTypeID = PrescriptionTypes.PrescriptionTypeID WHERE (PrescriptionTypes.Active = 0) ) GO DELETE FROM [Prescriptions] WHERE PrescriptionID IN ( SELECT Prescriptions.PrescriptionID FROM Prescriptions INNER JOIN PrescriptionTypes ON Prescriptions.PrescriptionTypeID = PrescriptionTypes.PrescriptionTypeID WHERE (PrescriptionTypes.Active = 0) ) GO