Friday, October 29, 2010

Windows OS versioning and problem with @@version

Today I came across a problem in getting the OS name with @@version variable in SQL Server.The @@version  variable returns the build of NT engine(say Windows NT 5.2,6.0).If its 5.2 then the OS would be windows 2003 server, 6.0 indicates Vista.

Now if you are running on Windows7 or Windows2008 R2 then @@version will return Windows NT 6.1.
This is confusing since DBA can't make out whether the operating System is Windows 7 or Windows2008 R2.The reason is because Windows 2008 R2 uses the same engine of Windows 7.
The solution is you use command line utility systeminfo.exe which will display detailed configuration information about a computer and its operating system, including operating system configuration, security information, product ID, and hardware properties, such as RAM, disk space, and network cards.

If you are running on SQL Server 2005 or SQL Server 2008 then this utility can be executed using xp_cmdshell.
Please don't forget to enable xp_cmdshell  using sp_configure

Below attached is the sample script that will get you OS Name.

create table #tmpOSName

(Details varchar(8000))

insert into #tmpOSName(Details)

exec master..xp_cmdshell 'systeminfo'

select rtrim(ltrim(SUBSTRING(Details,9,LEN(Details)))) from #tmpOSName where Details like 'OS Name%'
drop table #tmpOSName



Please note this is dependent on xp_cmdshell ; so that needs to be enabled first.

1 comment: