English (United States)
Call us! 512-349-0334 or (877) INDUSOFT

InduSoft Web Studio Forums

Find additional forum posts in in the Forum Archives. For assistance view the Forum Frequently Asked Questions.

Microsoft SQL serverexpress
Last Post 15 May 2019 06:00 PM by G Shearer. 4 Replies.
Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Dave#JACMFGINCUser is Offline
New Member
New Member
Posts:3


--
09 May 2019 11:19 AM
    Hello,

    This is my first time trying to use Microsoft Sql Express Database. I have successfully tested the connection, but when I run the project the DB is not created. I intend to install this on a windows 7 embedded system. Also the Trend Control Objects are not trending.

    Any thoughts on this is very appreciated.

    Thank you
    Dave
    Dave#JACMFGINCUser is Offline
    New Member
    New Member
    Posts:3


    --
    14 May 2019 10:20 AM
    [UPDATE]

    So far with testing I have data being placed into a sql database, however the auto creation of the table in the Database using Indusoft 8.1 SP3, has not been successful. If I manually create a table first in sql express, then the table will begin to populate when I run the project with a trend object set up to sample every second. However, due to this, I cannot get the batch function to work because it needs to create a Batch history table, and that is also not working yet. Has anybody had this issue, or have any Ideas what may be happening? Thanks Dave
    G ShearerUser is Offline
    Senior Member
    Senior Member
    Posts:250


    --
    14 May 2019 06:37 PM
    Hi Dave,

    I have had the same issue when trying to record alarms and events to a database.
    Exactly the same problem, except that I use Ingres, not SQL Express.
    As far as I could tell, this was because the Indusoft SQL/data types used to create tables was not fully compatible with Ingres requirements.

    I overcame this, as you have, by manually creating the two tables needed, which Indusoft then populated ok.

    Have you tried using LogWin to show any SQL errors when the table creation is attempted?
    I have a feeling that nothing was reported in LogWin but it would be worth checking.
    I know that LogWin was useful in helping to refine column data type requirements, as insert attempts failed until the data types matched exactly.

    My reading of the Batch History operation described in the Technical Reference is that only a single batch history table is used.
    So manual creation of a batch history table should be all that is required.
    The database field descriptions are include in the Technical Reference.

    Regards,
    Greg Shearer
    Dave#JACMFGINCUser is Offline
    New Member
    New Member
    Posts:3


    --
    15 May 2019 03:24 PM
    Thank you G Shearer for your response.

    These are the messages I get when Indusoft tries to auto create the table in Sql Express:

    Database: Error: Invalid object name 'BatchHistory'.<ConStr: Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=TrendData; Data Source=DAVE-ELITEBOOK\SQLEXPRESS><Table: BatchHistory><SQL: SELECT [Group_Number], [Batch_Name], [Start_Time], [Start_Time_ms], [End_Time], [End_Time_ms], [Pri_Table], [Sec_Table], [Description], [Deleted], [Last_Update], [Last_Update_ms], [Bias] FROM [BatchHistory] WHERE [Group_Number] = @Value0 AND [Batch_Name] = @Value1 AND [Deleted] = @Value2 ORDER BY [Start_Time] ASC, [Start_Time_ms] ASC>[CMD_QUERY] [Connection String: 'Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=TrendData; Data Source=DAVE-ELITEBOOK\SQLEXPRESS', Table: 'BatchHistory']

    These are the error messages after I manually create the table:

    Database: Error: Invalid column name 'Start_Time'.
    Invalid column name 'End_Time'.
    Invalid column name 'Last_Update'.<ConStr: Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=TrendData; Data Source=DAVE-ELITEBOOK\SQLEXPRESS><Table: BatchHistory><SQL: SELECT [Group_Number], [Batch_Name], [Start_Time], [Start_Time_ms], [End_Time], [End_Time_ms], [Pri_Table], [Sec_Table], [Description], [Deleted], [Last_Update], [Last_Update_ms], [Bias] FROM [BatchHistory] WHERE [Group_Number] = @Value0 AND [Batch_Name] = @Value1 AND [Deleted] = @Value2 ORDER BY [Start_Time] ASC, [Start_Time_ms] ASC>[CMD_QUERY] [Connection String: 'Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=TrendData; Data Source=DAVE-ELITEBOOK\SQLEXPRESS', Table: 'BatchHistory']

    Database: Error: Invalid column name 'Start_Time'.
    Invalid column name 'End_Time'.

    Not Sure what is going on, Indusoft doesn't like it even if I manually create the table.

    Any insight is very much appreciated.

    Thank you
    Dave
    G ShearerUser is Offline
    Senior Member
    Senior Member
    Posts:250


    --
    15 May 2019 06:00 PM
    Nothing too obvious.

    The description of the field names when using a database, specifically show Start_Time and End_Time but no millisecond (_ms) fields, so you could try removing those columns from your manually created table. Alternatively, if you didn't include them … you could try adding them

    When manually building the Alarm History database table, millisecond fields are required … but these aren't used in the 'Proprietary' data file format.
    However, they are clearly indicated in the documentation of the database columns.
    This may have been overlooked in the BatchHistory documentation?

    Also, the Last_Update and Bias columns aren't specified in the BatchHistory database table structure … but a Deleted column is?

    There certainly appears to be some inconsistency between the errors shown and the specified database table structure.
    You may need to try adding/removing columns to match the list shown in the errors you are seeing.
    If data types aren't clear, you may need to clarify with Indusoft support … or use trial and error.

    PS!!!
    I've just found that in the AlarmHistory table I created (which worked fine) there are two additional columns Al_Event_Time and Al_Event_time_ms which ARE NOT SHOWN in the Technical Reference documentation! I suspect that I included these columns after receiving runtime errors similar to those you are seeing.

    Greg
    You are not authorized to post a reply.