• Recent
    • Tags
    • Popular
    • Register
    • Login

    Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.

    Radix IoT Website Mango 3 Documentation Website Mango 4 Documentation Website Mango 5 Documentation Website

    MSSQL 2008

    Mango Automation Installation
    3
    19
    7.4k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • JoelHaggarJ
      JoelHaggar
      last edited by

      Sorry, I wanted to know if it would start up on MSSQL but with a clean database rather than doing the conversion from Derby.

      Joel.

      1 Reply Last reply Reply Quote 0
      • T
        turboblade
        last edited by

        Sorry, my mistake. This is it running with a clean database.

        INFO  2013-11-20 21:56:47,912 (com.serotonin.util.properties.ReloadingProperties.checkForReload:142) - (env) Found updated file(s) at [C:\Documents and Settings\Administrator.WINDXPGST1\Desktop\Mango\classes\env.properties, C:\Documents and Settings\Administrator.WINDXPGST1\Desktop\Mango\overrides\properties\env.properties]. Reloading properties 
WARN  2013-11-20 21:56:49,692 (com.serotonin.m2m2.Main.getProperties:607) - Module C:\Documents and Settings\Administrator.WINDXPGST1\Desktop\Mango\web\modules\_save: module.properties not found in module directory. Module not loaded. 
INFO  2013-11-20 21:56:49,708 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'dglux', v1.0.3 by Infinite Automation Systems Inc. 
INFO  2013-11-20 21:56:49,708 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'dojo', v1.7.3 by Serotonin Software 
INFO  2013-11-20 21:56:49,708 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'graphicalViews', v1.4.1 by Serotonin Software 
INFO  2013-11-20 21:56:49,911 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'internal', v1.4.0 by Serotonin Software 
INFO  2013-11-20 21:56:49,927 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'maintenanceEvents', v1.4.0 by Serotonin Software 
INFO  2013-11-20 21:56:49,942 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'modbus', v1.4.2 by Serotonin Software 
INFO  2013-11-20 21:56:49,958 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'pointLinks', v1.4.0 by Serotonin Software 
INFO  2013-11-20 21:56:49,974 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'reports', v2.0.0 by Serotonin Software 
INFO  2013-11-20 21:56:49,989 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'scheduledEvents', v1.4.0 by Serotonin Software 
INFO  2013-11-20 21:56:50,005 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'scripting', v1.0.0 by Serotonin Software 
INFO  2013-11-20 21:56:50,020 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'sqlds', v1.4.0 by Serotonin Software 
INFO  2013-11-20 21:56:50,036 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'sstGraphics', v1.0.1 by Serotonin Software 
INFO  2013-11-20 21:56:50,036 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'virtualDS', v1.4.0 by Serotonin Software 
INFO  2013-11-20 21:56:50,036 (com.serotonin.m2m2.Main.loadModules:371) - Loading module 'watchlists', v1.4.0 by Serotonin Software 
INFO  2013-11-20 21:56:51,270 (com.serotonin.m2m2.db.BasePooledProxy.initializeImpl:36) - Initializing pooled connection manager 
ERROR 2013-11-20 21:56:53,723 (com.serotonin.m2m2.Main.main:93) - Error during initialization 
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ alter table watchListUsers add constraint watchListUsersFk2 foreign key (userId) references users(id) on delete cascade; ]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Introducing FOREIGN KEY constraint 'watchListUsersFk2' on table 'watchListUsers' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:406)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:427)
	at com.serotonin.m2m2.db.BasePooledProxy.runScript(BasePooledProxy.java:71)
	at com.serotonin.m2m2.db.BasePooledProxy.runScript(BasePooledProxy.java:90)
	at com.serotonin.m2m2.db.DatabaseProxy.runScriptFile(DatabaseProxy.java:212)
	at com.serotonin.m2m2.watchlist.WatchListSchemaDefinition.newInstallationCheck(WatchListSchemaDefinition.java:19)
	at com.serotonin.m2m2.db.DatabaseProxy.newDatabaseCheck(DatabaseProxy.java:175)
	at com.serotonin.m2m2.db.DatabaseProxy.initialize(DatabaseProxy.java:84)
	at com.serotonin.m2m2.Lifecycle.databaseInitialize(Lifecycle.java:445)
	at com.serotonin.m2m2.Lifecycle.initialize(Lifecycle.java:159)
	at com.serotonin.m2m2.Main.main(Main.java:87)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Introducing FOREIGN KEY constraint 'watchListUsersFk2' on table 'watchListUsers' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:775)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:676)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:649)
	at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:266)
	at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:420)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)
	... 10 more
INFO  2013-11-20 21:56:53,785 (com.serotonin.m2m2.db.BasePooledProxy.terminate:108) - Stopping database
        1 Reply Last reply Reply Quote 0
        • JoelHaggarJ
          JoelHaggar
          last edited by

          Ok, thanks. I'll look into this and get back to you.

          Joel.

          1 Reply Last reply Reply Quote 0
          • T
            turboblade
            last edited by

            @JoelHaggar said:

            Ok, thanks. I'll look into this and get back to you.

            Joel.

            Thanks very much for your help

            1 Reply Last reply Reply Quote 0
            • T
              turboblade
              last edited by

              There are a few third party options for converting MYSQL to MSSQL. But I assume this wont help since even a clean database isn't working properly.

              1 Reply Last reply Reply Quote 0
              • T
                turboblade
                last edited by

                Any further developments on this. I find it hard to believe
                that MS sql has not been successfully used by anyone yet.

                I am currently running Mango from a virtual XP PC. Do you think this could cause issues?

                1 Reply Last reply Reply Quote 0
                • JoelHaggarJ
                  JoelHaggar
                  last edited by

                  The issue for upgrading from Derby to MSSQL is because of syntax issues in the code.

                  The issue for just straight up using MSSQL as a clean install is because it looks like the Watchlist module he is using has some database design issues. We are aware of this and plan to resolve these issues. If you don't need the watch list module and can live with a clean install you could remove the Watchlist and it should work. You can export your configuration from Derby and then once on a clean install on MSSQL you can import the configuration. Sorry about the inconvenience, evidently we don't have anyone using MSSQL at the moment. I will let you know when the issues are resolved.

                  Thanks,

                  Joel.

                  1 Reply Last reply Reply Quote 0
                  • T
                    turboblade
                    last edited by

                    Thank you for getting back to me. What exactly is the watch list module used for?

                    Will I be able to transfer my dglux design across to the new database

                    1 Reply Last reply Reply Quote 0
                    • JoelHaggarJ
                      JoelHaggar
                      last edited by

                      With the Watch List you can organize a list of data points to watch in real time and generate a combined chart. Yes you will be able to use your DGLux dashboards.

                      Joel.

                      1 Reply Last reply Reply Quote 0
                      • T
                        turboblade
                        last edited by

                        OK i have been able to start a new install of mango successfully with an MSSQL database. The only problem now is the GUID doesnt match my license. How can I fix this? Hopefully i wont have to purchase a new license.

                        Also, would the MSSQL convert work for the already established Mango installation if I disabled the Watch List module or did this only work because it is a fresh install?

                        1 Reply Last reply Reply Quote 0
                        • T
                          turboblade
                          last edited by

                          ok another query. How can i copy my DGLux installation across from my other Mango GUID. I tried copying the entire DGLux folder to the ...\web\modules folder but now I have no DGLux at all in Mango.

                          1 Reply Last reply Reply Quote 0
                          • T
                            turboblade
                            last edited by

                            Ok here I am now using a clean database and I am presented with the following error when I open the data sources page:

                            Server error: PreparedStatementCallback; bad SQL grammar [SELECT dp.id,dp.data,dp.xid,dp.dataSourceId,dp.name,dp.deviceName,dp.enabled,dp.pointFolderId,dp.loggingType,dp.intervalLoggingPeriodType,dp.intervalLoggingPeriod,dp.intervalLoggingType,dp.tolerance,dp.purgeOverride,dp.purgeType,dp.purgePeriod,dp.defaultCacheSize,dp.discardExtremeValues,dp.engineeringUnits,ds.name,ds.xid,ds.dataSourceType FROM dataPoints AS dp join dataSources ds on ds.id = dp.dataSourceId ORDER BY dp.deviceName, dp.name OFFSET ? ROWS FETCH NEXT ? ROWS ONLY]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'OFFSET'.
Server error: PreparedStatementCallback; bad SQL grammar [SELECT id,xid,name,dataSourceType,data FROM dataSources ORDER BY name OFFSET ? ROWS FETCH NEXT ? ROWS ONLY]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'OFFSET'.

                            Also, when I open the beta alarms page I am presented with the following error:

                            Server error: PreparedStatementCallback; bad SQL grammar [SELECT evt.id,evt.typeName,evt.subtypeName,evt.typeRef1,evt.typeRef2,evt.activeTs,evt.rtnApplicable,evt.rtnTs,evt.rtnCause,evt.alarmLevel,evt.message,evt.ackTs,evt.ackUserId,evt.alternateAckSource,u.username,(select count(1) from userComments where commentType=1 and typeKey=evt.id) as cnt ,ue.silenced FROM events AS evt left join users u on evt.ackUserId=u.id left join userEvents ue on evt.id=ue.eventId WHERE evt.rtnTs IS NULL AND evt.rtnApplicable IN (?) AND evt.ackTs IS NULL AND evt.alarmLevel >= 1 ORDER BY evt.activeTs DESC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'OFFSET'.
                            1 Reply Last reply Reply Quote 0
                            • JoelHaggarJ
                              JoelHaggar
                              last edited by

                              Part of the GUID is the file path to the MA_HOME folder. Make sure you are using the exact same folder path as before and your GUID should remain the same. I think for now you should use MYSQL while we sort out the issues with MSSQL.

                              Joel.

                              1 Reply Last reply Reply Quote 0
                              • T
                                turboblade
                                last edited by

                                ok I will use it for now. Can you please explain how to copy my existing DGLux dashboard to a new Mango installation

                                1 Reply Last reply Reply Quote 0
                                • phildunlapP
                                  phildunlap
                                  last edited by

                                  Depending on your DGLux licensing, you can either save the dashboard to disk and reopen it elsewhere, or you can directly copy the dgml files over. They can be found in MANGO_HOME/web/modules/dglux/web/dgml

                                  1 Reply Last reply Reply Quote 0
                                  • First post
                                    Last post