000038756 - RSA Archer Admin Dashboard Population Feed faulted - Error converting data type varchar to datetime

Document created by RSA Customer Support Employee on May 6, 2020
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000038756
Applies ToRSA Product Set: RSA Archer
RSA Product/Service Type: RSA Archer
RSA Version/Condition: 6.6.0.2 and above, 6.7.x
 
IssueArcher Admin Dashboard Population Feed faulted with Error converting data type varchar to datetime due to locale and date format mismatching on SQL Database Server, Operating System and RSA Archer.
SQL Server if configured to use US_English as default language, SQL Server uses date format mdy by default. This will cause Admin Dashboard Population Feed faulting when RSA Archer is configured to Locale in non U.S format.

Archer.ArcherTech.JobFramework.Job.YYYYMMDD.xml Error:

<E2ETraceEvent xmlns="http://schemas.microsoft.com/2004/06/E2ETraceEvent">
    <System xmlns="http://schemas.microsoft.com/2004/06/windows/eventlog/system">
        <EventID>3</EventID>
        <Type>3</Type>
        <SubType Name="Error">0</SubType>
        <Level>2</Level>
        <TimeCreated SystemTime="2020-01-05T13:26:51.6714675Z" />
        <Source Name="ArcherTech.DataFeed" />
        <Correlation ActivityID="{331646cb-b943-440d-9d8c-445452641c3b}" />
        <Execution ProcessName="ArcherTech.JobFramework.Job" ProcessID="5716" ThreadID="1" />
        <AssemblyVersion>6.6.200.1024</AssemblyVersion>
        <Channel />
        <Computer>SERVER</Computer>
    </System>
    <ApplicationData>
        <TraceData>
            <DataItem>
                <TraceRecord Severity="Error" xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord">
                    <TraceIdentifier>ArcherTech.DataFeed</TraceIdentifier>
                    <Description>Unexpected error occurred while executing data feed.</Description>
                    <AppDomain>ArcherTech.JobFramework.Job.exe</AppDomain>
                    <ContextData>
                        <LogReferenceId>010520-132651-6714</LogReferenceId>
                        <DataFeedId>1</DataFeedId>
                        <DataFeedName>Admin_Dashboard_Population_Feed</DataFeedName>
                        <DataFeedHistoryId>341249</DataFeedHistoryId>
                    </ContextData>
                    <Exception>
                        <ExceptionType>System.Data.SqlClient.SqlException, System.Data, Version=ip_address, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType>
                        <Message>Error converting data type varchar to datetime.</Message>
                        <Source>.Net SqlClient Data Provider</Source>
                        <ContextData>
                            <HelpLink.ProdName>Microsoft SQL Server</HelpLink.ProdName>
                            <HelpLink.ProdVer>13.00.5081</HelpLink.ProdVer>
                            <HelpLink.EvtSrc>MSSQLServer</HelpLink.EvtSrc>
                            <HelpLink.EvtID>8114</HelpLink.EvtID>
                            <HelpLink.BaseHelpUrl>http://go.microsoft.com/fwlink</HelpLink.BaseHelpUrl>
                            <HelpLink.LinkId>20476</HelpLink.LinkId>
                        </ContextData>
                        <StackTrace>   at System.Data.SqlClient.SqlConnection.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&amp; dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task&amp; task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task&amp; task, Boolean&amp; usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at ArcherTech.DataFeed.Transporters.DbQueryInfo.PerformQuery(NetworkCredentialWrapper credentials, Func`2 applyTokens, DataFeedContext context)
   at ArcherTech.DataFeed.Transporters.DbQueryTransporter.ExecuteInternal()
   at ArcherTech.DataFeed.ComponentModel.DataFeedActivity.Execute()</StackTrace>
                    </Exception>
                </TraceRecord>
            </DataItem>
        </TraceData>
    </ApplicationData>
</E2ETraceEvent>


 
Cause
  • Archer Admin Dashboard Population Feed faulted with data type varchar conversion error
  • Archer Admin Dashboard showing no data
Resolution
  1. Check Windows OS Locale/Date format.

  • Open Windows Control Panel.
  • Go to Region and Language and verify the Locale that is configured on the OS.
  • Go to Date and Time and verify the timezone that is configured on the OS.

  1. Check Microsoft SQL Locale/Date format.

Run these queries on SQL Server to check the Language and Date format that is configured on SQL Server.




DBCC USEROPTIONS



Note: For non U.S date format, it is recommended to configure SQL Database to use 'British English' to follow the dmy date format.


  1. Check the Locale configured in RSA Archer Control Panel.
  2. Check the Locale configured in XML Configuration for RSA Archer Admin Dashboard datafeed.



USE [archerinstance]
GO
-- XML Configuration
SELECT configuration_xml,datafeed_id, datafeed_name FROM tblDataFeed WHERE datafeed_name like '%Admin Dashboard%'



Note: To update the locale in XML configuration for RSA Archer Admin Dashboard Population Feed, upgrading RSA Archer to 6.6.0.2 version or above first.
Then use AdminDashboardDataFeed_UpdateLocale.sql SQL script to update the locale in XML configuration.


  1. Ensure all locale and date format match.

Outcomes