Adding support for SQL_VARIANT?

Topics: Database Publishing Wizard
Jul 10, 2007 at 9:40 PM
I'd love to use this tool to make backups of our production database and then restore them to our development server. Unfortunately we have some fields which are SQL_VARIANT.
SSIS has the same limitation, we get around this by casting them to varbinary and recording the original type (split the SQL_VARIANT in to two fields).

SELECT CAST(MyVariantField as varbinary) as data, CAST(SQLVARIANTPROPERTY(MyVariantField, 'BaseType') AS nvarchar) as DataType
FROM MyTable

Scripting the subsequent INSERT statement is then pretty trivial.
Aug 8, 2007 at 8:01 PM
The tool should work with variant columns when scripting to T-SQL or via the web service. Are you saying they do not?

alex


jmansford wrote:
I'd love to use this tool to make backups of our production database and then restore them to our development server. Unfortunately we have some fields which are SQL_VARIANT.
SSIS has the same limitation, we get around this by casting them to varbinary and recording the original type (split the SQL_VARIANT in to two fields).

SELECT CAST(MyVariantField as varbinary) as data, CAST(SQLVARIANTPROPERTY(MyVariantField, 'BaseType') AS nvarchar) as DataType
FROM MyTable

Scripting the subsequent INSERT statement is then pretty trivial.

Aug 9, 2007 at 9:19 AM
No it doesn't work, we have a field which is SQL_VARIANT and contains datetime,int,tinyint and various nvarchar types within it. The Wizard happily scripts the table creation and goes all the way through and starts writing down the data to the file until it gets to a variant containing a datetime type. The error returned is:
"sql_variant type datetime is not supported (SqlPubWiz)"

I would expect that this isn't too difficult to cope with, even taking in to account date formats as it could be cast to an integer and then cast back when inserting in to the destination.

Hope that helps,

Joel.


alexd wrote:
The tool should work with variant columns when scripting to T-SQL or via the web service. Are you saying they do not?

alex


jmansford wrote:
I'd love to use this tool to make backups of our production database and then restore them to our development server. Unfortunately we have some fields which are SQL_VARIANT.
SSIS has the same limitation, we get around this by casting them to varbinary and recording the original type (split the SQL_VARIANT in to two fields).

SELECT CAST(MyVariantField as varbinary) as data, CAST(SQLVARIANTPROPERTY(MyVariantField, 'BaseType') AS nvarchar) as DataType
FROM MyTable

Scripting the subsequent INSERT statement is then pretty trivial.