BEGIN TRY and EXECUTE dynamic simple query error

Question

when I execute attached query I get error:

Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.

When try-catch is commented then it works, same when other column is used. When partition by is removed it also works.

Can someone say where is the root of error here or is it some sql server bug?

Query:

BEGIN TRY 
    DECLARE  @vSqlCmd NVARCHAR(MAX) = N'
    SELECT TOP 1 V = d.collation_name, C = COUNT(1) OVER (PARTITION BY d.collation_name)
    FROM sys.all_columns d;';
    SELECT @vSqlCmd;

    EXECUTE (@vSqlCmd);
END TRY
BEGIN CATCH
    PRINT 'CATCH!!!';

    THROW;
END CATCH

Answer 1

This is a bug. I have reported it here

It is not clear why you need this query, the following explains the issue and how to avoid it if there is some reasonable reason for needing this query.

The following query returns two object_ids -103085222 and -593

SELECT object_id ,name
FROM sys.system_columns
where system_type_id in (35,99,167,175,231,239) and collation_name IS NULL

These relate to the objects below

+------------+----------------------------------+
|     id     |               name               |
+------------+----------------------------------+
| -103085222 | pdw_nodes_pdw_physical_databases |
|       -593 | pdw_table_mappings               |
+------------+----------------------------------+

For some reason both of these have a string column (called physical_name) that SQL Server can not resolve the collation for. As these objects are only relevant to Parallel Data Warehouse and don't even properly exist in other versions of the product this is not usually a problem.

sys.all_columns references the sys.system_columns view which uses the following expression for collation_name : convert(sysname, ColumnPropertyEx(object_id, name, 'collation'))

A simpler case that reproduces the issue is

BEGIN TRY
    SELECT columnpropertyex(-593, 'physical_name', 'collation')
END TRY
BEGIN CATCH
END CATCH 

Or

SET XACT_ABORT ON;
SELECT columnpropertyex(-593, 'physical_name', 'collation')

Both return

Msg 0, Level 11, State 0, Line 33

A severe error occurred on the current command. The results, if any, should be discarded.

When run outside the TRY block this returns NULL (when XACT_ABORT is off). Behind the scenes it throws an exception that is handled internally and the end user is unaware. The call stack when the exception is thrown shows that GetColumnCollate function ends up using the Algebrizer to try and resolve the column and it eventually fails in CAlgTableMetadata::RaiseBadTableException (possibly as a related object definition is missing somewhere).

When run in the TRY ... CATCH context or with XACT_ABORT ON something goes wrong with the bit that is supposed to silently ignore the error and return NULL. Inserting a duplicate key into an index with ignore_dup_key ON also raises an internal error that is ignored but does not have the same problem.

So one way of resolving the issue is to wrap the reference to collation_name in a CASE expression so it is never evaluated for those two problematic object_id when run inside the TRY block.

  BEGIN TRY 
    DECLARE  @vSqlCmd NVARCHAR(MAX) = N'
    SELECT TOP 1 V = ca.safe_collation_name, C = COUNT(1) OVER (PARTITION BY ca.safe_collation_name)
    FROM sys.all_columns d
    CROSS APPLY (SELECT CASE WHEN object_id NOT IN (-593,-103085222) THEN collation_name END) ca(safe_collation_name);
    ';
    SELECT @vSqlCmd;

    EXECUTE (@vSqlCmd);
END TRY
BEGIN CATCH
    PRINT 'CATCH!!!';

    THROW;
END CATCH

This does not protect you against different cases of problematic metadata being added to the product in the future. Again I question the need for this query however. The query you have provided is so odd that it is difficult to advise you what you should replace it with.