GoldMine Web (GMWEB) - Error, Run SQLcollation statements error when logging into GoldMine Web - Log file states: Run add dependency sql statemens error.,  System.InvalidOperationException: The SQL Server Service Broker for the current database is not en

Version 1

    Details

    [STEPS TO REPRODUCE]
    1. Log into GoldMine Web client

    [RESULT:]
    a. on screen on the client side: Error, Run SQLcollation statements error





    b. The GMConnect.log shows the following error:


    2015-04-10 19:11:14,099 - [INFO] Thread id[8] - Validating user:master
    2015-04-10 19:11:15,615 - [ERROR] Thread id[5] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Run add dependency sql statemens error.,
    System.InvalidOperationException: The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported.  Please enable the Service Broker for this database if you wish to use notifications.

    Server stack trace:
       at SqlDependencyProcessDispatcher.SqlConnectionContainer..ctor(SqlConnectionContainerHashHelper hashHelper, String appDomainKey, Boolean useDefaults)
       at SqlDependencyProcessDispatcher.Start(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& queueService, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart, Boolean useDefaults)
       at SqlDependencyProcessDispatcher.StartWithDefault(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& service, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart)
       at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
       at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg)

    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at SqlDependencyProcessDispatcher.StartWithDefault(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& service, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart)
       at System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults)
       at GoldMine.Models.GmSqlDependency.SqlDependencyRepository.Initialization(String connection_str)
       at GoldMine.Models.GmSqlDependency.SqlDependencyRepository.AddDependency(String sSQL, OnDependencyChangeDelegate OnDependencyChange, String connection_str)
    2015-04-10 19:11:15,693 - [ERROR] Thread id[5] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, Run SQLcollation statements error.,
    GoldMine.Models.ApplicationExceptions.ClientInformationException: Run add dependency sql statemens error.
       at GoldMine.Models.Log.GMLoggerFactory.GMLogger.ErrorClientAndServer(Object customMessage, Exception e)
       at GoldMine.Models.GmSqlDependency.SqlDependencyRepository.AddDependency(String sSQL, OnDependencyChangeDelegate OnDependencyChange, String connection_str)
       at GoldMine.Models.GmSqlDependency.OnDependencyChangeHandler.Subscribe()
       at GoldMine.Models.GoldMineAPI.GoldMineAPIEntry.DatabaseRebuildDependency.SetSqlDependency()
    2015-04-10 19:11:15,693 - [ERROR] Thread id[5] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, Run SQLcollation statements error.,
    GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, Run SQLcollation statements error.
       at GoldMine.Models.Log.GMLoggerFactory.GMLogger.ErrorClientAndServer(Object customMessage, Exception e)
       at GoldMine.Models.GoldMineAPI.GoldMineAPIEntry.DatabaseRebuildDependency.SetSqlDependency()
       at GoldMine.Models.GoldMineAPI.GoldMineAPIEntry.Load(String userName, String password, Boolean isWeb)
    2015-04-10 19:11:15,709 - [INFO] Thread id[8] - user:  signed out
    2015-04-10 19:11:15,709 - [ERROR] Thread id[8] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, Run SQLcollation statements error.,
    GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, Run SQLcollation statements error.
       at GoldMine.Models.Log.GMLoggerFactory.GMLogger.ErrorClientAndServer(Object customMessage, Exception e)


    Resolution

    [CAUSE]
    The Service Broker within Microsoft SQL Server must be enabled for the GoldMine database. This is required also for certain features within GoldMine Premium Edition since GMPE 9.0.x. This is usually by default when a database is created and for a day to day use on an existing GoldMine Premium Edition environment the issue should not occur, but it can occur for example when the database was restored. 

    [RESOLUTION]
    - verify that the Service Broker is enabled for the GoldMine database

    - a possible query for verifying if the Service Broker is enabled (1) or not enabled (0( within SQL Server Management Studio is

    SELECT is_broker_enabled,name FROM sys.databases




    - in order to enable the Service Broker for the GoldMine database and also for further information, please refer to https://msdn.microsoft.com/en-us/library/ms166086.aspx










    Additional Note:

    - in some occasions - especially after restoring a database - it might be sufficient, simply to start GoldMine one time as this will also enable the Service Broker for the restored database again.