April 26, 2016 at 1:40 am
The below query does not work across all versions(2005,2008,2012,2014,2016) of SQL server(not working on 2012)
How can I get the physical memory using t-sql across all versions of SQL Servers.
Thanks
select Servername = cast(@@servername as varchar(255))
,[min server memory (MB)] = cast ((select value from sys.configurations where name = 'min server memory (MB)') as varchar)
,[max server memory (MB)] = cast((select value from sys.configurations where name = 'max server memory (MB)')as varchar)
,[Physical memory (MB)]= CAST ((select physical_memory_in_bytes/1048576 as PhysicalMemoryMB from sys.dm_os_sys_info) as int)
,[max degree of parallelism]=CAST((select value from sys.configurations where name='max degree of parallelism')as int)
April 26, 2016 at 2:14 am
Did you read the error message?
Msg 207, Level 16, State 1, Line 13
Invalid column name 'physical_memory_in_bytes'.
Try using one of the other memory columns, such as physical_memory_kb. If you can't find a column name that's common to all versions (which would surprise me), you may have to use dynamic SQL or separate queries.
John
April 26, 2016 at 3:01 am
Yes, that column name has changed in 2012 and it is shown in kbs and bytes across these different versions.Hence this issue.
How do I get this working ?
I can't use different queries for diff versions.It has to be the same query as I will be running this as part of an SSIS package iterating over several servers to collect this data.
Is there any other common table/common query which can be used across all versions which can be used to get this physical memory?
April 26, 2016 at 3:29 am
If you're using SSIS, it's easy. Create a variable called SettingsQuery and set its value with an expression that changes according to what version it's running on. You can do it with pure T-SQL as well if you use dynamic SQL.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply