SQL Query help

Question

Post reply

SQL Query help

Chitown

Hall of Fame

Points: 3239
More actions
October 20, 2015 at 12:47 pm

#399213

This is what I am trying to do.
1. I 'd like to insert (ServerID, memory, Core, Version, Edition) values of all the servers we have in our environment into a #temp table. This is the syntax I have
Create table #temp(
ServerID int,
Version varchar (50),
Edition varchar (10),
CoreCount varchar (200),
Memory int)
Declare @serverID int,
@Version varchar (100),
@Edition varchar (100),
@CoreCount Int,
@Memory int
select @serverID = server_id from sys.servers
Select @Memory = (SELECT round (total_physical_memory_kb / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"
FROM [master].[sys].[dm_os_sys_memory])
I am not able to figure out how will get information about Version (probably Select @@VERSION), Edition (probably from serverproperty) and core values and insert all those values into a table. Anyhelp with the syntax please?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Err...

INSERT INTO <table name> (<column list>)

VALUES (<list of values here>);

The version and edition you can get from SERVERPROPERTY. @@Version is a long string, not just the version itself.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chitown Hall of Fame Points: 3239 More actions · Answer 2

I kinda figured it out

Create table inventory_server(

ServerID int PRIMARY KEY,

Memory int,

Edition varchar(200),

Version varchar(200),

CoreCount int)

Declare @serverID int,

@Memory int,

@Edition varchar (200),

@Version varchar (200),

@CoreCount int

select @serverID = server_id from sys.servers

Select @Memory = (SELECT round (total_physical_memory_kb / 1024 /1024.0 ,0) AS "Total_Physical_Memory_In_GB"

FROM [master].[sys].[dm_os_sys_memory])

set @Edition = convert (varchar (200), (Select SERVERPROPERTY ('Edition')))

set @Version = convert (varchar (200), (Select SERVERPROPERTY ('ProductVersion')))

select @CoreCount = (select cpu_count from sys.dm_os_sys_info)

delete from dba.dbo.inventory_server

where @serverID = ServerID

insert into inventory_server (ServerID, Memory, Edition, Version, CoreCount)

select

@serverID,

@Memory,

@Edition,

@Version,

@CoreCount

Lowell SSC Guru Points: 323518 More actions · Answer 3

i have all my servers available in Registered servers/Central Management Servers, so i'll paste this and copy paste to excel to get a list off all my servers in a single document:

DECLARE @version NVARCHAR(32)

DECLARE @edition INT

DECLARE @SerialNumber VARCHAR(30);

DECLARE @cpu_count int;

DECLARE @hyperthreadratio int;

DECLARE @physicialCPUSockets int;

DECLARE @PhysicalMemoryMB int;

DECLARE @VirtualMachine varchar(50)

IF OBJECT_ID('tempdb.[dbo].[#CPUData]') IS NOT NULL

DROP TABLE [dbo].[#CPUData]

CREATE TABLE #CPUData (cpu_count int, hyperthread_ratio int,physicialCPUSockets int,PhysicalMemoryMB int,VirtualMachine varchar(50))

--SQL2014 and above, column is different

--select Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4) + '.' + Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)

IF Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)

+ '.'

+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)

IN('11.0','12.0','13.0')

BEGIN

INSERT INTO #CPUData([cpu_count],[hyperthread_ratio],[physicialCPUSockets],[PhysicalMemoryMB],[VirtualMachine])

EXEC(

'SELECT

s.cpu_count ,

s.hyperthread_ratio ,

s.cpu_count / s.hyperthread_ratio ,

s.physical_memory_kb/1024,

s.virtual_machine_type_desc

FROM sys.dm_os_sys_info s');

END

ELSE

BEGIN

INSERT INTO #CPUData([cpu_count],[hyperthread_ratio],[physicialCPUSockets],[PhysicalMemoryMB],[VirtualMachine])

EXEC(

'SELECT

s.cpu_count ,

s.hyperthread_ratio ,

s.cpu_count / s.hyperthread_ratio ,

s.physical_memory_in_bytes/1048576,

''''

FROM sys.dm_os_sys_info s');

END

IF EXISTS (SELECT * FROM sys.all_columns WHERe object_name(object_id) ='dm_os_sys_info' AND name = 'virtual_machine_type')

BEGIN

EXEC(

'UPDATE MyTarget

SET VirtualMachine = CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END

--SELECT CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END --1 = hypervisor, 2 = other type of virtual

FROM sys.dm_os_sys_info s

CROSS JOIN #CPUData MyTarget');

END

SELECT

@cpu_count = s.cpu_count ,

@hyperthreadratio = s.hyperthread_ratio ,

@physicialCPUSockets = s.physicialCPUSockets ,

@PhysicalMemoryMB = s.PhysicalMemoryMB,

@VirtualMachine = s.VirtualMachine

FROM #CPUData s ;

IF EXISTS(SELECT * from master.sys.objects where name = 'sqbutility' and type_desc = 'EXTENDED_STORED_PROCEDURE')

BEGIN

EXEC master..sqbutility

21,

@edition OUTPUT,

@version OUTPUT,

@SerialNumber OUTPUT;

END

ELSE

BEGIN

SELECT @version = '' ,

@edition = 0,

@SerialNumber = ''

END

SELECT --@@version,

Serverproperty('BuildClrVersion') AS BuildClrVersion,

Serverproperty('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS,

Serverproperty('Edition') AS Edition,

CASE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)

+ '.'

+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)

WHEN '9.00' THEN 'SQL 2005'

WHEN '10.0' THEN 'SQL 2008'

WHEN '10.50' THEN 'SQL 2008R2'

WHEN '11.0' THEN 'SQL 2012'

WHEN '12.0' THEN 'SQL 2014'

WHEN '13.0' THEN 'SQL 2016'

ELSE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)

+ '.'

+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)

END As SQLVersion,

Serverproperty('productversion') As ProductVersion,

Serverproperty('EditionID') AS EditionID,

Serverproperty('EngineEdition') AS EngineEdition,

Serverproperty('MachineName') AS MachineName,

Serverproperty('ProductLevel') AS ProductLevel,

Serverproperty('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,

Serverproperty('ResourceVersion') AS ResourceVersion,

Serverproperty('ServerName') AS ServerName,

ISNULL(Serverproperty('InstanceName'),'') AS InstanceName ,

CASE

WHEN CHARINDEX('(Hypervisor)',CONVERT(varchar(8000),@@version)) > 0

THEN '(Hypervisor)'

ELSE ''

END As VM,

@VirtualMachine AS VirtualMachine,

@version AS RedGateVersion,

@edition AS RedGateEdition,

@SerialNumber AS RedGateSerialNumber,

@cpu_count AS CPUCount,

@hyperthreadratio AS HyperThreadRatio,

@physicialCPUSockets AS PhysicalCPUSockets,

@PhysicalMemoryMB As [PhysicalMemory(MB)]

--EXEC sys.xp_readerrorlog 0,1,'cpus'

--select 1024 * 1024

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Chitown Hall of Fame Points: 3239 More actions · Answer 4

Chitown

Hall of Fame

Points: 3239

October 21, 2015 at 9:59 am

#1834717

Thanks!