Hi Jeff,
I would like to switch data saved from SQLite to Full SQL. I have a SQL Server and created a database called HS_WeatherXML. In the plug in, I can give it the server\instance and port and then the database, but what I cannot see to figure out is what user is trying to get to the database to write to it. Once I enter the server and database and click the Create Table, it comes back with Table created but when I look in the database, the table is not there.
What is in the HS log is:
Jan-06 15:12:53 weatherXML Error Cannot find the object "current" because it does not exist or you do not have permissions.
Jan-06 15:12:53 weatherXML Error Cannot find the object "current" because it does not exist or you do not have permissions.
Jan-06 15:12:53 weatherXML Error Incorrect syntax near 'ID'.
So how do I know what user the system is trying to use or can I set it in an ini file or somewhere?
Update: When I did a refresh on SQL Server, the providerCounts table was created but the current table was not. When I look at the SQLite table structure I see the first column is ID. So it did try to create the table but the create table syntax is incorrect. I will try to create the table in the SQL2016 syntax and repost what the first column needs to be.
There were a couple issues with using the SQLite version of the create table so I fixed them and make some adjustments to save database space and processing. Here is the code I use to create the SQL Server table:
Next is to output the data I have in the current SQLite database into the SQL and then turn back on the scheduled downloads in WeatherXML.
Had to make weatherSpoken 5000 but you could also use varchar(max). All others are ok at 50 but you could make them 255 also. Also made updatedate as a datetime instead of a varchar or text so I can report using date
Thanks,
Michael
I would like to switch data saved from SQLite to Full SQL. I have a SQL Server and created a database called HS_WeatherXML. In the plug in, I can give it the server\instance and port and then the database, but what I cannot see to figure out is what user is trying to get to the database to write to it. Once I enter the server and database and click the Create Table, it comes back with Table created but when I look in the database, the table is not there.
What is in the HS log is:
Jan-06 15:12:53 weatherXML Error Cannot find the object "current" because it does not exist or you do not have permissions.
Jan-06 15:12:53 weatherXML Error Cannot find the object "current" because it does not exist or you do not have permissions.
Jan-06 15:12:53 weatherXML Error Incorrect syntax near 'ID'.
So how do I know what user the system is trying to use or can I set it in an ini file or somewhere?
Update: When I did a refresh on SQL Server, the providerCounts table was created but the current table was not. When I look at the SQLite table structure I see the first column is ID. So it did try to create the table but the create table syntax is incorrect. I will try to create the table in the SQL2016 syntax and repost what the first column needs to be.
There were a couple issues with using the SQLite version of the create table so I fixed them and make some adjustments to save database space and processing. Here is the code I use to create the SQL Server table:
Code:
CREATE TABLE [current] ([ID] INT NOT NULL PRIMARY KEY IDENTITY, [updatedTime] datetime NULL, [temperature] varchar(50) NULL, [feelsLike] varchar(50) NULL, [todaysHigh] varchar(50) NULL, [todaysLow] varchar(50) NULL, [todaysNormalHigh] varchar(50) NULL, [todaysNormalLow] varchar(50) NULL, [todaysRecordHigh] varchar(50) NULL, [todaysRecordHighYear] varchar(50) NULL, [todaysRecordLow] varchar(50) NULL, [todaysRecordLowYear] varchar(50) NULL, [lastYearsHigh] varchar(50) NULL, [lastYearsLow] varchar(50) NULL, [humidity] varchar(50) NULL, [windSpeed] varchar(50) NULL, [windDirection] varchar(50) NULL, [windGust] varchar(50) NULL, [windDegrees] varchar(50) NULL, [visibility] varchar(50) NULL, [dewpoint] varchar(50) NULL, [barometer] varchar(50) NULL, [barometerDir] varchar(50) NULL, [moonPhase] varchar(50) NULL, [weatherSpoken] varchar(5000) NULL, [locationID] TINYINT NOT NULL, [rainHour] REAL NULL, [rainToday] REAL NULL, [curCondition] varchar(50) )
Had to make weatherSpoken 5000 but you could also use varchar(max). All others are ok at 50 but you could make them 255 also. Also made updatedate as a datetime instead of a varchar or text so I can report using date
Thanks,
Michael
Comment