Attempting to import a VM larger than 2 TB into vCloud Director 5.6.3 from vSphere gives a data truncation error message

By Marco Alcala

This morning, I attempted to import a virtual machine with a hard disk larger than 2 TB into vCloud Director from vSphere.  When I clicked the import from vSphere button, I got the following error message:

[ 5594fe5e-7ad3-429b-a42e-5544bad64d69 ] could not execute query
- Data truncation

Using SQL profiler against my vcloud database, I found that when the following query was executed, SQL server threw an 8115 arithmetic overflow error.

select count(*) as y0_ from ui_vm_inv_list_view this_ where (this_.vc_id= @P0 and this_.state= @P1 )

The exact error message in my SQL trace was: “Arithmetic overflow error converting expression to data type int.”

In SQL Management studio, I located the view ui_vm_inv_list_view and confirmed that this was the source of the arithmetic overflow error message.

After reviewing the code, I found this sum was the cause of the error

SUM(storage_requested_mb) AS storage_used_mb

Casting storage_requested_mb as a big integer solved my challenge.   If you want to try this fix, make sure you take a backup of your vcloud database or at the very least save the text of the view ui_vm_inv_list_view so that you can restore it if necessary.

The revised code is:

SUM(CAST (storage_requested_mb AS bigint)) AS storage_used_mb

I am happy to report that after making this change, I was able to import my 2 TB VM into vCloud Director from vSphere.vC