I want to use SQL_VARIANT datatype in external table Azure SQL and I get the "Index was out of range error."

Question

I have two SQL Azure databases - DatabaseA and DatabaseB on a server hosted in Azure.

I need to access a view on DatabaseA from DatabaseB - namely I need the sys.identity_columns in DatabaseA to be available to me on DatabaseB. So I am creating an external table on DatabaseB that links to this information like this (I didn't include all the columns but I included the one causing the problem)

CREATE EXTERNAL TABLE [SOURCE_SYS].[identity_columns](
      [object_id] int not null
      ,[name] nvarchar(128) null
      ,[column_id] int not null
      ,[system_type_id] tinyint not null
      ,[seed_value] sql_variant null
    )
    WITH
    (
    DATA_SOURCE = MyElasticDBQueryDataSrc,
    SCHEMA_NAME = 'sys',
    OBJECT_NAME = 'identity_columns'
    );

When I run this - it works. But when I try to use the result - select * from [SOURCE_SYS].[identity_columns] - I get this error:

Msg 46823, Level 16, State 1, Line 50 Error retrieving data from MyServer.database.windows.net.DatabaseA. The underlying error message received was: 'Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index'.

If I comment out the fields in this table that have the sql_variant datatypes - it works fine but I do need the information in that field and the other two sql_variant fields that exist in the same table. MyElasticDBQueryDataSrc works fine on other similar tables without the sql_variant type.

Can anyone suggest what I might be doing wrong? Or suggest a workaround? I tried using bigints as it is mostly seed values that are either integers or null but that didn't work because it told me it wasn't the same datatype.

Any help much appreciated.

Answer 1

Well - after a weekend of sleep I figured out the answer!

If you use nvarchar(30) in he external table definition - you can then convert it to a bigint in any query you use it in

CREATE EXTERNAL TABLE [SOURCE_SYS].[identity_columns](
      [object_id] int not null
      ,[name] nvarchar(128) null
      ,[column_id] int not null
      ,[system_type_id] tinyint not null
      ,[seed_value] nvarchar(30) null
    )
    WITH
    (
    DATA_SOURCE = MyElasticDBQueryDataSrc,
    SCHEMA_NAME = 'sys',
    OBJECT_NAME = 'identity_columns'
    );

Now I can access the value like this:

select cast(isnull([seed_value], 0) as bigint) from SOURCE_SYS.identity_columns

Beware that if you do a select * from - you will need to do the variants separately from the rest of the query - you'll get this error:

Msg 46825, Level 16, State 1, Line 58 The data type of the column 'seed_value' in the external table is different than the column's data type in the underlying standalone or sharded table present on the external source.

Hope this is helpful to someone!