sys.dm_exec_describe_first_result_set returns wrong data type

Question

I have two SQL Servers 2016 connected with Linked Server. When I query sys.dm_exec_describe_first_result_set from the remote server, it returns numeric datatype for column table instead of decimal.

First query (executed from remote server)

select * from sys.dm_exec_describe_first_result_set (N'select top 1 * from LinkedServerFoo.DatabaseFoo.dbo.TargetTable', NULL, 0) [source]

Second query (executed from sql server)

select * from sys.dm_exec_describe_first_result_set (N'select top 1 * from DatabaseFoo.dbo.TargetTable', NULL, 0) [source]

Result

This is an issue for me since I compare the data types based on output of these queries.

Any idea what might be an issue, why is that and how it can be solved?

• There is also the same issue with smalldatetime where sys table wrongly returns datetime

Answer 1

System types 106 and 108 are decimal and numeric, respectively. These type names are synonyms and can be used interchangeably:

SELECT system_type_id, name
FROM sys.types
WHERE system_type_id IN(106, 108);

+----------------+---------+
| system_type_id |  name   |
+----------------+---------+
|            106 | decimal |
|            108 | numeric |
+----------------+---------+

The local query returns the type name specified by the original DDL used to create the column. The linked server query, however, returns the type name returned by the linked server driver. In my testing, the name is numeric regardless of whether the column was actually defined as numeric or decimal.

How it can be solved?

One work-around is to use explicit column names in the TVF SELECT list instead * so that you can translate the type name to a consistent one. For example:

SELECT
      is_hidden 
    , column_ordinal    
    , REPLACE(name, N'decimal', N'numeric') AS name
    , is_nullable   
    , system_type_id
    , REPLACE(system_type_name, N'decimal', N'numeric') AS system_type_name
    , max_length    
    , precision 
    , scale 
    , collation_name    
    , user_type_id  
    , user_type_database    
    , user_type_schema  
    , user_type_name    
    , assembly_qualified_type_name  
    , xml_collection_id 
    , xml_collection_database   
    , xml_collection_schema 
    , xml_collection_name   
    , is_xml_document   
    , is_case_sensitive 
    , is_fixed_length_clr_type  
    , source_server 
    , source_database   
    , source_schema 
    , source_table  
    , source_column 
    , is_identity_column    
    , is_part_of_unique_key 
    , is_updateable 
    , is_computed_column    
    , is_sparse_column_set  
    , ordinal_in_order_by_list  
    , order_by_is_descending    
    , order_by_list_length  
FROM sys.dm_exec_describe_first_result_set(N'select top 1 * from DatabaseFoo.dbo.TargetTable;', DEFAULT, DEFAULT) AS source;