m-jahedbozorgan
commented
5 years ago The same error occurs when I open the dashboard portal and want to view stats after manually copying contents of all tables in my H2 EI_ANALYTICS database to tables in my Microsoft SQL Server database and updating datasources.
Using Profiler I got executed queries when viewing my dashboard. These are executed queries:
exec sp_executesql N'SELECT URL, OWNER, NAME, DESCRIPTION, PARENT_ID, LANDING_PAGE, CONTENT FROM DASHBOARD_RESOURCE WHERE URL = @P0 ',N'@P0 varchar(8000)','eianalytics'
exec sp_executesql N'SELECT WIDGET_NAME, WIDGET_CONFIGS FROM WIDGET_RESOURCE WHERE WIDGET_ID = @P0 ',N'@P0 varchar(8000)','EIAnalyticsStatsChart'
exec sp_executesql N'SELECT WIDGET_NAME, WIDGET_CONFIGS FROM WIDGET_RESOURCE WHERE WIDGET_ID = @P0 ',N'@P0 varchar(8000)','EIAnalyticsStatsChart'
exec sp_executesql N'SELECT WIDGET_NAME, WIDGET_CONFIGS FROM WIDGET_RESOURCE WHERE WIDGET_ID = @P0 ',N'@P0 varchar(8000)','EIAnalyticsHorizontalBarChart'
exec sp_executesql N'SELECT WIDGET_NAME, WIDGET_CONFIGS FROM WIDGET_RESOURCE WHERE WIDGET_ID = @P0 ',N'@P0 varchar(8000)','EIAnalyticsTPS'
exec sp_executesql N'SELECT WIDGET_NAME, WIDGET_CONFIGS FROM WIDGET_RESOURCE WHERE WIDGET_ID = @P0 ',N'@P0 varchar(8000)','Overall-Message-Count'
exec sp_executesql N'SELECT WIDGET_NAME, WIDGET_CONFIGS FROM WIDGET_RESOURCE WHERE WIDGET_ID = @P0 ',N'@P0 varchar(8000)','EIAnalyticsHorizontalBarChart'
--exec sp_executesql N'SELECT ESBStatAgg_YEARS.AGG_TIMESTAMP AS AGG_TIMESTAMP, ESBStatAgg_YEARS.AGG_EVENT_TIMESTAMP AS AGG_EVENT_TIMESTAMP, ESBStatAgg_YEARS.metaTenantId AS metaTenantId, ESBStatAgg_YEARS.componentId AS componentId, ESBStatAgg_YEARS.componentName AS componentName, ESBStatAgg_YEARS.componentType AS componentType, ESBStatAgg_YEARS.entryPoint AS entryPoint, ESBStatAgg_YEARS.AGG_LAST_EVENT_TIMESTAMP AS AGG_LAST_EVENT_TIMESTAMP, ESBStatAgg_YEARS.eventTimestamp AS eventTimestamp, ESBStatAgg_YEARS.AGG_SUM_duration AS AGG_SUM_duration, ESBStatAgg_YEARS.AGG_COUNT AS AGG_COUNT, ESBStatAgg_YEARS.AGG_MIN_duration AS AGG_MIN_duration, ESBStatAgg_YEARS.AGG_MAX_duration AS AGG_MAX_duration, ESBStatAgg_YEARS.AGG_SUM_faultCount AS AGG_SUM_faultCount FROM ESBStatAgg_YEARS ORDER BY ESBStatAgg_YEARS.AGG_TIMESTAMP DESC FETCH NEXT 1 ROWS ONLY'
exec sp_executesql N'SELECT WIDGET_NAME, WIDGET_CONFIGS FROM WIDGET_RESOURCE WHERE WIDGET_ID = @P0 ',N'@P0 varchar(8000)','EIAnalyticsHorizontalBarChart'
--exec sp_executesql N'SELECT ESBStatAgg_YEARS.AGG_TIMESTAMP AS AGG_TIMESTAMP, ESBStatAgg_YEARS.AGG_EVENT_TIMESTAMP AS AGG_EVENT_TIMESTAMP, ESBStatAgg_YEARS.metaTenantId AS metaTenantId, ESBStatAgg_YEARS.componentId AS componentId, ESBStatAgg_YEARS.componentName AS componentName, ESBStatAgg_YEARS.componentType AS componentType, ESBStatAgg_YEARS.entryPoint AS entryPoint, ESBStatAgg_YEARS.AGG_LAST_EVENT_TIMESTAMP AS AGG_LAST_EVENT_TIMESTAMP, ESBStatAgg_YEARS.eventTimestamp AS eventTimestamp, ESBStatAgg_YEARS.AGG_SUM_duration AS AGG_SUM_duration, ESBStatAgg_YEARS.AGG_COUNT AS AGG_COUNT, ESBStatAgg_YEARS.AGG_MIN_duration AS AGG_MIN_duration, ESBStatAgg_YEARS.AGG_MAX_duration AS AGG_MAX_duration, ESBStatAgg_YEARS.AGG_SUM_faultCount AS AGG_SUM_faultCount FROM ESBStatAgg_YEARS ORDER BY ESBStatAgg_YEARS.AGG_TIMESTAMP DESC FETCH NEXT 1 ROWS ONLY'
exec sp_executesql N'SELECT WIDGET_NAME, WIDGET_CONFIGS FROM WIDGET_RESOURCE WHERE WIDGET_ID = @P0 ',N'@P0 varchar(8000)','EIAnalyticsHorizontalBarChart'
--exec sp_executesql N'SELECT ESBStatAgg_YEARS.AGG_TIMESTAMP AS AGG_TIMESTAMP, ESBStatAgg_YEARS.AGG_EVENT_TIMESTAMP AS AGG_EVENT_TIMESTAMP, ESBStatAgg_YEARS.metaTenantId AS metaTenantId, ESBStatAgg_YEARS.componentId AS componentId, ESBStatAgg_YEARS.componentName AS componentName, ESBStatAgg_YEARS.componentType AS componentType, ESBStatAgg_YEARS.entryPoint AS entryPoint, ESBStatAgg_YEARS.AGG_LAST_EVENT_TIMESTAMP AS AGG_LAST_EVENT_TIMESTAMP, ESBStatAgg_YEARS.eventTimestamp AS eventTimestamp, ESBStatAgg_YEARS.AGG_SUM_duration AS AGG_SUM_duration, ESBStatAgg_YEARS.AGG_COUNT AS AGG_COUNT, ESBStatAgg_YEARS.AGG_MIN_duration AS AGG_MIN_duration, ESBStatAgg_YEARS.AGG_MAX_duration AS AGG_MAX_duration, ESBStatAgg_YEARS.AGG_SUM_faultCount AS AGG_SUM_faultCount FROM ESBStatAgg_YEARS ORDER BY ESBStatAgg_YEARS.AGG_TIMESTAMP DESC FETCH NEXT 1 ROWS ONLY'
exec sp_executesql N'SELECT WIDGET_NAME, WIDGET_CONFIGS FROM WIDGET_RESOURCE WHERE WIDGET_ID = @P0 ',N'@P0 varchar(8000)','EIAnalyticsHorizontalBarChart'
--exec sp_executesql N'SELECT ESBStatAgg_YEARS.AGG_TIMESTAMP AS AGG_TIMESTAMP, ESBStatAgg_YEARS.AGG_EVENT_TIMESTAMP AS AGG_EVENT_TIMESTAMP, ESBStatAgg_YEARS.metaTenantId AS metaTenantId, ESBStatAgg_YEARS.componentId AS componentId, ESBStatAgg_YEARS.componentName AS componentName, ESBStatAgg_YEARS.componentType AS componentType, ESBStatAgg_YEARS.entryPoint AS entryPoint, ESBStatAgg_YEARS.AGG_LAST_EVENT_TIMESTAMP AS AGG_LAST_EVENT_TIMESTAMP, ESBStatAgg_YEARS.eventTimestamp AS eventTimestamp, ESBStatAgg_YEARS.AGG_SUM_duration AS AGG_SUM_duration, ESBStatAgg_YEARS.AGG_COUNT AS AGG_COUNT, ESBStatAgg_YEARS.AGG_MIN_duration AS AGG_MIN_duration, ESBStatAgg_YEARS.AGG_MAX_duration AS AGG_MAX_duration, ESBStatAgg_YEARS.AGG_SUM_faultCount AS AGG_SUM_faultCount FROM ESBStatAgg_YEARS ORDER BY ESBStatAgg_YEARS.AGG_TIMESTAMP DESC FETCH NEXT 1 ROWS ONLY'
--exec sp_executesql N'SELECT ESBStatAgg_YEARS.AGG_TIMESTAMP AS AGG_TIMESTAMP, ESBStatAgg_YEARS.AGG_EVENT_TIMESTAMP AS AGG_EVENT_TIMESTAMP, ESBStatAgg_YEARS.metaTenantId AS metaTenantId, ESBStatAgg_YEARS.componentId AS componentId, ESBStatAgg_YEARS.componentName AS componentName, ESBStatAgg_YEARS.componentType AS componentType, ESBStatAgg_YEARS.entryPoint AS entryPoint, ESBStatAgg_YEARS.AGG_LAST_EVENT_TIMESTAMP AS AGG_LAST_EVENT_TIMESTAMP, ESBStatAgg_YEARS.eventTimestamp AS eventTimestamp, ESBStatAgg_YEARS.AGG_SUM_duration AS AGG_SUM_duration, ESBStatAgg_YEARS.AGG_COUNT AS AGG_COUNT, ESBStatAgg_YEARS.AGG_MIN_duration AS AGG_MIN_duration, ESBStatAgg_YEARS.AGG_MAX_duration AS AGG_MAX_duration, ESBStatAgg_YEARS.AGG_SUM_faultCount AS AGG_SUM_faultCount FROM ESBStatAgg_YEARS ORDER BY ESBStatAgg_YEARS.AGG_TIMESTAMP DESC FETCH NEXT 1 ROWS ONLY'
Commented SELECT statements are invalid in Microsoft SQL Server because FETCH NEXT n ROWS ONLY must be used with OFFSET m ROWS.
RAVEENSR
Description: Changed all datasources from H2 to Microsoft SQL Server. After calling one of services in ESB profile, the console for Dashboard Worker profile prints error log. Error log shows that an invalid query was executed because specifying "FETCH NEXT n ROWS ONLY" without specifying "OFFSET m ROWS" in not valid in Microsoft SQL Server. See attached error log file for more info.
Suggested Labels: Type/Bug,Priority/High,Severity/Blocker
Suggested Assignees:
Affected Product Version: SP 4.4.0
OS, DB, other environment details and versions:
Environment is WSO2 EI (Integrator and Dashboard Worker profiles) on Windows + Microsoft SQL Server 2017
Steps to reproduce:
Related Issues:
error analytics mssql.txt