When being in a separate Contact Set scheduled activities are not visible in the Activity List although they are available on the Pending tab of the record

Version 1

    Details

    When being in a separate Contact Set scheduled activities are not visible in the Activity List although they are available on the Pending tab of the record

    [PREREQUISITES]
    - Having several contact sets available within GoldMine
    - Having several activities scheduled for contact records in several contact sets which are available on the contact record's Pending tab

    [STEPS]
    1. Go To > Activities
    2. Browse to Open Activities
    3. Set Date to All dates and users to All users

    [RESULT]
    - Does not display anything the window remains blank

    [DISCUSSION]
    1. Tools > SQL Query
    2. Copy and paste the query

    select USERID, ACCOUNTNO, ONDATE, COMPANY, DIRCODE from CAL where userid <>'' order by DIRCODE


    3. Query





    Resolution

    [CAUSE]- The behavior is due to different used collations on the GoldMine base database (where the CAL table resides) and the Contact Set database (which is used in a left join query for the Activity list when the Contact Set is open).
    - In a SQL Profile trace the following query can be identified
    SELECT TOP 5000 GMPEBASEDB.DBO.CAL.*  FROM GMPEBASEDB.DBO.CAL  WITH (NOLOCK) LEFT JOIN
    CONTACTSETDB.DBO.CONTACT1 C1  WITH (NOLOCK) ON GMPEBASEDB.DBO.CAL.ACCOUNTNO = C1.ACCOUNTNO  
    WHERE USERID IS NOT NULL AND USERID > ''

    - The above results with the following error when submitted in a separate New Query window of Microsoft SQL Server Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
    [GENERAL INFORMATION ABOUT Microsoft SQL Server collations]
      - For GoldMine in general ALL databases and tables and applicable fields should have the same collation which should be consistent also with the SQL Server itself within Microsoft SQL Server and SQL Server Management Studio.  - Related to GoldMine a SQL DBA should verify the collations of all items which are important for GoldMine - Server Collation, GoldMine Database Collations incl. Table and Field Collations for the GoldMine databases as they should be consistent the same and GoldMine Technical Support usually recommends SQL_Latin1_General_CP1_CI_AS as collation.   - The set Microsoft SQL Server collation can be verified in SQL Server Management Studio 1. Right Click on the SQL Server >> Properties 2. Tab General 3. Field Server Collation on the right pane   - The collation of the database can be verified in SQL Server Management Studio 1. Browse to the database in question 2. Right Click on the Database > Properties 3. Tab General 4. Field Collation on the right pane  - Each table within a database may also have a different collation and this can be verified in SQL Server Management Studio 1. Browse to the database in question 2. Browse to the table in question 3. Right Click on the table > Properties 4. Tab Extended Properties 5. Field Collation on the right pane  - Additionally also each column (depending on data type may have an own collation which can be verified in SQL Server Management Studio 1. Browse to the database in question 2. Browse to Tables and open the Table via the + and open also the Columns tree (also via the + in front of the Columns entry) 3. Browse to the field in question > for example CONTACT1.CONTACT 4. Right Click on the specific field column for example CONTACT > Properties 5. Tab General 6. Field Collation on the right pane   - As simply changing the collation on the database level (for example via the Right Click on Database > Properties > Option tab and change the collation) will NOT change existing data within the database, it is absolutely recommended that any related task should be performed or guided by a Microsoft SQL DBA to make sure that also tables and columns are changed as otherwise other related issues may occur in GoldMine.   [RESOLUTION - 3 ALTERNATIVES] Please keep in mind that changing a collation is outside of GoldMine Technical Support boundaries and the following information is only provided as-is without any warranty of any kind  A. Alternative A - changing collation of database + rebuild of Contact Set with prior deletion of not real data containing tables HISTORY and HISTORY_ITEM- Most likely the shortest way to adjust a single CONTACT SET (it has not been verified but may also work for a GoldMine base database) in the same manner as another related database or the overall Microsoft SQL Server collation   1. Make sure that all users are logged out of GoldMine and all services related to GoldMine are shut down (for example GoldSync) 2. Make sure that all users are logged off or have closed all applications with an integration into GoldMine (for example GMME, Outlook, Word etc) 3. Make sure to have a full running backup of your GoldMine databases 4. in SQL Server Management Studio browse to the specific Contact Set database 5. Right Click > Properties 6. Tab Options 7. Change in the Collation drop down to the desired collation (either as the other related database or the SQL Server collation) 8. OK 9. Now follows a tricky point on this short way as the HISTORY (contains amount how many recently viewed items shall be stored) and the HISTORY_ITEM (contains the recently viewed items for this Contact Set for the users) need to be deleted. There should be no problem and impact in doing so, as theses tables do not contain contact or important information >> 9.a Right Click on HISTORY > Delete > OK 9.b. Right Click on HISTORY_ITEM > Delete > OK 10. Start GoldMine as a Master user in MAINTAIN mode, means on the Login screen 10.a. enter Username (a master user) 10.b. enter password 10.c. press CTRL key and click OK 11. The Contact Set Databases window will appear where you see at left the Maintain Button 12. Highlight the Contact Set database in question and press the Maintain button 13. Current Contact Set Files (of course if trying the same for GoldMine base tables ALL database files need to be selected) >> Next 14. Check Rebuild and Pack the database files (press OK on the upcoming warning message that SQL tables need not be rebuilt ….) + check Sort the database files + check Verify the data and synchronization information >> Next 15. Finish (as you should be anyway the only user being logged into GoldMine) 16. after the rebuild finished start GoldMine and verify the behavior   - From GoldMine Technical Support testing there should be no error messages appearing and also the Activity list or Graphical Calendar should now display proper information again     B. Alternative B - Rehosting the GoldMine database and / or Contact Set into a new SQL database with the proper collation- Another possibility / alternative would be to create a new SQL database with the correct SQL collation which can be used for this specific case and the contact set or may help in getting the GoldMine main database into the correct / server collation as again it would be necessary to have a consistent collation for all GoldMine databases which should be also consistent with the Server collation. In the following process the GoldMine database will be rehosted into a new database shell.   - The rough steps are: 1. in SQL Server Management Studio > in Database section > Right Click > New Database 2. provide a proper Database name and change to tab Options 3. in the Collation drop down (the default is on <server default> select the identical collation as the AX_GMSM database > OK (you may want to verify that the collation is set properly by Right Click on database > Properties > Collation field) 4. Make sure that all users are logged out of GoldMine and all services related to GoldMine are shut down (for example GoldSync) 5. Make sure that all users are logged off or have closed all applications with an integration into GoldMine (for example GMME, Outlook, Word etc) 6. Make sure to have a full running backup of your GoldMine databases 7. Verify and document the details a. Help > About > System (most interesting are GoldMine Files and Contact Files) b. Tools > Databases > Database Alias Manager (necessary all GoldMine database table alias which shall be changed) c. Tools > Databases > Open Database (the GoldMine Contact set table alias which will be changed) 8. Tools > Databases > Open Databases > select the proper Contact Set which needs to be rehosted (if this is for a separate contact set it might be any entry) if this is to ‘convert’ the GoldMine base database then this MUST be the same database as the GoldMine Base tables (Help >> About >> System >> GoldMine Files and Contact Files must be identical now) 9. Tools > Databases > New Database 10.a. If a single Contact Set needs to be rehosted check ONLY the Create or Copy data for Contact Set tables > Next, 10.b. If instead the complete GoldMine base database needs to be rehosted check Create or Copy data for Contact Set tables + Create or copy data for base GoldMine tables (please keep in mind that this process may take much longer e.g. for large Mailbox tables etc) 10.c Next 11. New Alias Button 12. New Alias 12.a. Provide new Alias name 12.b. Provide Host name where the new created database from step 2.+3. resides 12.c. Provide Database name from created database from step 2.+3. resides 12.d. provide Login and SQL password 12.e. Test Connection 12.f. OK > OK 13. Select the new created alias in the create a new Contact Database (if applicable also select the new created alias in the Please select the database alias for the base GoldMine tables) > Next 14. Copy data from the currently active database > Next 15. Provide a Contact Set Code > Next > Finish 16. At the end of the rehost process a message  will appear depending on the settings in step 10.
    - If you are not sure what those messages mean then recommended to press NO 17. You have to logon again (as master into GoldMine) 18. Tools > Options > Login tab 18.a. In GoldMine database = Make sure to have the proper alias set, if the intention was also to convert the base tables of GoldMine and step 10.b was chosen >> select the new created database alias, otherwise make sure that the proper original GoldMine database entry is still available and set 18.b. If applicable set also the Default Contact database although users may change this also via Tools >> Databases >> Open Database 18.c. OK (it might be necessary to login into GoldMine again), this should be done again with Master rights 19. Verify the behaviour and correctness of data etc. 20. After confirming that all data and behaviour is (still) correct > clean up your Open databases, Database Aliases and MS SQL databases 20.a. Tools > Databases > Open Database >> Make sure to delete the former GoldMine contact sets which are now obsolete via Highlight > Delete > Yes 20.b. in case you are using synchronization: The Contact Set File Code MUST be set to the original File Code (see documentation from step 7.c. 20.c. Tools > Databases > Alias Manager > Make sure to delete the former GoldMine contact sets which are now obsolete via Highlight > Delete Alias > Yes > OK 20.d. in SQL Server Management Studio take offline or delete the obsolete database with the wrong collation or archive it  - Additional recommendations for alternative B:1. Verify and document the details a. Help > About > System (most interesting are GoldMine Files and Contact Files) b. Tools > Databases > Alias Manager c. Tools > Databases > Open Database 2. For all networked clients they should have a REALINI referral in their local GM.INI BUT it should be checked that they are referring to the correct database alias via locally opening GoldMine > Help > About > System) 3. Also the first synchronizations should be verified if applicable 4. If either other applications (on the MS SQL server e.g. reporting server or maybe also other applications like Crystal reports rely on the original GoldMine database name. This NEEDS to be discussed prior internally as to overcome this the full process CAN be done twice which means in the second attempt it must be done with the exact original Database name and File Code with prior deleting the original GoldMine database C. Alternative C - A Microsoft SQL DBA to perform all necessary actions only on the back end   - A totally unsupported way which can be done by a Microsoft SQL DBA is to change the Database collation via the back end.

    - As GoldMine Technical Support cannot assist in such an attempt we can only refer to Microsoft’s webpages for changing the
    collation of a database e.g. http://msdn.microsoft.com/en-us/library/ms174269.aspx but as mentioned above it will require
    also to alter all already existing tables and applicable field within the database.   - Also the following article may help and provide further information about the necessary steps for changing the database / table collation. http://blog.sqlauthority.com/2008/12/20/sql-server-change-collation-of-database-column-t-sql-script/