Trouble running SQLH2

Question

Trouble running SQLH2

podgehb

SSC Enthusiast

Points: 135
More actions
April 19, 2005 at 7:38 am

#88240

I am trying to install the "SQL Server Health and History Tool (SQLH2)". The Setup creates a database to store the data, and ou run an exe to collect the data and store it in the database. When I ran the exe it errored. I checked the error log and found the T-SQL that caused the error.
This is a cut-down version of the script that caused the error....
Insert dbo.c_wmi_OS (InstallDate, LastBootUpTime, LocalDateTime)
Values ('11/06/2004', '18/04/2005', '19/04/2005')
And this is the error...
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated."
The problem seems to be that the SQLH2 exe is trying to insert dates that are in the dd/mm/yy date format (Regional Settings set to "dd/mm/yy") into a database that will not accept that format. And it doesn't seem like it is possible to change the database to accept a date like '18/04/2005', as this posting says...
http://msdn.microsoft.com/newsgroups/default.aspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.server&p=1&tid=ed6a6512-c624-4de0-be29-d8c06e4a98de
Because the script is in a compiled exe, I can't edit it, but I do have access to the server and database.
I can't imagine that this Microsoft utility will only work on computers that are using a US date format in there regional setting. Is there anything you can suggest that would help ?
Craig

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

You can use sp_defaultlanguage to change the language to us_english for the login that you are using, run the exe, and then set it back.

You could opt to use sp_configure to change this at the server level as well.

Andy

philcart SSC-Forever Points: 47794 More actions · Answer 2

Craig

If you're using the latest download of SQLH2, you can use the /D parameter to set the date format.

Check the Deployment documentation for details.

--------------------
Colt 45 - the original point and click interface

podgehb SSC Enthusiast Points: 135 More actions · Answer 3

Spot on, Phil ! If only I have read the whole deployment guide I would have had this working straight away.

Thanks

podgehb SSC Enthusiast Points: 135 More actions · Answer 4

Yes, I installed the Performance Collector, but unfortunitely get 2 errors when I run SQLH2.exe. I have emailed sqlh2@microsoft.com so hopefully they can help.

These are the errors from H2log.txt...

20/04/2005 10:22:20 Status: Opening Unit 7 (Products)

20/04/2005 10:22:20 Status: Collecting Win32_Product

20/04/2005 10:22:20 ERROR: Invalid class: SELECT * FROM Win32_Product

20/04/2005 10:22:20 ERROR: Unit 7 failed

20/04/2005 10:22:20 Status: Closing Unit 7

.

20/04/2005 10:22:21 Status: Starting Perf Loader

20/04/2005 10:22:28 ERROR: The network path was not found.

20/04/2005 10:22:28 WARNING: Errors were encountered processing PerfProviders

20/04/2005 10:22:28 Status: Perf Loader finished

If you have any advice, please let me know.

In the SQLH2 Configuration Utility, I set the PerfProvider as "C:\SQLH2\PerfCollector\Data" --- i.e. the data folder that I set up for the Performance collector. Is that right ?

cheers,

Craig

philcart SSC-Forever Points: 47794 More actions · Answer 5

The "Invalid class ..." error is covered in the doco as well. You need to install the WMI provider on the machine that you're gathering data from. In Windows Server 2003 it's not installed by default.

For the PerfProvider, you put then name of the machine that the service is running on. I mentioned to the SQLH2 guys that this was a little confusing. Depending on developer availability, there might be some changes when SQL Server 2005 is released.

--------------------
Colt 45 - the original point and click interface

podgehb SSC Enthusiast Points: 135 More actions · Answer 6

I installed the WMI provider and that sorted out the 1st error --- thanks!

I changed the Perf Provider folder to \\ServerName\SQLH2\PerfCollector\Data and that did't work, so I set up a share and changed the perf provider folder to : \\ServerName\SQLH2PerfCollectorData$

I don't get the same error, but now I get this error...

20/04/2005 14:48:40 Status: Starting Perf Loader

20/04/2005 14:48:40 ERROR: Access to the registry key is denied.

20/04/2005 14:48:40 WARNING: Errors were encountered processing PerfProviders

20/04/2005 14:48:40 Status: Perf Loader finished

I am a member of the administrators group, so I'm not sure why I'm getting this.

Any ideas,

Craig

philcart SSC-Forever Points: 47794 More actions · Answer 7

You just have the name of the server by itself

eg: ServerName not \\ServerName\SQLH2PerfCollectorData$

--------------------
Colt 45 - the original point and click interface

podgehb SSC Enthusiast Points: 135 More actions · Answer 8

ahhh.... yes that did the trick. I have changed the Perf Provider to ServerName, and I get no errors --- thanks.

Thanks for all your help.

By the way, I emailed sqlh2@microsoft.com and got sent a document that was very useful (SQLH2 - FAQ), and was also told...

"I would also made the following change to avoid date format problems on UK locale.

Connect to your Repository DB and change column type for stamp in pt_perf1 from datetime to nvarchar

alter table pt_perf1 alter column stamp nvarchar (24)"

cheers,

Craig

BC-394365 SSC Enthusiast Points: 148 More actions · Answer 9

Hi everyone, I'm experiencing a problem that I cannot find a resolution for. My reports are working fine, I'm collecting the standard SQLH2 data just fine but when the SQLH2 tool tries to import the PerfCollector data I get an error. I cleared out the SQL servers and just add one perf server until I can figure this out.

I'm testing at this point just getting the bugs worked out, I have it running fine on a different setup which is Windows2000 and SQL2000 on but we are upgrading soon and I need to test all of our processes. Something to do with Server2003 and 2000 maybe?

Any thoughts would be appreciated.

Database on SQL2005

Collector and database on same machine which is Server2003.

2/15/2007 9:51:58 AM

2/15/2007 9:51:58 AM Opening Log

2/15/2007 9:51:58 AM SQLH2 ver. V2 2.0.027.0 10/17/05 16:25

2/15/2007 9:51:58 AM Status: Init

2/15/2007 9:51:58 AM WARNING: There are no Targets in the config file

2/15/2007 9:51:58 AM Status: Initializing repository: (local)

2/15/2007 9:51:58 AM Status: Repository Database: SQLH2Repository

2/15/2007 9:51:58 AM INFO: host_id = 2

2/15/2007 9:51:58 AM Status: Starting RUN

2/15/2007 9:51:58 AM Status: Registering Run

2/15/2007 9:51:58 AM INFO: run_id = 19

2/15/2007 9:51:58 AM Status: Starting Targets processing

2/15/2007 9:51:58 AM Status: Targets processing finished

2/15/2007 9:51:58 AM Status: Starting Perf Loader

2/15/2007 9:51:58 AM ERROR: Couldn't find required registry key on Perf Provider

2/15/2007 9:51:58 AM WARNING: Errors were encountered processing PerfProviders

2/15/2007 9:51:58 AM Status: Perf Loader finished

2/15/2007 9:51:58 AM SQLH2 is shutting down

2/15/2007 9:51:58 AM Closing Log

philcart SSC-Forever Points: 47794 More actions · Answer 10

Have you installed the Performance Collector? It is a seperate download available from http://www.microsoft.com/downloads/details.aspx?FamilyID=64983af0-7902-427e-9b41-7c2e8fdcc140&DisplayLang=en

--------------------
Colt 45 - the original point and click interface

BC-394365 SSC Enthusiast Points: 148 More actions · Answer 11

Please correct me if I'm wrong but do I need to install the PerfCollector on each machine that I want to collect data from? I was under the impression that I only had to install the PerfC on one machine.

I edited the file H2PerfConfigFile.xml and added the computers I want to collect from. I then ran the SQLH2 Config utility and add the servers under the Perf section at the bottom. When I look at the file being generated it looks like there is data for both computers.

I'm doing something wrong.

Thanks for the help.

BC-394365 SSC Enthusiast Points: 148 More actions · Answer 12

I think I figured it out.

In the SQLH2 Config utility under PerfCollector just add the one PC that has the collector installed. However in the XML file you add all the computers you want to collect from.

philcart SSC-Forever Points: 47794 More actions · Answer 13

That is just was I was going to suggest. Those errors you were getting are usually seen when the collector is not installed. So if you added all the servers in the SQLH2 config then it was trying to access the service on those machines instead of locally.

--------------------
Colt 45 - the original point and click interface

BC-394365 SSC Enthusiast Points: 148 More actions · Answer 14

I have been running the SQLH2 for a little bit now and for the most part things are working well. I do have a slight problem with the Perf Collector portion and I was wondering if anyone else was getting the following error?

2/21/2007 8:26:52 AM ERROR: Unable to obtain Lock to load perf data

2/21/2007 8:26:52 AM WARNING: Errors were encountered processing PerfProviders

2/21/2007 8:26:52 AM Status: Perf Loader finished

There are about 10 files that need importing but I'm getting the above error. I might consider a manual SSIS job to import the data if SQLH2 keeps doing this.