NetXMS upgrade to v1.2.5 MSSQL database upgrade issue

[bdefloo]

Hi,

Just for future reference of anyone having the same problems as we did upgrading to v1.2.5 on MS SQL 2008. We upgraded from v1.2.4 on a 32bit Windows 2003 server. When upgrading the database I got the following error:

C:\NetXMS\bin>nxdbmgr.exe upgrade
NetXMS Database Manager Version 1.2.5

Upgrading database...
Upgrading from version 265 to 266
SQL query failed ([Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot c
reate index. Object 'event_log' was created with the following SET options off:
'ANSI_NULLS'.):
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

Most likely, this was because we've been upgrading the same NetXMS instance since v1.0.13 or older. The only solution we found was to rename the event_log table, and to recreate it with ANSI_NULLS on:

USE [netxms]
GO

/****** Object:  Table [netxms].[event_log]    Script Date: 01/11/2013 10:37:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [netxms].[event_log](
	[event_id] [bigint] NOT NULL,
	[event_code] [int] NOT NULL,
	[event_timestamp] [int] NOT NULL,
	[event_source] [int] NOT NULL,
	[event_severity] [int] NOT NULL,
	[event_message] [varchar](255) NULL,
	[root_event_id] [bigint] NOT NULL,
	[user_tag] [varchar](63) NULL,
PRIMARY KEY CLUSTERED
(
	[event_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Probably you could then move all the data back to the new table, but we didn't really have any need for it so we just started with an empty table.

Hope this helps anyone coming across the same issue.

Kind regards,
bdefloo

[Lotan]

Thanks! It worked.

Although, I had to change CREATE TABLE [netxms].[event_log] to CREATE TABLE [event_log] for it to work.