How can I log result to MSSQL 2000?

Guest · Post by **Guest** » Wed Sep 18, 2002 4:11 am

Dear Sir:
How can I log result to MSSQL 2000 with hostmonitor ver3.26?
I find Option/advanced logs SQL Query
Insert into hmlog (eventtime, testname, status, reply) VALUES (%DateTime%, '%TestName%', '%Status%', '%Reply%')
Regards;
Steven Kuo
steven_kuo@abit.com.tw

KS-Soft · Post by **KS-Soft** » Wed Sep 18, 2002 9:35 am

You are right, you should use this option. Do you have some problems?

Regards
Alex

mpriess · Post by **mpriess** » Wed Sep 18, 2002 10:24 am

Hi Alex,

This feature works for me if I remove the %DateTime% variable. If I leave it in it gives the following error.

Line1: Incorrect syntax near '9'
Uncolosed quotation mark before the character string.

Thanks for your help,
Mark

KS-Soft · Post by **KS-Soft** » Wed Sep 18, 2002 11:18 pm

Please, enclose %DateTime% variable in quotation marks: '%DateTime%'

Regards
Alex

<font size=-1>[ This Message was edited by: KS-Soft on 2002-09-18 23:18 ]</font>

mpriess · Post by **mpriess** » Tue Sep 24, 2002 11:12 am

Hi Alex,

After enclosing the %DATETIME$ variable with "" I receive the following error:

Return code: 4294967295
[Microsoft][ODBC SQL Server Driver][SQL Server] The name '9/24/2002 10:05:26 AM' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

NOTE: I'm using MS SQL 7.0
Thanks for your help.

KS-Soft · Post by **KS-Soft** » Tue Sep 24, 2002 11:46 pm

So, server does not accept SQL Query like "Insert into hmlog (eventtime, testname, status, reply) VALUES ('9/24/2002 10:05:26 AM', 'my test', 'host is alive', '300 ms')"? Ok, what datetime format it accepts?

Regards
Alex

mpriess · Post by **mpriess** » Thu Sep 26, 2002 2:22 pm

My query above was choking because the first test in my list had a testname called "What's Up Gold Application".
Apparently, when the test button is clicked it chooses the first test that is setup.
It failed because of the ' in What's. So, I'll just remember NOT to name any tests with a '.

Thanks for all your help.

Guest · Post by **Guest** » Fri Sep 27, 2002 7:02 am

Hi
I am sorry to ask as following issues:
1.How to define the field of application data display in my database? And the field type for varchar or number ,date,boolean?
2.Can you tell me how to select the data to my report in MSSQL? Because I viewed the report function,it doesn't provide the format to SQL server.
3.Can you provide the parameter of the application fields to me?Because I am going to use the syntax to run SQL statement to insert data to database.
Await responsing

KS-Soft · Post by **KS-Soft** » Fri Sep 27, 2002 11:06 pm

>1.How to define the field of application data display in my database? And the field type for varchar or number ,date,boolean?

Sorry, I am not quite sure I understand your question. Do you mean how to define list of fields to insert into the log? Just enumerate fields in the SQL Query like "INSERT INTO HMLOG (DATE1, TIME1, TEST_NAME, STATUS, REPLY) VALUES (%Date%, '%Time%', '%TestName%', '%Status%', '%Reply%')". You can use any macro variables those HostMonitor suppors. You can find information about macro variables in help system or on our web site at http://www.ks-soft.net/hostmon.eng/mfra ... .htm#macro

>2.Can you tell me how to select the data to my report in MSSQL? Because I viewed the report function,it doesn't provide the format to SQL server.

Do you need some table in database that will represent current status of the tests? I don't see good solution. Probably you can use HostMonitor's Report Manager to create report in DBF format and start some script to import data from DBF into MSSQL database.

>3.Can you provide the parameter of the application fields to me?Because I am going to use the syntax to run SQL statement to insert data to database.

I hope I answered to this question in 1st paragraph (www.ks-soft.net/hostmon.eng/mframe.htm# ... .htm#macro). Or you are asking about something else?

Regards
Alex