ODBC Logging to SQL Server

cbraatz · Post by **cbraatz** » Thu Apr 04, 2002 11:05 am

What are the details of the database needed to log all activity to sql. Do you have a db for us to load. How about the specs so I can create my own. Thanks Alex!

KS-Soft · Post by **KS-Soft** » Thu Apr 04, 2002 12:49 pm

cbraatz,

Format of database table is entirely up to you. You can specify SQL query to insert record to the log. So, format of table must be corresponding to your SQL query.

Just for example you can create table (hmlog) with 1 date field (named date1), and 4 string fields (named: date1, time1, testname, status, reply). To insert records use SQL query like:
Insert into hmlog (date1, time1, testname, status, reply) VALUES (%Date%, '%Time%', '%TestName%', '%Status%', '%Reply%')

Regards
Alex

simonl · Post by **simonl** » Thu Apr 04, 2002 3:05 pm

Hi Alex, I try to specify more fields to my sql query, but it only except that field which you provide on example. I want to save in table all field from log. It's possible or not?.

KS-Soft · Post by **KS-Soft** » Thu Apr 04, 2002 3:48 pm

Yes, its possible, but its depend on your database, table, query.
You can try to use "Test" button on "Advanced Logs" page in the Options dialog. When some error occurs, HostMonitor will display error message from your ODBC driver. So, you will know where is mistake.

Regards
Alex

cbraatz · Post by **cbraatz** » Thu Apr 04, 2002 4:06 pm

When logging to sql server the date amd time field show up with the date as 1900-01-01. The time however is correct.
Thanks for your fast responses.
Craig

KS-Soft · Post by **KS-Soft** » Fri Apr 05, 2002 2:35 pm

As I understand you have one field in database which represented date&time?
What macro variable for this field do you use in SQL query?
%DateTime%? Or %Time%? Must be %DateTime%

Regards
Alex

cbraatz · Post by **cbraatz** » Fri Apr 05, 2002 3:44 pm

Thanks Alex. I used the following for the sql query:
Insert into hmlog (time, testname, status, reply) VALUES ('%DateTime%', '%TestName%', '%Status%', '%Reply%')

KS-Soft · Post by **KS-Soft** » Fri Apr 05, 2002 4:53 pm

By default HostMonitor uses your system date format. Perhaps your SQL server (or ODBC driver) doesn't like this format?
I see two ways to test this:
- change type of "time" field to string.
- set "Use fixed date&time formats" option (on Miscellaneous page in the Options dialog) and specify date format supported by your SQL server.

Regards
Alex

simonl · Post by **simonl** » Tue Apr 09, 2002 8:28 am

Alex

I understand that output from Host Monitor to odbc database connections depend of which table structure I specify in query, but I have to know how I can specify field name from Host Monitor log for example: %date%, %testname%, %status%, %reply% are working find but when I use %lasttesttime% or %comment% or all other columns from log file I got zero output to the my table. Please, provide the right syntax for all columns name from Host Monitor log file.
Thank you. I

KS-Soft · Post by **KS-Soft** » Tue Apr 09, 2002 1:37 pm

Old macro variables (supported by HostMonitor 1.xx and 3xx): http://www.ks-soft.net/hostmon.eng/actions.htm#macro
New macro variables (version 3.xx only): http://www.ks-soft.net/hostmon.eng/news.htm#NewCol

But if the program does not insert any records into your table, it means you SQL server doesn't like SQL query. Use "Test" button (beside SQL query field in the Options dialog) to see answer from SQL server.
Also make sure you use ('') for text fields like '%TaskComment%', '%Status%'.

Regards
Alex

simonl · Post by **simonl** » Wed Apr 10, 2002 7:33 am

Alex,
Thank you very much.

KS-Soft · Post by **KS-Soft** » Wed Apr 10, 2002 11:42 am

You are welcome