SM 9.62 - Upgrade IR Expert MS SQL error 42000-574 ?

[Dido]

Recently I've start testing SM 9.62 (upgrade from 9.61) and faced some issues with the IR Expert. sm -upgradeir  returns the following error: No DBMS full text search engine is available.

In the log file there is an error:

2072( 6244) 04/02/2019 18:24:26 RTE E Error: SQL State: 42000-574 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
2072( 6244) 04/02/2019 18:24:26 RTE I Row Number = 1, Column number = -1
2072( 6244) 04/02/2019 18:24:26 RTE E SQL State: 42000-574 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
2072( 6244) 04/02/2019 18:24:26 RTE E API=SQLExecute [in sqmssqlExecOne], Statement=IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = 'SM_FULLTEXT_CATALOG') CREATE FULLTEXT CATALOG SM_FULLTEXT_CATALOG AS DEFAULT
2072( 6244) 04/02/2019 18:24:26 RTE E Additional errors have been written to the log. Contact your system administrator
2072( 6244) 04/02/2019 18:24:26 RTE E Failed to create FULLTEXT CATALOG SM_FULLTEXT_CATALOG AS DEFAULT, IR will not be available
2072( 6244) 04/02/2019 18:24:26 RTE I License table is empty, created initial record
2072( 6244) 04/02/2019 18:24:26 RTE E Error: SQL State: 42000-574 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
2072( 6244) 04/02/2019 18:24:26 RTE I Row Number = 1, Column number = -1
2072( 6244) 04/02/2019 18:24:26 RTE E SQL State: 42000-574 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
2072( 6244) 04/02/2019 18:24:26 RTE E API=SQLExecute [in sqmssqlExecOne], Statement=IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = 'SM_FULLTEXT_CATALOG') CREATE FULLTEXT CATALOG SM_FULLTEXT_CATALOG AS DEFAULT
2072( 6244) 04/02/2019 18:24:26 RTE E Additional errors have been written to the log. Contact your system administrator
2072( 6244) 04/02/2019 18:24:26 RTE E Failed to create FULLTEXT CATALOG SM_FULLTEXT_CATALOG AS DEFAULT, IR will not be available
2072( 6244) 04/02/2019 18:24:26 RTE E No DBMS full text search engine is available.
2072( 6244) 04/02/2019 18:24:26 RTE I System being shutdown by SM from SYSTEM

...

2072( 6244) 04/02/2019 18:24:31 RTE utalloc found 126 potential leaks of 11088 bytes (122880 bytes with overhead)

If I create catalog manually, during the initialization there are couple of errors like this:

1372( 4032) 04/01/2019 10:08:13 RTE I Processing file "sla"...
1372( 4032) 04/01/2019 10:08:13 RTE I Found IR key, Checking the mapped SQL types of its fields...
1372( 4032) 04/01/2019 10:08:13 RTE I Field "description" is mapped to column m1."DESCRIPTION" with SQL type "VARCHAR(4000)"
1372( 4032) 04/01/2019 10:08:13 RTE I Starting IR regen of file "sla"...
1372( 4032) 04/01/2019 10:08:13 RTE I REGEN of file 'sla' is starting
1372( 4032) 04/01/2019 10:08:13 RTE I REGEN of file 'sla' is starting
1372( 4032) 04/01/2019 10:08:14 RTE E Error: SQL State: 42000-574 Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]CREATE FULLTEXT INDEX statement cannot be used inside a user transaction.
1372( 4032) 04/01/2019 10:08:14 RTE I Row Number = 1, Column number = -1
1372( 4032) 04/01/2019 10:08:14 RTE E SQL State: 42000-574 Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]CREATE FULLTEXT INDEX statement cannot be used inside a user transaction.
1372( 4032) 04/01/2019 10:08:14 RTE E API=SQLExecute [in sqmssqlExecOne], Statement=CREATE FULLTEXT INDEX ON dbo.SLAM1 ("DESCRIPTION" LANGUAGE 'English') KEY INDEX SMIR_135AB028D5026F477E8F057D512A58CE1B5B4698 WITH STOPLIST=OFF
1372( 4032) 04/01/2019 10:08:14 RTE E Additional errors have been written to the log. Contact your system administrator
1372( 4032) 04/01/2019 10:08:14 RTE E IR Regen failed.
1372( 4032) 04/01/2019 10:08:14 RTE E Failed to IR regen file "sla".

And IR is not available. 

Any toughts/hints are welcome.

[Luis_G_SM]

Hello

You say that you have IR disable, but where do you have it, what paremeters do you have in the ini and in the cfg related to IR.

this could help on why this is failing.

BR!

[Dido]

There is no IR specific configuration in sm.ini or sm.cfg.

sm.ini:

shared_memory:134217728
log:../logs/sm.log
numberoflogfiles:20
maxlogsize:20971520
system:13080
httpPort:13080
sslConnector:0
httpsPort:13443
#### Optional:
msglog:1
###Instance setup:
ntservice:SM 9.62 Dev
groupname:SM962.DEV
groupmcastaddress:224.0.0.255
groupport:13100
preferredFQHN:smdev.net
#### End optional
################ WEB ###########################
##querysecurity:0
################ IR Expert######################
##ir_asynchronous:1
################ New Anubis ####################
enableAnubisMonitor:1
###############
dao_sessiontimeout:30
################ debug #########################
#rtm:3
#debugdbquery:999
#debughttp:1
sqldictionary:sqlserver
[sqlserver]
sqldb:HPSM_961_Dev

sm.cfg:

#
# Service Manager Server Configuration File
#
# Used by Service Manager service on Windows and smstart script on Unix
# to start the Service Manager server processes.
#
######################################################################################
#
# © Copyright 1994 - 2018 Micro Focus or one of its affiliates.
# All Rights Reserved
#
######################################################################################

#
# start a Service Manager listener
#

sm

# When unable to start the server as a service
# uncomment the following line to produce an output
# file which may contain some information from the
# Java JVM.
#cmd /c "sm" > C:\\process_stdout 2>&1

#
# start background schedulers
#

sm system.start

##Connect-It servlet##
sm -httpPort:13090 -httpsPort:13453 -debugNode:1 -rtm:3 -debugdbquery:999 -log:../logs/connect-it.13090.log

[Dido]

According https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191544(v=sql.105) :

You can use all Transact-SQL statements in an explicit transaction, except for the following statements:

ALTER DATABASE

CREATE FULLTEXT INDEX

ALTER FULLTEXT CATALOG

DROP DATABASE

ALTER FULLTEXT INDEX

DROP FULLTEXT CATALOG

BACKUP

DROP FULLTEXT INDEX

CREATE DATABASE

RECONFIGURE

CREATE FULLTEXT CATALOG

RESTORE

You also cannot use the following:

• Full-text system stored procedures in an explicit transaction. For more information, see Full-Text Search Stored Procedures (Transact-SQL).

• sp_dboption to set database options or use any system procedures that modify the master database inside explicit or implicit transactions.

And this is the sqldebug output:

3152( 4136) 04/03/2019 10:11:00 RTE D sql diff 0.000 total 0.234 call#:3252 --> sqmssqlTransactionRollback:ROLLBACK
3152( 4136) 04/03/2019 10:11:00 RTE D sql diff 0.000 total 0.234 call#:3253 --> _prepare:SELECT SERVERPROPERTY('IsFullTextInstalled') as installed
3152( 4136) 04/03/2019 10:11:00 RTE D sql diff 0.000 total 0.234 call#:3254 --> _describe:SELECT SERVERPROPERTY('IsFullTextInstalled') as installed
3152( 4136) 04/03/2019 10:11:00 RTE D sql diff 0.000 total 0.234 call#:3255 --> sqmssqlSelectSome - EXECUTE:SELECT SERVERPROPERTY('IsFullTextInstalled') as installed
3152( 4136) 04/03/2019 10:11:00 RTE D sql diff 0.000 total 0.234 call#:3256 --> sqmssqlSelectSome - FETCH:SELECT SERVERPROPERTY('IsFullTextInstalled') as installed
3152( 4136) 04/03/2019 10:11:00 RTE D sql diff 0.000 total 0.234 call#:3257 --> sqmssqlSelectSome - FETCH EOF:SELECT SERVERPROPERTY('IsFullTextInstalled') as installed
3152( 4136) 04/03/2019 10:11:00 RTE D sql diff 0.000 total 0.234 call#:3258 --> sqmssqlCommit:COMMIT
3152( 4136) 04/03/2019 10:11:00 RTE D sql diff 0.000 total 0.234 call#:3259 --> sqmssqlExecOne - PREPARE:IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = 'SM_FULLTEXT_CATALOG') CREATE FULLTEXT CATALOG SM_FULLTEXT_CATALOG AS DEFAULT
3152( 4136) 04/03/2019 10:11:00 RTE D sql diff 0.000 total 0.234 call#:3260 --> sqmssqlExecOne - BIND:IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = 'SM_FULLTEXT_CATALOG') CREATE FULLTEXT CATALOG SM_FULLTEXT_CATALOG AS DEFAULT
3152( 4136) 04/03/2019 10:11:00 RTE D sql diff 0.000 total 0.234 call#:3261 --> sqmssqlExecOne - EXECUTE:IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = 'SM_FULLTEXT_CATALOG') CREATE FULLTEXT CATALOG SM_FULLTEXT_CATALOG AS DEFAULT
3152( 4136) 04/03/2019 10:11:00 RTE E Error: SQL State: 42000-574 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
3152( 4136) 04/03/2019 10:11:00 RTE I Row Number = 1, Column number = -1
3152( 4136) 04/03/2019 10:11:00 RTE E SQL State: 42000-574 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
3152( 4136) 04/03/2019 10:11:00 RTE E API=SQLExecute [in sqmssqlExecOne], Statement=IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = 'SM_FULLTEXT_CATALOG') CREATE FULLTEXT CATALOG SM_FULLTEXT_CATALOG AS DEFAULT
3152( 4136) 04/03/2019 10:11:00 RTE E Additional errors have been written to the log. Contact your system administrator
3152( 4136) 04/03/2019 10:11:00 RTE D sql diff 0.000 total 0.234 call#:3262 --> _errorStatement:ROLLBACK
3152( 4136) 04/03/2019 10:11:00 RTE E Failed to create FULLTEXT CATALOG SM_FULLTEXT_CATALOG AS DEFAULT, IR will not be available

I've the feeling that there is something wrong with RTE code preparing/executing SQL transaction that create SM_FULLTEXT_CATALOG.

[NioByte]

Hi Dido,

I managed to workaround this issue by performing the following:

• Open sm.log in a text-editor search for all entries with 'statement='.
  • Take a note of each 'statement=' entry.
• Run each of the SQL statements in Microsoft SQL Server Management Studio manually.
  • Start off with: IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = 'SM_FULLTEXT_CATALOG') CREATE FULLTEXT CATALOG SM_FULLTEXT_CATALOG AS DEFAULT
• Run sm -upgradeir again. This will still fail, but will at least continue past the initial error.
  •  The following may occur:
     The IR sub system was upgraded, but IR Regen failed on/skipped the following files due to some  errors: "KnowledgePak","cm3r","contract","core","help","helptext","incidents","knowledge","knownerror","probcause","probsummary","rootcause","sla","svcDisplay", Please check the output/log messages and manually fix the errors then re-run this command.
• Open sm.log again in a text-editor search for all entries with 'statement='.
  • Take a note of each 'statement=' entry.
  • Run each of these 'statement=' entries in Microsoft SQL Server Management Studio manually.
• Run sm -upgradeir again. This will still fail, but will now show the indexes have already been created, etc.
• Start HP Service Manager if still stopped part of 'sm -upgradeir' failed result.
  • Attempt to perform a text search in each of the Service Manager modules e.g. Change (cm3r), Incident (probsummary), Service Desk (incident).
  • If errors are still experienced,  Delete the old SMIR indexes manually using Microsoft SQL Server Management Studio for each of the relevant tables.
  • Use an SM Fat Client that has access to 'System Definition → Tables'.
• Run ‘Regenerate IR Index’ on each of the affected tables where text search does not work.
• Copy each of the failed statements in the sm.log logfile, based on each appropriate table that has IR Indexes. Then run the failed statements in Microsoft SQL Server Management Studio.
• Attempt a text search again. If it still fails, delete the SMIR indexes again manually in Microsoft SQL Server Management Studio. Then re-run ‘Regenerate IR Index’ and copy/paste the 'statement=' result into Microsoft SQL Server Management Studio.
• Restart Service Manager (optional).
• Full text search works as expected across each of the modules.

I have raised a support case to see what can be done about this issue.

[NioByte]

I thought I would update this thread.

As this issue has been addressed in SM 9.63.

Server

QCCR1E153907

After upgrade to the new IR solution, a user cannot use the new IR feature in an SQL Always ON environment. The fix for this issue updates the original design. There is no error no matter the ODBC Driver is for SQL Server or not.

Source: https://docs.microfocus.com/itom/Service_Manager:9.63/FixedIssues

The 'sm -upgradeir' command now runs without experiencing any MSSQL errors.

Full Text Search works as expected in Service Manager 9.63 post-upgrade.

[mai_tai]

did you fix it or not?

[NioByte]

Hi mai_tai,

The workaround I provided back in April fixes the issue as well as upgrading to SM 9.63.

I have tested both scenarios and they both work fine.

Upgrade to SM 9.63 RTE run 'sm -upgradeir' and no errors occur using MSSQL.