In v12.0, when you connect to a SQL Server 2000 database, the following alarm appears:
'Collection 'Database List' failed : Collection 'Database List (protected)' failed : Incorrect syntax near the keyword 'drop'. [156]'
Would this error also prevent other alarms such as the database backup alarm from showing?
Incorrect SQL syntax in file: C:\Program Files (x86)\Quest Software\Diagnostic Server\Agent\conf\Package\sqlserver_spotlight\QS_Databases.sql
In the file C:\Program Files (x86)\Quest Software\Diagnostic Server\Agent\conf\Package\sqlserver_spotlight\QS_Databases.sql , the end of the code looks like this:
from #Quest_database_results ddr
join #Quest_databases_healthcheck ddh on (ddr.dbid=ddh.database_id
drop table #Quest_databases_healthcheck
drop table #Quest_database_results
drop table #Quest_dbcc_opentran
drop table #DBLastBackup
There is a missing close parenthesis at the end of the JOIN condition, which means it's unfinished, and gives an invalid syntax error:
Msg 156, Level 15, State 1, Line 320
Incorrect syntax near the keyword 'drop'.
WORKAROUND:
This error would prevent other database alarms, such as the backup alarm from showing. The workaround steps below will address the error and prevention of other alarms from showing.
If you add the close parenthesis in the C:\Program Files (x86)\Quest Software\Diagnostic Server\Agent\conf\Package\sqlserver_spotlight\QS_Databases.sql file, a resultset will be returned:
from #Quest_database_results ddr
join #Quest_databases_healthcheck ddh on (ddr.dbid=ddh.database_id)
drop table #Quest_databases_healthcheck
drop table #Quest_database_results
drop table #Quest_dbcc_opentran
drop table #DBLastBackup
Once the change is made, please restart the Diagnostic Server service, and this issue will no longer occur
STATUS:
The issue is fixed in the upcoming 12.0.1 release
