Microsoft SQL Server Management Studio - error on refresh tables - by MarkVanTilburg

Status : 

 


13
0
Sign in
to vote
ID 3119590 Comments
Status Active Workarounds
Type Bug Repros 5
Opened 1/25/2017 2:43:34 AM
Access Restriction Public

Description

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (Microsoft SQL Server, Error: 512)
Sign in to post a comment.
Posted by Matteo [MSFT] on 3/8/2017 at 3:23 PM
Indeed, this was fixed in SSMS 16.5.3. See also https://connect.microsoft.com/SQLServer/Feedback/Details/3119467, which is the same issue as reported belwo by others.

I'm going to resolve and close it.

Thanks,
-Matteo
Posted by MarkVanTilburg on 1/31/2017 at 12:46 AM
This is indeed fixed in the latest release
Microsoft SQL Server Management Studio                        13.0.16106.4
Posted by Liudi90 on 1/30/2017 at 6:16 PM
Resolved with SQL Server Management Studio version 16.5.3 at 30 January 2017
Posted by Sunil Gure on 1/27/2017 at 9:28 AM
I do not see this same issue and probably may be I have few databases and only few (max two or 3 ) tables into those databases.
Posted by Nick_Craver on 1/25/2017 at 1:04 PM
Here's an example query:

exec sp_executesql N'SELECT
tbl.name AS [Name],
SCHEMA_NAME(tbl.schema_id) AS [Schema],
''Server[@Name='' + quotename(CAST(
        serverproperty(N''Servername'')
     AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/Table[@Name='' + quotename(tbl.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(tbl.schema_id),'''''''') + '']'' AS [Urn],
tbl.create_date AS [CreateDate],
CAST(tbl.is_memory_optimized AS bit) AS [IsMemoryOptimized],
CAST(CASE idx.type WHEN 5 THEN 1 ELSE 0 END AS bit) AS [HasClusteredColumnStoreIndex],
CAST(tbl.is_remote_data_archive_enabled AS bit) AS [RemoteDataArchiveEnabled],
tbl.temporal_type AS [TemporalType],
CAST(CASE WHEN ''PS''=dsidx.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned],
CAST(
        ISNULL((SELECT 1 from sys.all_columns
                WHERE object_id = tbl.object_id
                AND is_sparse = 1), 0)
     AS bit) AS [HasSparseColumn]
FROM
sys.tables AS tbl
LEFT OUTER JOIN sys.periods as periods ON periods.object_id = tbl.object_id
LEFT OUTER JOIN sys.tables as historyTable ON historyTable.object_id = tbl.history_table_id
INNER JOIN sys.indexes AS idx ON
        idx.object_id = tbl.object_id and (idx.index_id < @_msparam_0 or (tbl.is_memory_optimized = 1 and idx.index_id = (select min(index_id) from sys.indexes where object_id = tbl.object_id)))
    
LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id
WHERE
(CAST(
case
    when tbl.is_ms_shipped = 1 then 1
    when (
        select
            major_id
        from
            sys.extended_properties
        where
            major_id = tbl.object_id and
            minor_id = 0 and
            class = 1 and
            name = N''microsoft_database_tools_support'')
        is not null then 1
    else 0
end        
             AS bit)=@_msparam_1 and tbl.is_filetable=@_msparam_2 and CAST(tbl.is_memory_optimized AS bit)=@_msparam_3 and tbl.temporal_type=@_msparam_4 and CAST(tbl.is_external AS bit)=@_msparam_5)
ORDER BY
[Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'2',@_msparam_1=N'0',@_msparam_2=N'0',@_msparam_3=N'0',@_msparam_4=N'0',@_msparam_5=N'0'

The problem is the `SELECT 1 from sys.all_columns`, which should be `SELECT TOP 1 1 from sys.all_columns` instead.
Posted by MarkVanTilburg on 1/25/2017 at 9:09 AM
Notice this now yes, the search for the exact error does not return that issue (guess that is also broken ;)).
Posted by Christopher Aliotta on 1/25/2017 at 8:15 AM
This bug has also been reported here:
https://connect.microsoft.com/SQLServer/Feedback/Details/3119467
Posted by Alberto Morillo on 1/25/2017 at 4:57 AM
Hello,

Related thread on MSDN forums:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/27c39cf6-555a-4dad-b1b3-db14b63ba5e6/ssms-2016-error-when-expanding-tables?forum=sqltools#15082f74-11d7-46a5-8d04-c8ec2256d07f

Regards,

Alberto Morillo
SQLCoffee.com
Posted by Christoph_Wagner on 1/25/2017 at 3:51 AM
I can't open the tables view on one of our databases at all, no need to even refresh first. Other databases work.