2015-11-14T22:11:45.001-05:00 error vpxd[05056] [Originator@6876 sub=StatsAggregator opID=29966d1e] [VpxdStatsAggregator::QueryCounterMetric] "ODBC error: (42000) - [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon." is returned when executing SQL statement "SELECT SC.ENTITY_ID, SM.SAMPLE_TIME, ST.STAT_VAL FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), (SELECT * FROM VPX_HIST_STAT1 WITH (NOLOCK)) ST WITH (NOLOCK) WHERE SM.SAMPLE_TIME >= ? AND SM.SAMPLE_TIME <= ? AND ST.TIME_ID = SM.TIME_ID AND SC.STAT_ID = ? AND COALESCE(D.DEVICE_NAME, N'') = N'' AND SC.ENTITY_ID IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
Any idea how to fix or troubleshoot this?
Database is MS SQL 2014
Problem seems to be with stas_rollup1_proc T-SQL syntax
WITH(NOLOCK) <- what is this, why does the SQL server not like it, and how can I modify all of these in the entire database ?
A more serious looking error is also logged in vpxd log:
2015-11-15T00:41:11.721-05:00 warning vpxd[07740] [Originator@6876 sub=Default opID=377ae2f1] [VdbStatement] SQL execution failed: SELECT SC.ENTITY_ID, SM.SAMPLE_TIME, ST.STAT_VAL FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), (SELECT * FROM VPX_HIST_STAT1 WITH (NOLOCK)) ST WITH (NOLOCK) WHERE SM.SAMPLE_TIME >= ? AND SM.SAMPLE_TIME <= ? AND ST.TIME_ID = SM.TIME_ID AND SC.STAT_ID = ? AND COALESCE(D.DEVICE_NAME, N'') = N'' AND SC.ENTITY_ID IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) AND SC.COUNTER_ID = ST.COUNTER_ID AND SC.DEVICE_ID = D.DEVICE_ID ORDER BY SC.ENTITY_ID, SM.SAMPLE_TIME
I know this is a old post but I'm currently seeing the same error messages in a customer's vpxd log who is reporting performance issues on the vcenter server.
Has there been a fix to this issue?
The customer is running a windows based vcenter 6.0 U2 build 3634793
As far as I know this vCenter was a fresh install with 6.0 U1b and has thereafter been updated to the above release.
We are also seeing these errors after upgrading to vCenter server 6.0 U2 (build 3634793). Looks like there is a KB article about it.
The fix is to upgrade to vCenter 6.0 U3