Skip to content

Troubleshooting & FAQ

Note: Details here are currently Powershell specific and relate only to the API data-flow, not the SSD. We're working on the Python/CLI API related version as this was a more recent development within our project solution. We have included below some anticipated sample/simple questions, but want to build on this with your on-the-ground experiences and questions as you attempt to deploy the API solution in order that we might collaborate on the guidance and help that other LA's might need as they do the same.

Log SSD/API support tickets

How do I check my PowerShell version?

  • Run: $PSVersionTable.PSVersion

How do I check if the SQL Server is accessible?

  • Run: ping YourSQLServerInstance

The script says Invoke-Sqlcmd is not recognised

  • Ensure the SqlServer module is installed: Install-Module -Name SqlServer -AllowClobber -Scope CurrentUser

I'm getting SSL/TLS errors when connecting to SQL Server

  • Add the -TrustServerCertificate parameter to Invoke-Sqlcmd commands in the script.

The script runs but doesn’t send data

  • Ensure $testingMode is set to $false for production.
  • Verify the API token and endpoint URL.

Modify the database details

  • Update the $server and $database variables to match your reporting instance
  • Verify that ssd_api_data_staging table exists alongside the other SSD tables

PowerShell Module Issues

Verify Installation Location

  • The Install-Module command installs the module to a user-specific directory when using -Scope CurrentUser.

  • Check if the module is installed in the expected directory: Get-ChildItem "$env:USERPROFILE\Documents\WindowsPowerShell\Modules\SqlServer"

  • If files exist, the module is installed correctly but may not be loading

  • If no files are found, reinstall the module and ensure no errors occur

Import the Module Manually

  • Try importing the module explicitly: Import-Module "$env:USERPROFILE\Documents\WindowsPowerShell\Modules\SqlServer\SqlServer.psd1"

  • If this works, PowerShell isn't searching the correct module directory

  • If it fails, check if the module files exist in the specified location

Check Execution Policy

  • PowerShell's execution policy might be preventing module imports. Check the policy: Get-ExecutionPolicy

  • If set to Restricted, change it to RemoteSigned temporarily: Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned

Reinstall the Module

  • If the module still doesn’t appear, force a reinstallation: Install-Module -Name SqlServer -Force -AllowClobber -Scope CurrentUser

Check PowerShell Version

  • Ensure you are running PowerShell 5.1 or later: $PSVersionTable.PSVersion

  • If your version is outdated, upgrade to the latest version:

  • Windows 10/11: Install the latest version of PowerShell (7.x) via the Microsoft Store or from the PowerShell GitHub releases

Verify Modules Directory

  • Ensure that PowerShell is correctly searching the user-specific modules directory: $env:PSModulePath -split ';'

  • Look for a path similar to: C:\Users\<YourUser>\Documents\WindowsPowerShell\Modules

  • If the path is missing, add it manually: $env:PSModulePath += ";$env:USERPROFILE\Documents\WindowsPowerShell\Modules"

Use Administrator Privileges

  • If all else fails, try installing the module for all users with admin privileges:
  • Open PowerShell as Administrator
  • Run: Install-Module -Name SqlServer -AllowClobber -Scope AllUsers
  • This installs the module in the Program Files directory, making it accessible to all users

Testing Invoke-Sqlcmd

  • Once the module is installed (or if already installed), verify connectivity to your SQL Server: Invoke-Sqlcmd -Query "SELECT 1 AS Test" -ServerInstance "YourServerName"

Expected Output:

Test 1

Connectivity Issues:

  1. SQL Server Connectivity:

  2. Ensure YourServerName is accessible. Test with: ping YourServerName

  3. SQL Server Instance Name:

  4. If YourServerName is a named instance, include it: Invoke-Sqlcmd -Query "SELECT 1 AS Test" -ServerInstance "YourServerName\InstanceName"

  5. SQL Server Port:

  6. If SQL Server is not running on the default port (1433), include the port: Invoke-Sqlcmd -Query "SELECT 1 AS Test" -ServerInstance "YourServerName,1433"

  7. Firewall Rules:

  8. Ensure your machine has permission to connect to the SQL Server
  9. Check for firewall rules blocking the connection