www.homeseer.com    
 

Go Back   HomeSeer Message Board > 3rd Party Plug-Ins/Scripts > Plug-ins by Author > Click Here for List of Author Forums > Ultra Scripts and Plug-Ins > UltraLog HSPI

UltraLog HSPI Discussion area for the UltraLog, HomeSeer database logging plug-in.

Reply
 
Thread Tools Display Modes
  #1  
Old September 9th, 2006, 03:00 PM
jonas.hellgren jonas.hellgren is offline
Seer Deluxe
 
Join Date: May 2001
Location: Sweden
Posts: 262
UltraLog and SQL 2000

What is the steps for switching from access to MS SQL 2000

Do I need to create the database and tables manualy?


Thanks,
__________________
Jonas Hellgren, Sweden
HS Pro 3 HP i3 3.3 Ghz, Z-Wave 39+ nodes, DSC, SMS Gateway, Arduino, HStouch
Reply With Quote
  #2  
Old September 9th, 2006, 06:17 PM
Ultrajones's Avatar
Ultrajones Ultrajones is offline
OverSeer
 
Join Date: Jan 2001
Location: Michigan, US
Posts: 5,758
Try this:

http://automatedhomeonline.com/HomeS...ftSQL2005.html

Last edited by Ultrajones; July 5th, 2008 at 10:02 PM.
Reply With Quote
  #3  
Old September 11th, 2006, 04:04 PM
jonas.hellgren jonas.hellgren is offline
Seer Deluxe
 
Join Date: May 2001
Location: Sweden
Posts: 262
I can't get the script to work on my SQL 2000 i get error on line 7 and ......

Should it work on SQL 2000?

Thanks,
Reply With Quote
  #4  
Old September 11th, 2006, 07:36 PM
Ultrajones's Avatar
Ultrajones Ultrajones is offline
OverSeer
 
Join Date: Jan 2001
Location: Michigan, US
Posts: 5,758
Try this:
PHP Code:
/****** Object:  Table [dbo].[tblLog]    Script Date: 8/1/2005 8:47:52 AM ******/
CREATE TABLE [dbo].[tblLog] (
    [
ID] [intIDENTITY (11NOT NULL ,
    [
Log_Date] [datetimeNOT NULL ,
    [
Log_Type] [varchar] (50COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [
Log_Data] [varchar] (255COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblSyslog]    Script Date: 8/1/2005 8:47:52 AM ******/
CREATE TABLE [dbo].[tblSyslog] (
    [
ID] [intIDENTITY (11NOT NULL ,
    [
Syslog_Date] [datetimeNOT NULL ,
    [
Syslog_Facility] [intNOT NULL ,
    [
Syslog_Severity] [intNOT NULL ,
    [
Syslog_Hostname] [varchar] (50COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [
Syslog_Msg_Tag] [varchar] (32COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [
Syslog_Msg_Content] [varchar] (1024COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblSyslogFacility]    Script Date: 8/1/2005 8:47:53 AM ******/
CREATE TABLE [dbo].[tblSyslogFacility] (
    [
Facility_Code] [intNOT NULL ,
    [
Facility_Description] [nvarchar] (255COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblSyslogSeverity]    Script Date: 8/1/2005 8:47:53 AM ******/
CREATE TABLE [dbo].[tblSyslogSeverity] (
    [
Severity_Code] [intNOT NULL ,
    [
Severity_Desc_Short] [nvarchar] (50COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [
Severity_Desc_Long] [nvarchar] (255COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblTemperature]    Script Date: 8/1/2005 8:47:53 AM ******/
CREATE TABLE [dbo].[tblTemperature] (
    [
ID] [intIDENTITY (11NOT NULL ,
    [
Temp_Date] [datetimeNOT NULL ,
    [
Temp_Device] [varchar] (3COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [
Temp_Value] [numeric](100NOT NULL 
ON [PRIMARY]
GO 
Reply With Quote
  #5  
Old September 12th, 2006, 03:45 PM
jonas.hellgren jonas.hellgren is offline
Seer Deluxe
 
Join Date: May 2001
Location: Sweden
Posts: 262
Many thanks works perfect with the last script.

Thanks,
Reply With Quote
  #6  
Old January 20th, 2008, 11:22 PM
TeleFragger's Avatar
TeleFragger TeleFragger is offline
OverSeer
 
Join Date: Jun 2004
Location: Williamstown, NJ
Posts: 4,794
THis worked for me too...
im up and running on SQL2000 now....

~Jeff
__________________
HW - i5 4570T @2.9ghz runs @11w | 8gb ram | 128gb ssd OS - Win10 x64

HS - HS3 Pro Edition 3.0.0.435

Plugins - BLRF 2.0.94.0 | Concord 4 3.1.13.10 | HSBuddy 3.9.605.5 | HSTouch Server 3.0.0.68 | RFXCOM 30.0.0.36 | X10 3.0.0.36 | Z-Wave 3.0.1.190

Hardware - EdgePort/4 DB9 Serial | RFXCOM 433MHz USB Transceiver | Superbus 2000 for Concord 4 | TI103 X-10 Interface | WGL Designs W800 RF | Z-Net Z-Wave Interface
Reply With Quote
  #7  
Old January 24th, 2008, 11:37 PM
Ultrajones's Avatar
Ultrajones Ultrajones is offline
OverSeer
 
Join Date: Jan 2001
Location: Michigan, US
Posts: 5,758
It appears the Microsoft SQL 2005 SP2 needs HOSTNAME\INSTANCE for the Data Source instead of just localhost.

Example:
Code:
Provider=SQLOLEDB;Data Source=MEDIACENTER\SQLEXPRESS;Initial Catalog=HomeSeerDB;Trusted_Connection=yes
Reply With Quote
  #8  
Old January 25th, 2008, 08:51 AM
TeleFragger's Avatar
TeleFragger TeleFragger is offline
OverSeer
 
Join Date: Jun 2004
Location: Williamstown, NJ
Posts: 4,794
Quote:
Originally Posted by Ultrajones View Post
It appears the Microsoft SQL 2005 SP2 needs HOSTNAME\INSTANCE for the Data Source instead of just localhost.

Example:
Code:
Provider=SQLOLEDB;Data Source=MEDIACENTER\SQLEXPRESS;Initial Catalog=HomeSeerDB;Trusted_Connection=yes
because I had 3 versions installed (SQL2000 SP3, SQL2005 Express, SQL2008 CT), then uninstalled them all...

now when I setup I have to use localhost vs the actual server name..weird...

what are the benefits of sql 2005 express over sql 2000?
Reply With Quote
  #9  
Old June 10th, 2009, 05:02 AM
beerygaz's Avatar
beerygaz beerygaz is offline
Super Seer
 
Join Date: Mar 2006
Location: Johannesburg, South Africa
Posts: 1,662
I'd like some assistance if anyone can help. I'm running SQL Express 2008, I've created a database and run the above script to create the tables, UltraLog thinks it's connected to the SQL server (and I see a valid authentication entry in my log) but I still get no entries, and the UltraLog status page just shows 0 Inserts and 300+ failures.

Any ideas on how to to troubleshoot further would be much appreciated.

----- Fixed -----
I had a database permissions error, my HomeSeer user did not have sufficient rights to write to the database. All up and running with SQL.

Last edited by beerygaz; June 10th, 2009 at 05:13 AM.
Reply With Quote
  #10  
Old July 15th, 2009, 03:51 AM
beerygaz's Avatar
beerygaz beerygaz is offline
Super Seer
 
Join Date: Mar 2006
Location: Johannesburg, South Africa
Posts: 1,662
UltraJones, do you have the SQL scripts to create the tables for logging device status and device value changes too please? When I try and enable these features I get errors consistent with the tables not being in my SQL Database
Reply With Quote
  #11  
Old July 30th, 2009, 05:37 AM
beerygaz's Avatar
beerygaz beerygaz is offline
Super Seer
 
Join Date: Mar 2006
Location: Johannesburg, South Africa
Posts: 1,662
Bump. UJ - any advice on creating the tables for device status and value changes?
Reply With Quote
  #12  
Old July 30th, 2009, 07:45 PM
Ultrajones's Avatar
Ultrajones Ultrajones is offline
OverSeer
 
Join Date: Jan 2001
Location: Michigan, US
Posts: 5,758
Here are the create table statement generated by SQL 2008:

PHP Code:
USE [HomeSeerDB]
GO

/****** Object:  Table [dbo].[tblDeviceStatusLog]    Script Date: 07/30/2009 19:42:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE 
[dbo].[tblDeviceStatusLog](
    [
id] [intIDENTITY(1,1NOT NULL,
    [
ts] [datetimeNULL,
    [
hc] [char](1NOT NULL,
    [
dc] [varchar](3NOT NULL,
    [
status] [intNOT NULL,
    [
data1] [intNULL,
    [
data2] [intNULL,
 
CONSTRAINT [pkID2PRIMARY KEY CLUSTERED 
(
    [
idASC
)WITH (PAD_INDEX  OFFSTATISTICS_NORECOMPUTE  OFFIGNORE_DUP_KEY OFFALLOW_ROW_LOCKS  ONALLOW_PAGE_LOCKS  ONON [PRIMARY]
ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 
PHP Code:
USE [HomeSeerDB]
GO

/****** Object:  Table [dbo].[tblDeviceValueLog]    Script Date: 07/30/2009 19:44:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE 
[dbo].[tblDeviceValueLog](
    [
id] [intIDENTITY(1,1NOT NULL,
    [
ts] [datetimeNULL,
    [
hc] [char](1NOT NULL,
    [
dc] [varchar](3NOT NULL,
    [
data1] [intNOT NULL,
    [
data2] [intNOT NULL,
 
CONSTRAINT [pkID1PRIMARY KEY CLUSTERED 
(
    [
idASC
)WITH (PAD_INDEX  OFFSTATISTICS_NORECOMPUTE  OFFIGNORE_DUP_KEY OFFALLOW_ROW_LOCKS  ONALLOW_PAGE_LOCKS  ONON [PRIMARY]
ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 
Reply With Quote
  #13  
Old November 21st, 2009, 08:36 PM
darren-mc's Avatar
darren-mc darren-mc is offline
Seer Master
 
Join Date: Dec 2003
Location: Stafford, UK
Posts: 1,375
Is there a new create statement for the syslog tabels for SQL 2008? As I am getting lots of syslog insert errors?

Thanks

Darren
Reply With Quote
  #14  
Old November 23rd, 2009, 09:01 PM
Ultrajones's Avatar
Ultrajones Ultrajones is offline
OverSeer
 
Join Date: Jan 2001
Location: Michigan, US
Posts: 5,758
The ones listed above should work. Can you send me the create table statements so I can review them? Please send them to ultrajones@hotmail.com.

Regards,
Ultrajones
Reply With Quote
  #15  
Old July 1st, 2012, 07:23 AM
darren-mc's Avatar
darren-mc darren-mc is offline
Seer Master
 
Join Date: Dec 2003
Location: Stafford, UK
Posts: 1,375
Sql 2012

I have tried using these scripts on SQL 2012 but they don't seem to work. Has anyone got any updated DB creation scripts for SQL 2012?

Thanks

Darren
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump


All times are GMT -4. The time now is 05:30 AM.


Copyright HomeSeer Technologies, LLC