Since updating to v10.24 the backup logging to MSSQL does not work anymore.
The SQL is unchanged and testing the ODBC data source on the HostMon host is fine.
Testing the Data source in HostMon via Options works fine as well.
The logging itself does not work and throws multiple errors in syslog.
Here are some german examples, shortened and anonymized:
Backup log error. ODBC error code: 4294967295 ([Microsoft][ODBC SQL Server Driver][SQL Server]In der INSERT-Anweisung sind mehr Spalten angegeben als Werte in der VALUES-Klausel. Die Anzahl von Werten in der VALUES-Klausel und die Anzahl von in der INSERT-Anweisung angegebenen Spalten müssen übereinstimmen)
Backup log error. ODBC error code: 4294967295 ([Microsoft][ODBC SQL Server Driver][SQL Server]Falsche Syntax in der Nähe von 'Root'
[Microsoft][ODBC SQL Server Driver][SQL Server]Kein schließendes Anführungszeichen nach der Zeichenfolge ',',LEFT(',499),LEFT(',7999),LEFT(',19),LEFT(',299))')
Thanks in advance
Problems with SQL Backup log since v10.24
The ODBC driver is Microsoft built-in SQL Server driver version 10.00.17763.4974 from 12.10.2023, but that's most likely not the culprit.
The hostmon.ini has the following odbcLogSQLQuery:
Insert into hmlog (eventtime,eventtimestamp,testname,status,statusid,simplestatus,reply,testid,testmethod,testmethodid,testcomment1,testcomment2,testcreatedate,testmodifydate,interval,alertthreshold,hostaddr,folder,fullpath,acknowledgedat,acknowledgedby,ackcomment,httppage,httpcode,httpheader) VALUES ('%DateTime%',getDate(),'%TestName%','%Status%',LEFT('%StatusID%',2),'%SimpleStatus%',LEFT('%Reply%',7999),'%TestID%','%TestMethod%',LEFT('%MethodID%',2),'%CommentLine1%','%CommentLine2%','%CreatedTime%','%ModifiedTime%','%Interval%','%AlertThreshold%','%HostAddr%','%Folder%','%FullPath%','%AcknowledgedAt% ','%AcknowledgedBy%',LEFT('%AckComment%',499),LEFT('%HttpPage%',7999),LEFT('%HttpCode%',19),LEFT('%HttpHeader%',299))
This query worked for years and it did in v10.20 until 8:30 am. I even restored the hostmon.ini from pre-update to make sure the query was exactly the same. The database and the table were not changed and the problem started directly with the upgrade.
For testing purposes I checked the original query from a fresh install / hostmon.ini, created an appropriate table and tested with that query:
Insert into hmlog (eventtime, testname, status, reply, testid, testmethod) VALUES ('%DateTime%', '%TestName%', '%Status%', '%Reply%', %TestID%, '%TestMethod%')
That works flawlessly.
The hostmon.ini has the following odbcLogSQLQuery:
Insert into hmlog (eventtime,eventtimestamp,testname,status,statusid,simplestatus,reply,testid,testmethod,testmethodid,testcomment1,testcomment2,testcreatedate,testmodifydate,interval,alertthreshold,hostaddr,folder,fullpath,acknowledgedat,acknowledgedby,ackcomment,httppage,httpcode,httpheader) VALUES ('%DateTime%',getDate(),'%TestName%','%Status%',LEFT('%StatusID%',2),'%SimpleStatus%',LEFT('%Reply%',7999),'%TestID%','%TestMethod%',LEFT('%MethodID%',2),'%CommentLine1%','%CommentLine2%','%CreatedTime%','%ModifiedTime%','%Interval%','%AlertThreshold%','%HostAddr%','%Folder%','%FullPath%','%AcknowledgedAt% ','%AcknowledgedBy%',LEFT('%AckComment%',499),LEFT('%HttpPage%',7999),LEFT('%HttpCode%',19),LEFT('%HttpHeader%',299))
This query worked for years and it did in v10.20 until 8:30 am. I even restored the hostmon.ini from pre-update to make sure the query was exactly the same. The database and the table were not changed and the problem started directly with the upgrade.
For testing purposes I checked the original query from a fresh install / hostmon.ini, created an appropriate table and tested with that query:
Insert into hmlog (eventtime, testname, status, reply, testid, testmethod) VALUES ('%DateTime%', '%TestName%', '%Status%', '%Reply%', %TestID%, '%TestMethod%')
That works flawlessly.
Thanks for the quick fix, SQL logging is working again. In fact it partially worked before, it just threw lots of errors and dropped some entries. I assume it had to do with the double backslashes in the full path, or?
And yes, I used 14.24.
Sorry for that typo and once again thanks for the fast help.
And yes, I used 14.24.
Sorry for that typo and once again thanks for the fast help.