Network Management

Report Writer failed to run report with SQL Error : "Operand type clash: datetime2 is incompatible with int"

When running a report created with Report Writer the report will fail with the SQL Error: Operand type clash: datetime2 is incompatible with int

First published date

3/5/2020 7:55 AM

Last published date

2/12/2025 12:23 AM

Overview

When running a report in the deprecated Report Writer the report will fail with the error below.



ERROR:
SQL Error:
"Operand type clash: datetime2 is incompatible with int"

Product section

Network Performance Monitor

Cause

The main reason of the issue is in Orion database upgrade where some "datetime" columns were moved to the "datetime2" type. As a result, old queries from ReportWriter started failing on new database.
 

Resolution

Report Writer no longer works after an upgrade to Orion Platform 2019.4 or higher as it is now deprecated in future releases. 
You would need to recreate the report using the web-based report writer.
 

Manage and view reports in the Orion Platform
 

Resolution 2

Migrate failing custom SQL reports with the datetime2 error

Starting with Orion Platform 2020.2.5, you can follow these steps to migrate legacy reports with the datetime2 error.

  1. Log in to the server hosting your main polling engine.
  2. Open the LegacyReportMigration.exe.config file and set the CustomSQLEvaluation option to "true".
  3. If the report uses non-default date column, extend then DateTimeColumns setting.
For example, "DateTime,EventTime".
  1. If the date column is wrapped by "[" and "]" within the SQL query, add "\" before the square bracket "[" or "]".
Example: <add key="DateTimeColumns" value="DateTime,\[DateTime\],\[MyTime\],\[TimeOfMyLife\],\[NoTime\]"
  1. Start the Legacy Migration Tool and migrate the report.