How can I log result to MSSQL 2000?

Guest · Posted: Wed Sep 18, 2002 4:11 am Post subject:

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 · Joined: 03 Apr 2002 Posts: 12807 Location: USA

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

Regards
Alex

mpriess · Joined: 02 Jul 2002 Posts: 112 Location: Arizona, USA

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 · Joined: 03 Apr 2002 Posts: 12807 Location: USA

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

Regards
Alex

[ This Message was edited by: KS-Soft on 2002-09-18 23:18 ]

mpriess · Joined: 02 Jul 2002 Posts: 112 Location: Arizona, USA

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 · Joined: 03 Apr 2002 Posts: 12807 Location: USA

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 · Joined: 02 Jul 2002 Posts: 112 Location: Arizona, USA

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 · Posted: Fri Sep 27, 2002 7:02 am Post subject:

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 · Joined: 03 Apr 2002 Posts: 12807 Location: USA

>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/mframe.htm#actions.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#actions.htm#macro). Or you are asking about something else?

Regards
Alex