sp_describe_first_result_set and system_type_name NULL by Andrea_Benedetti

Closed

as Fixed

Type:	Bug
ID:	765998
Opened:	10/3/2012 3:58:44 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

If you exec this script:

EXEC sp_describe_first_result_set @tsql = N'exec [AdventureWorks2012].[dbo].[uspGetEmployeeManagers] 1';

You can find NULL on [system_type_name] when system_type_id is not unique in sys.types.

Maybe, internally, the join must be:
sys.columns.system_type_id = sys.types.user_type_id

Details (expand)

English

SQL Server 2012 - Developer Edition

SQL Engine

Other

English

-- to see the NULL:
EXEC sp_describe_first_result_set @tsql = N'exec [AdventureWorks2012].[dbo].[uspGetEmployeeManagers] 1';

-- to see the correct schema:
IF OBJECT_ID( N'tempdb..#t', N'U' )IS NOT NULL
    BEGIN
        DROP TABLE #t;
    END;

/* please type your server... */
SELECT P.* INTO #t
FROM OPENROWSET
     (
     'SQLNCLI' ,
     'Server=.\SQL2012;Trusted_Connection=yes;' ,
     'exec [AdventureWorks2012].[dbo].[uspGetEmployeeManagers] 1'
     ) AS P
WHERE 1 = 0;

SELECT c.name as [column name],
     (
         SELECT t.name
         FROM sys.types t
         WHERE
         t.user_type_id = c.system_type_id AND
         is_user_defined = 0
     ) AS [column type] ,
     c.max_length
FROM tempdb.sys.columns c
WHERE object_id = OBJECT_ID( N'tempdb..#t' , N'U' );

NULL on system_type_name column

nvarchar on system_type_name column

0 attachments

Posted by Microsoft on 1/4/2013 at 12:58 PM

Hello Andrea,
We have now fixed the issue for the next major version of SQL Server. We may port it to a future SQL Server 2012 cumulative update if there are many occurrences of this issue.

--
Umachandar, SQL Programmability Team

Posted by Microsoft on 10/9/2012 at 12:38 PM

Hello Andrea,
Thanks for reporting this issue. We will take a look at it & get back to you.

--
Umachandar, SQL Programmability Team

Microsoft