0

I'm trying to fetch information about a database and I have the following query that prints most of it out:

SELECT Serverproperty('ServerName') AS [Server], 
       (SELECT Server_type = CASE 
                               WHEN virtual_machine_type = 1 THEN 'Virtual' 
                               ELSE 'Physical' 
                             END 
        FROM sys.dm_os_sys_info)
       AS [Virtual/Physical], 
       (SELECT ( cpu_count / hyperthread_ratio ) 
        FROM   sys.dm_os_sys_info) 
       AS [CPU(s)], 
       (SELECT CASE 
                 WHEN hyperthread_ratio = cpu_count THEN cpu_count 
                 WHEN ( ( cpu_count - hyperthread_ratio ) / ( 
                        cpu_count / hyperthread_ratio ) 
                      ) = 0 THEN 1 
               END  
        FROM   sys.dm_os_sys_info) 
       AS [Core(s)], 
       Substring ((SELECT @@VERSION), 1, Charindex('-', (SELECT @@VERSION)) - 1) 
       AS [Product Name], 
       Serverproperty('ProductVersion') AS [Product Version], 
       Serverproperty('ProductLevel') AS [Product Level], 
       Serverproperty('Edition') AS [Edition] 

Result: Results

This is where my issue comes in. I found a script that prints out all your instances on the server, too. Like this:

DECLARE @GetInstances TABLE 
  ( 
     value         NVARCHAR(100), 
     instancenames NVARCHAR(100), 
     data          NVARCHAR(100) 
  ) 

INSERT INTO @GetInstances 
EXECUTE Xp_regread 
  @rootkey = 'HKEY_LOCAL_MACHINE', 
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server', 
  @value_name = 'InstalledInstances' 

SELECT instancenames 
FROM   @GetInstances 

Instance Names

My issue is that I cannot seem to forge the first query with the second by including it in the SELECT-statement of the first script. I get the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How can I fix this? I would like the result to be printed out in the form of the image below, is that possible? Desired result

  • 1
    Could you supply us with the combined query by adding it to your question? Might be just a simple syntax error or misunderstanding. – John aka hot2use Nov 10 '17 at 13:52
  • 1
    If you have multiple instances of SQL Server on a single server, you are not guaranteed that they are all the same. So I guess I'm asking - is that an acceptable assumption to make? – mathewb Nov 10 '17 at 14:25
1

Only way to format like that is to UNION the queries:

DECLARE @GetInstances TABLE 
  ( 
     value         NVARCHAR(100), 
     instancenames NVARCHAR(100), 
     data          NVARCHAR(100) 
  ) 

INSERT INTO @GetInstances 
EXECUTE Xp_regread 
  @rootkey = 'HKEY_LOCAL_MACHINE', 
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server', 
  @value_name = 'InstalledInstances' 


SELECT Serverproperty('ServerName') AS [Server], 
       (SELECT Server_type = CASE 
                               WHEN virtual_machine_type = 1 THEN 'Virtual' 
                               ELSE 'Physical' 
                             END 
        FROM sys.dm_os_sys_info)
       AS [Virtual/Physical], 
       CAST((SELECT ( cpu_count / hyperthread_ratio ) 
        FROM   sys.dm_os_sys_info) 
        AS VARCHAR(10))
       AS  [CPU(s)], 
       CAST((SELECT CASE 
                 WHEN hyperthread_ratio = cpu_count THEN cpu_count 
                 WHEN ( ( cpu_count - hyperthread_ratio ) / ( 
                        cpu_count / hyperthread_ratio ) 
                      ) = 0 THEN 1 
               END  
        FROM   sys.dm_os_sys_info) 
         AS VARCHAR(10)) AS [Core(s)], 
       Substring ((SELECT @@VERSION), 1, Charindex('-', (SELECT @@VERSION)) - 1) 
       AS [Product Name], 
       Serverproperty('ProductVersion') AS [Product Version], 
       Serverproperty('ProductLevel') AS [Product Level], 
       Serverproperty('Edition') AS [Edition] 

UNION ALL

SELECT '', instancenames, '', '', '', '', '', ''
FROM   @GetInstances 

We return blank strings '' for most columns in the second query as both queries must have the same number of columns. Note I've also CAST [CPU(s)] and [Core(s)] columns in the top query to VARCHAR, otherwise the second query returns 0 in those columns ('' cast to INT = 0).

  • @matthewb's comment is valid - Version, Edition, and Patch Level can differ between instances so you'd need to query those individually to get that data. – Gareth Lyons Nov 13 '17 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.