Upgrade Error from 265 to 266

[hadesfromhell]

Hi there,

Yesterday I tried to upgrade to the newest version 1.2.6 from version 1.2.4 ..
Unfortunatelly the upgrade did not suceed and even trying to install 1.2.4 again, I cannot get the database running again.
I followed the instructions from these posts, but no success.

https://www.netxms.org/forum/oe-oo/1-2-4-gt-1-2-5-upgrade-fail/msg9310/#msg9310
https://www.netxms.org/forum/general-support/netxms-upgrade-to-v1-2-5-mssql-database-upgrade-issue/msg9297/#msg9297

I deleted my [event_log] table, re-created it and the error is still the same ..
I really don't have an idea of what's the problem, I don't have much knowledge in SQL ..

C:\NetXMS\bin>nxdbmgr upgrade -t
NetXMS Database Manager Version 1.2.6

Upgrading database...
Upgrading from version 265 to 266
>>> CREATE INDEX idx_event_log_root_id ON event_log(root_event_id) WHERE root_event_id > 0
SQL query failed ([Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'WHERE'.):
CREATE INDEX idx_event_log_root_id ON event_log(root_event_id) WHERE root_event_id > 0
Rolling back last stage due to upgrade errors...
Database upgrade failed

C:\NetXMS\bin>

[hadesfromhell]

Ok, Seems that I found a workaround ...
I just forced the upgrade .. I don't know how can it impact  ..
For now, it's working back .

C:\NetXMS\bin>nxdbmgr upgrade -X
NetXMS Database Manager Version 1.2.6

Upgrading database...
Upgrading from version 265 to 266
SQL query failed ([Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'WHERE'.):
CREATE INDEX idx_event_log_root_id ON event_log(root_event_id) WHERE root_event_id > 0
Upgrading from version 266 to 267
Upgrading from version 267 to 268
Upgrading from version 268 to 269
Upgrading from version 269 to 270
Upgrading from version 270 to 271
Database upgrade succeeded

C:\NetXMS\bin>

[victor]

Hi!

It will work correctly. You can also execute manually

CREATE INDEX idx_event_log_root_id ON event_log(root_event_id)

This is a bug in upgrade procedure - it tries to create filtered index, which is a feature new to SQL 2008, so it fails on previous SQL server versions.

Best regards,
Victor

[hadesfromhell]

Hi Victor,

Thank you very much !
It's working now perfectly.