GMWEB Error, Run SQLcollation statements error, while browsing to a Contact - Conversion failed when converting date and/or time from character string.

Version 1

    Details

    GMWEB Error, Run SQLcollation statements error, while browsing to a Contact - Conversion failed when converting date and/or time from character string.

    [STEPS TO REPRODUCE]
    1. Log into GoldMine Web
    2. CONTACTS
    3. Open an impacted record

    [RESULT]



    furthermore the CONTACTS page remains blank.

    The GMConnect.Log file shows
     
    2017-07-13 14:00:13,105 - [ERROR] Thread id[22] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, Run SQLcollation statements error.,
    System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
       at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
       at System.Data.SqlClient.SqlDataReader.Read()
       at QueryBuilder.Entity`1._GetResult(ICustDatabase database, Boolean CheckIfEntityCanBeAdd)

    2017-06-22 14:57:52,424 - [ERROR] Thread id[9] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, Run SQLcollation statements error.,
    System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
       at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
       at System.Data.SqlClient.SqlDataReader.Read()
       at QueryBuilder.Entity`1._GetResult(ICustDatabase database, Boolean CheckIfEntityCanBeAdd)


    Resolution

    [CAUSE]

    From the log files it is not straight away  visible why it would bring this error message but in a SQL trace it will show  the last query for drilling down on the contact record as

    SELECT    [CONTSUPP].[ACCOUNTNO] as [ContactProfile__accountno],  [CONTSUPP].[LASTDATE] as [ContactProfile__lastdate], [CONTSUPP].[NOTES]  as [ContactProfile__notes], [CONTSUPP].[PHONE] as  [ContactProfile__detail_id], [CONTSUPP].[CONTACT] as  [ContactProfile__detail_name], [CONTSUPP].[CONTSUPREF] as  [ContactProfile__reference], isnull( cast ( SUBSTRING([CONTSUPP].[CITY],  9, 8) as DateTime ), [CONTSUPP].[LASTDATE]) as  [ContactProfile__detail_date], [CONTSUPP].[CITY] as  [ContactProfile__db_city_fld], [CONTSUPP].[TITLE] as  [ContactProfile__field1], [CONTSUPP].[LINKACCT] as  [ContactProfile__field2], [CONTSUPP].[COUNTRY] as  [ContactProfile__field3], [CONTSUPP].[DEAR] as [ContactProfile__field4],  [CONTSUPP].[FAX] as [ContactProfile__field5], [CONTSUPP].[ZIP] as  [ContactProfile__field6], [CONTSUPP].[EXT] as [ContactProfile__field7],  [CONTSUPP].[STATE] as [ContactProfile__field8], [CONTSUPP].[MERGECODES]  as [ContactProfile__field9], [CONTSUPP].[ADDRESS1] as  [ContactProfile__field10], [CONTSUPP].[ADDRESS2] as  [ContactProfile__field11], [CONTSUPP].[ADDRESS3] as  [ContactProfile__field12], [CONTSUPP].[recid] as [ContactProfile__id],  [CONTSUPP].[LASTUSER] as [ContactProfile__last_user] FROM  [dbo].[CONTSUPP] [CONTSUPP]  WHERE ([CONTSUPP].[RECTYPE] = 'P') AND  (([CONTSUPP].[ACCOUNTNO] = 'B7050416563474518CIT'))


    Running this query separately in SQL Server Management Studio showed the same related error
    Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting date and/or time from character string.


    So the following conversion from above complete query isnull( cast ( SUBSTRING([CONTSUPP].[CITY], 9, 8) as DateTime ) will  not work, so there should be a wrong value in the CITY which is the CREATION date of the email address or the Web site of a contact record.

    Usually the format should be something similar like MASTER  20151214 1:57pm but it may show for example as May  4 2017  3:31PM

    It is unknown why the data may have been stored like this,  this is something the customer/user may remember, but below are the steps how  to resolve the behavior,


    [RESOLUTION]
    While  a change from the back end by simply overwriting the CONTSUPP->CITY  field with a valid value would work we strongly recommend and only support in regards of  possible synchronizations that the data is 'corrected' via the front  end.

    1. Open GoldMine and browse to the identified contact record  (for example by verifying with the below query)
    2. Click into the Email field / or Web Site field
    3. Highlight the primary email address / Web Site entry
    4. The best is to mark the email address and copy it via CTRL+A > CTLR+C into the Windows clipboard
    5. Delete Button
    6. OK (if applicable confirm the deletion)
    7. Click into the E- mail / Web Site field again
    8. Add the E-mail Address / Web Site again from within GoldMine
    9. OK
    10. Verify in GoldMine Web (GMWEB) as now the record should open a desired without an error message


    [ADDITIONAL INFORMATION]

    For a verification (This is provided without any warranty) the following query should help to identify such cases.

    select  rectype, contact, CITY, zip, ACCOUNTNO, recid from CONTSUPP WHERE  ISDATE(SUBSTRING([CONTSUPP].[CITY], 9, 8))!=1 and (contact like  'e-mail%' or contact like 'Web site') and rectype = 'P' order by  rectype, recid

    So now if it has been identified that  these are really a lot of entries and the customer may not be able to take care about this via the GoldMine front end the further information is provided as as, without any warranties and officially unsupported and should be only done

    a. After having a full running backup of the GoldMine database
    b. After it has been verified that the impacted records are ONLY E-mail Address and Web Site entries
    c.  That the customer is not synchronizing (no Outlook Sync, no Riva Sync,  No GoldMine Sync and not any other maybe add on application sync) as  changes may be ignored on remote systems or even revert the values from  the remote systems  on the app server's database

    The  customer needs to agree that this is done on his own risk and that they  understand that there are no related TLOG entries created.

    If  the customer agrees to this the following back end update query would replace the impacted records  with a sample value from a working record and as a usual value (which is due to mass update a wrong creation date information). This query which again should be  ONLY run after making sure that there is a full running backup available would be

    Update contsupp set City = 'MASTER  20170504 9:01am ' where recid in (select recid from CONTSUPP WHERE  ISDATE(SUBSTRING([CONTSUPP].[CITY], 9, 8))!=1 and (contact like  'e-mail%' or contact like 'Web site') and rectype = 'P' )

    Running this query should bring the exact amount row(s) affected) as the previous SELECT query