VERIFIED SOLUTION i

Resolve - Upgrading DW and PSR databases to Portrait HQ version 2.1 incurs a timeout error

Product Feature: Installation
 

Issue

When upgrading to Portrait Interaction Optimizer v5.6 or Portrait Dialogue v6.1 it is necessary to run the CreatePortraitDatabases.ps1 PowerShell script so that the PSR and DW databases are upgraded.
However, the script will sometime produce a timeout error as shown in the screen shot below:
User-added image
The upgrade to the new version is not complete without this step being corrected.

Cause

The cause of this issue is a known Microsoft problem (523996) with PowerShell where it fails to apply the correct default SQL query timeout when making a call to SQL Command.
As a result of this, the specific statement will timeout after just 30 seconds. This is a problem when upgrading large implementations where some of the individual database upgrade scripts can take more than 30 seconds to complete.

Resolution

UPDATED: April 19, 2017
The resolution is to manually add a large query timeout value to the SQL requests. This can be done as follows:
  • On the server being upgraded navigate to the following folder: "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\PortraitHQ\DbModules\DbUtils\"
  • Note that there is a single file called DbUtils.psm1.
  • Make a back up of this file so that you can revert back to it if necessary.
  • Open the file in a text editor such as Notepad++.
  • Perform a search and replace within the file, replacing all instances of "Invoke-Sqlcmd" with "Invoke-Sqlcmd -querytimeout 65534 " (without the quotes).
  • Save the file and exit the text editor.
  • Re-run the database upgrade scripts.
You should see a handful of warning messages (because parts of the upgrade have already been done), but no timeout errors.