Operations Manager 2007 Reporting failed with following error:
=
"MSI (s) (F8!48)
FORMATTER ERROR (":" and "&" not supported in Page Names): Product: System Center Operations Manager 2007
Reporting Server -- Error 26204.Error -2147217900: failed to execute SQL string,
error detail: Incorrect syntax near the keyword 'with'. If this statement is a
common table expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon., SQL key:
Disable_AutoClose.128A4B6A_C876_40B8_83B5_2793414E2138 SQL string: ALTER DATABASE
SCOM-Reporting SET AUTO_CLOSE OFF WITH NO_WAIT"
=
To Troubleshoot the issue, I enabled SQL Profiler trace and captured the
Errors and Warnings group (the most useful data was the “User Error Message”
event). Attentions and Exceptions are also useful.
Issue can be reproducable by using reporting DB name with '-' in.
In this case the reporting DB name was SCOM-Reporting.
You can also test it by creating a DB with ABC-XYZ name and run the following SQL
query:
ALTER DATABASE ABC-XYZ SET AUTO_CLOSE OFF WITH NO_WAIT
This query will only wirg if you put DB name in [] (Square brackets)
ALTER DATABASE
ABC-XYZ SET AUTO_CLOSE OFF WITH NO_WAIT
but in the SCOM setup files that SQL string doesnt contain [] for DB name.