Announcement

Collapse
No announcement yet.

UltraLog and SQL 2000

Collapse
This is a sticky topic.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    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,

    #2
    Try this:

    http://automatedhomeonline.com/HomeS...ftSQL2005.html
    Last edited by Ultrajones; July 5, 2008, 09:02 PM.
    Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

    Comment


      #3
      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,

      Comment


        #4
        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 
        Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

        Comment


          #5
          Many thanks works perfect with the last script.

          Thanks,

          Comment


            #6
            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

            Comment


              #7
              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
              Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

              Comment


                #8
                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?
                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

                Comment


                  #9
                  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 10, 2009, 04:13 AM.
                  Author of Highpeak Plugins | SMS-Gateway Plugin | Blue Iris Plugin | Paradox (Beta) Plugin | Modbus Plugin | Yamaha Plugin

                  Comment


                    #10
                    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
                    Author of Highpeak Plugins | SMS-Gateway Plugin | Blue Iris Plugin | Paradox (Beta) Plugin | Modbus Plugin | Yamaha Plugin

                    Comment


                      #11
                      Bump. UJ - any advice on creating the tables for device status and value changes?
                      Author of Highpeak Plugins | SMS-Gateway Plugin | Blue Iris Plugin | Paradox (Beta) Plugin | Modbus Plugin | Yamaha Plugin

                      Comment


                        #12
                        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 
                        Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

                        Comment


                          #13
                          Is there a new create statement for the syslog tabels for SQL 2008? As I am getting lots of syslog insert errors?

                          Thanks

                          Darren

                          Comment


                            #14
                            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
                            Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

                            Comment


                              #15
                              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

                              Comment

                              Working...
                              X