Generating BACPAC using data type sql_variant in InstancePromotedPropertiesTable

Question

Hello folks,

I am trying to make an export from an SQL Azure database using the BACPAC technology. Unfortunately this procedure fails with:

Microsoft.SqlServer.Management.Dac.Services.ServiceException: Error encountered during the service operation.
Inner exception Microsoft.SqlServer.Management.Dac.BacpacException: 30002: Export failed for database 'xxxxxx'. See the inner exception for more details.
Inner exception Microsoft.SqlServer.Management.Dac.Core.BacpacException: 30002: Export failed for database 'xxxxxx'. See the inner exception for more details.
Inner exception Microsoft.SqlServer.Management.Dac.Core.BacpacException: 50005: Column type 'Variant' unsupported from table 'InstancePromotedPropertiesTable'.

The table InstancePromotedPropertiesTable is a table needed for Microsoft's own Workflows in .NET 4. Indeed, it contains sql_variant fields.

Anyone experiencing the same problems?

Thanks in advance!

Tim

Answer 1

Hi,

This is the standard behavior for sql_variant fields, since they are not supported in the current release ofr SQL Azure's import/export. Quoting the original source: 

Other Backup Notes

• Passwords are not stored in the BACPAC therefore you'll need to set the passwords on relevant logins upon import to a new server
• SQL variant data types are not yet supported, support will be added in the next release of the Import/Export Service
• You can import (restore) BACPACs created by the service by using the client side tools also available in this project 

As an alternative you might want to investigate if Enzo Backup supports the sql_variant field.

Sandrino

---

Sandrino Di Mattia | Twitter: http://twitter.com/sandrinodm | Azure Blog: http://fabriccontroller.net/blog | Blog: http://sandrinodimattia.net/blog

Answer 2

I would call it BUG and not a standard behavior. I believe that SQL Azure development team should put real effort on this topic. There many sql server databases out there that make use of the sql variant datatype.

Should any one of those needs to be ported to the cloud it would require to have bacpac services to work properly.

---

Stavros Menegos

Answer 3

I was wondering what the status of this bug is. Have you made any progress in supporting the sql_variant data type?

---

Anders Borum / SphereWorks