Friday, November 23, 2012

Veeam MP For VMware Report Issue SOLVED: ‘Query execution failed for dataset 'AnalysisDS'. Error converting data type varchar to numeric’

Bumped into this issue on a customers location. OM12 in place and a VMware production environment. Of course the Veeam MP for VMware was installed, imported and configured. Soon the information started to flow into OM12. Nice!

The issue
Time for some demonstrations in order to show the power of the Veeam MP for VMware!

Everything went just fine. But when I tried to ran the report Virtual Machines. Right-sizing – VMs Oversized for Memory and CPU, I got this error (with remote errors on the related SSRS instance enabled):

This puzzled me since the Collation setting is 100% OK. But as the last sentence of the error states (Error converting data type varchar to numeric) changes are something else is at play here, the (in)famous location issues we IT people living outside the US/UK do experience many times…

The cause
One of the reasons I am a BIG fan of Veeam is not only the quality of its products but also the high level of support one gets. Within a minute I located this issue in their KB library, article KB1565.

And indeed, the location issue is at play here. Have bumped into it many times before with other software as well. In western Europe many countries use the comma (,) as decimal separator format whereas the US uses the dot (.) instead. And this is why the report fails. It expects a dot (.) and gets a comma (,) instead.

So there is nothing wrong with the dataset of that report, it’s just the location setting which is haunting me from day one I became an IT geek…

Taken directly from KB1565:

‘…To resolve the issue, either set the English System Locale in Region and Language options or change the decimal separator setting in Region and Language> Formats> Additional settings> Decimal symbol (for Win 2k3: Region and Language OptionsRegional Options> Customize> Decimal Symbol). Make sure you change the decimal symbol for nworks collector service account…’

After these steps I did a rebuild of the full topology ‘…Next, trigger full topology rebuild in nworks UI> Enterprise Manager> Rebuild Full Topology (available, when the Enterprise Manager node is selected in the left pane)…’ and soon afterwards the report worked like a charm!

When using OpsMgr 2012 and other software in area’s where a comma (,) is used as decimal separator, make sure you run your servers in the English system locale OR change the decimal symbol to a dot (.) instead.

And last but not least, the customer was even more impressed about Veeam because the time to fix (time measured from the moment the problem was found, the cause isolated and a solution applied), took less then half an hour simply because Veeam has a good relevant and up to date KB library in place. Awesome!

No comments: