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

2
  • 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 K. N.
    Nov 10, 2017 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, 2017 at 14:25

1 Answer 1

0

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).

1
  • @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. Nov 13, 2017 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.