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]
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
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?
Best Answer
Only way to format like that is to
UNION
the queries:We return blank strings
''
for most columns in the second query as both queries must have the same number of columns. Note I've alsoCAST
[CPU(s)]
and[Core(s)]
columns in the top query toVARCHAR
, otherwise the second query returns0
in those columns ('' cast to INT = 0).