3

I am in the upgrading process of Sitecore instance from 9.1 to 10.2 where the upgrade steps have been followed where I created a fresh 10.2.x instance and deployed the solution in to after the fact it got refactored to the version it is about to be deployed with. Except the data exception on Experience Analytics end.

ERROR [Experience Analytics]: System.Exception: Invalid column name 'Converted'. ---> System.Data.DataException: Error executing SQL command: 
WITH WorkingSubset AS 
(
SELECT Fact_ChannelTypeMetrics.[bounces], Fact_ChannelTypeMetrics.[conversions], Fact_ChannelTypeMetrics.[pageViews], Fact_ChannelTypeMetrics.[timeOnSite], Fact_ChannelTypeMetrics.[value], Fact_ChannelTypeMetrics.[visits], Fact_ChannelTypeMetrics.[outcomeOccurrences], Fact_ChannelTypeMetrics.[monetaryValue], Fact_ChannelTypeMetrics.[DimensionKeyId], Fact_ChannelTypeMetrics.[SegmentId], Fact_ChannelTypeMetrics.[Date],Fact_SystemMetrics.[totalVisits], Fact_SystemMetrics.[totalTimeOnSite], Fact_SystemMetrics.[totalPageviews], Fact_SystemMetrics.[totalEngagementValue]
FROM Fact_ChannelTypeMetrics INNER JOIN [DimensionKeys] on [DimensionKeys].DimensionKeyId = Fact_ChannelTypeMetrics.DimensionKeyId LEFT OUTER JOIN (SELECT * FROM Fact_SystemMetrics WHERE [SegmentId] IN (@systemSegmentId1)) AS Fact_SystemMetrics ON Fact_ChannelTypeMetrics.[SiteNameId] = Fact_SystemMetrics.[SiteNameId] AND Fact_ChannelTypeMetrics.[Date] = Fact_SystemMetrics.[Date] AND Fact_ChannelTypeMetrics.[FilterId] = Fact_SystemMetrics.[FilterId] 
WHERE Fact_ChannelTypeMetrics.[SegmentId] = @SegmentId AND Fact_ChannelTypeMetrics.[Date] BETWEEN @StartDate AND @EndDate AND ((Fact_ChannelTypeMetrics.DimensionKeyId = @DimensionKeyId))
)
,BaseResults AS 
(
SELECT Sum(CAST([bounces] as bigint)) AS [bounces], Sum(CAST([conversions] as bigint)) AS [conversions], Sum(CAST([pageViews] as bigint)) AS [pageViews], Sum(CAST([timeOnSite] as bigint)) AS [timeOnSite], Sum(CAST([value] as bigint)) AS [value], Sum(CAST([visits] as bigint)) AS [visits], Sum(CAST([outcomeOccurrences] as bigint)) AS [outcomeOccurrences], Sum([monetaryValue]) AS [monetaryValue], Sum(CAST([totalVisits] as bigint)) AS [totalVisits], Sum(CAST([totalTimeOnSite] as bigint)) AS [totalTimeOnSite], Sum(CAST([totalPageviews] as bigint)) AS [totalPageviews], Sum(CAST([totalEngagementValue] as bigint)) AS [totalEngagementValue], COUNT(*) OVER () AS [TotalDbRows] 
FROM [WorkingSubset]
)
SELECT *, 1.0*TimeOnSite/Visits as [avgVisitDuration], 1.0*Bounces/Visits as [bounceRate], 1.0*Converted/Visits as [conversionRate], 1.0*PageViews/Visits as [avgVisitPageViews], 1.0*Value/Visits as [valuePerVisit], 1.0*MonetaryValue/Visits as [avgMonetaryValue]  
FROM [BaseResults] ---> System.Data.SqlClient.SqlException: Invalid column name 'Converted'.
   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& 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& 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& task, Boolean& 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 Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteReader()
   --- End of inner exception stack trace ---
   --- End of inner exception stack trace ---
   at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteReader()
   at Sitecore.Data.DataProviders.Sql.DataProviderReader..ctor(DataProviderCommand command)
   at Sitecore.Data.DataProviders.Sql.SqlDataApi.<>c__DisplayClass29_0.<CreateReader>b__0()
   at Sitecore.Data.DataProviders.NullRetryer.Execute[T](Func`1 action, Action recover)
   at Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(String sql, Object[] parameters)
   at Sitecore.Analytics.Reporting.SqlReportDataSource.GetData(ReportDataQuery query)
   at Sitecore.Xdb.Reporting.ReportDataProvider.ExecuteQueryWithCache(ReportDataQuery query, ReportDataSource dataSource, CachingPolicy cachingPolicy)
   at Sitecore.Xdb.Reporting.ReportDataProvider.GetData(String dataSourceName, ReportDataQuery query, CachingPolicy cachingPolicy)
   at Sitecore.ExperienceAnalytics.Api.ReportDataService.ExecuteQuery(IReportQueryData queryData, CachingPolicy cachingPolicy)
   at Sitecore.ExperienceAnalytics.Api.ReportingService.RunQuery(ReportQuery reportQuery)
   at Sitecore.ExperienceAnalytics.Api.Http.AnalyticsDataController.Get(ReportQuery reportQuery)

I have validated the following

  1. XConnect app_data had infacts a valid license xml file
  2. Users been added to the computer management Performance log users and Performance montior users
  3. validated that the cert assigned is infact self-assigned
  4. Performed a Sitecore 10.2.1 rev. 008245 PRE (OnPrem) Cumulative hotfix patch
  5. Being a fresh xconnect instance rather with fresh dbs no old databases were reference to perform any xp related upgrades where as master db related upgrades like sql scripts, clean ups where done

Please let me know if i have missed any. enter image description here

2
  • Is Xconnect site working?
    – Ashish
    Oct 17, 2022 at 6:48
  • Xconnect is intact
    – Arun Kumar
    Oct 20, 2022 at 6:44

2 Answers 2

4

Dean OBrien's answer was helpful as a starting point when investigating this one. However I needed to dig deeper, which meant following the stacktrace and digging into the decompiled code which lead me to Sitecore.ExperienceAnalytics.Api.Query.QueryBuilder.FactsQueryBuilder.

This builder is actually building up the SQL query based on item configuration in /sitecore/system/Marketing Control Panel/Experience Analytics/Dimensions.

Here we can see that this item in question does not have the 'Converted' metric specified and hence causing the SQL error.

1

So we can simply delete everything out of /sitecore/system/Marketing Control Panel/Experience Analytics/Dimensions by adding a new version to that item and then deleting the item. Sitecore will delete all the items out the DB and then restore the stock items from the master resource file. After this, restart the CM server to clear all caches.

0

If you check a fresh copy of the Reporting Database from Sitecore Experience Platform 10.2 you will see there is a column called Converted in the Fact_ChannelTypeMetrics table.

Fact_ChannelTypeMetrics 10.2

However, the came table in Sitecore Experience Platform 9.1 Update-1, does not have the column.

Fact_ChannelTypeMetrics 9.1

Therefore, given that the error is stating that it is an invalid column name, I would suspect that you are running old Sitecore 9.1 DLLs against new Sitecore 10.2 databases.

Please check your websites bin folded and ensure the sitecore DLLs are the correct version (i.e. not 12.0.1.0)

UPDATE

If your instance is distributed, remember that you may still have reporting instance configured from pre upgrade (this is removed in SC 10.1). Config for the reporting url will be as below:

  <sitecore role:require="ContentManagement">
    <reporting role:require="!Reporting">
      <remote enabled="true">
        <httpTransportFactory type="Sitecore.Analytics.Core.ApiKeyHttpTransportFactory, Sitecore.Analytics.Core" singleInstance="true">
          <param desc="serviceUrl">#{reporting-service-url}</param>

UPDATE

Sorry upon looking at error again, I think we got it the wrong way round... the source is generating the SQL with 'Converted' in it, and the database is erroring to say it does not exist. So to confirm this, please install a fresh Reporting database and connect that to your instance. If that corrects your issue, then it looks like you have not fully performed all of the SQL upgrade scripts relating to you Reporting Database.

3
  • Thank you. I have validated if any old sitecore dlls are being published from solution, i couldnt find any. To validate i even installed a fresh 10.2 installation and connected existing master db rather publish any solution. But then just by referencing prod master db after executing all db upgrade scripts from 9.1.1 to 10.2 . It is still throwing the same error where no new assemblies of previous sitecore version is pushed in
    – Arun Kumar
    Oct 20, 2022 at 6:39
  • The table being referenced is from the Reporting DB, please make same checks with that connection string also. Also, is your upgraded instance in 'standalone' role or 'distributed'. If distributed, then see update in answer. Oct 20, 2022 at 7:03
  • it is a standalone. And the reporting db i am using is the one Sitecore created on fresh install of 10.2 rather prod db. Based on the sql query i can see the Converted column is missed as part of subset but it is expected on the the select where it tried to fetched the column out of the subset which wasnt there. That where the confusion is for me where reporting db is fresh one, dlls pushed in not holding any outdated dll, upgrade script for master was run which as per upgrade guidelines.
    – Arun Kumar
    Oct 21, 2022 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.