Archive

Posts Tagged ‘xp_instance_regred’

How to check SQL Server version – TSQL

13.05.2011 1 comment

The most common way to check SQL Server is to use @@VERSION configuration function. It returns version, architecture, OS version and build date for current instance.

— © 2011 – Vishal (http://SqlAndMe.com)

SELECT @@VERSION AS [Version]

Version
—————————————————————————————————————————————————————————————————————————————————————-
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (Intel X86)
       Apr  2 2010 15:53:02
       Copyright (c) Microsoft Corporation
       Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

(1 row(s) affected)

Another way is to use SERVERPROPERTY() metadata function. For a full list of properties that can be returned, check BOL.

— © 2011 – Vishal (http://SqlAndMe.com)

SELECT SERVERPROPERTY('ProductVersion') AS [Version]

Version
—————————————————————————————————————————————————————————————————————————————————————-
10.50.1600.1

(1 row(s) affected)

 

You can also use extended stored procedures to check SQL Server version:


sp_MSgetversion:

— © 2011 – Vishal (http://SqlAndMe.com)

EXEC master..sp_MSgetversion

Character_Value
——————– ———– ———–
10.50.1600.1         1           3

(1 row(s) affected)


xp_msver:

— © 2011 – Vishal (http://SqlAndMe.com)

EXEC master..xp_msver 'ProductVersion'

Index  Name                             Internal_Value Character_Value
—— ——————————– ————– ————————————————————————————————————————————————————————————————
2      ProductVersion                   655410         10.50.1600.1

(1 row(s) affected)

To see the full list of properties returned by xp_msver, execute it without any arguments.

— © 2011 – Vishal (http://SqlAndMe.com)

 

EXEC master..xp_msver

Index  Name                             Internal_Value Character_Value
—— ——————————– ————– ————————————————————————————————————————————————————————————————
1      ProductName                      NULL           Microsoft SQL Server

2      ProductVersion                   655410         10.50.1600.1

3      Language                         1033           English (United States)

4      Platform                         NULL           NT INTEL X86

5      Comments                         NULL           SQL

6      CompanyName                      NULL           Microsoft Corporation

7      FileDescription                  NULL           SQL Server Windows NT

8      FileVersion                      NULL           2009.0100.1600.01 ((KJ_RTM).100402-1540 )

9      InternalName                     NULL           SQLSERVR

10     LegalCopyright                   NULL           Microsoft Corp. All rights reserved.

11     LegalTrademarks                  NULL           Microsoft SQL Server is a registered trademark of Microsoft Corporation.

12     OriginalFilename                 NULL           SQLSERVR.EXE

13     PrivateBuild                     NULL           NULL

14     SpecialBuild                     104857601      NULL

15     WindowsVersion                   393347078      6.0 (6002)

16     ProcessorCount                   2              2

17     ProcessorActiveMask              3              00000003

18     ProcessorType                    586            PROCESSOR_INTEL_PENTIUM

19     PhysicalMemory                   2041           2041 (2140626944)

20     Product ID                       NULL           NULL

(20 row(s) affected)

-– © 2011 – Vishal (http://SqlAndMe.com)


xp_instance_regread:

DECLARE @returnValue NVARCHAR(100)
EXEC   master..xp_instance_regread
       @rootkey              = N'HKEY_LOCAL_MACHINE',
       
@key                     = N'SOFTWAREMicrosoftMSSQLServerSetup',
       
@value_name       = N'Version',
       
@value                  = @returnValue output
SELECT @returnValue AS [Version]

Version
—————————————————————————————————-
10.50.1600.1

(1 row(s) affected)

Hope This Helps!

Vishal

Accessing Registry using XPs (contd..) – TSQL

10.05.2011 3 comments

While xp_regread read values from registry under exact path specified. To read instance specific registry entries from registry you can use xp_instance_regenumvalues and xp_instance_regread.

xp_instance_regread translates the given path to instance-specific path in the registry:

for example, executing following code against a SQL 2008 Instance returns the default database location which is specific to that instance:

— © 2011 – Vishal (http://SqlAndMe.com)

 

DECLARE @returnValue NVARCHAR(500)

EXEC   master..xp_instance_regread

       @rootkey      = N'HKEY_LOCAL_MACHINE',

       @key          = N'SOFTWAREMicrosoftMSSQLServerSetup',

       @value_name   = N'SQLDataRoot',

       @value        = @returnValue output

PRINT @returnValue

[EDIT: Above path returns the default installation data directory, to get default data directory use path: SOFTWAREMicrosoftMicrosoft SQL ServerMSSQLServer]

this returns:

C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQL

 

Now, if I execute the same code against a SQL 2005 Instance on my system, it returns:

 

C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQL

 

If the key path cannot be translated to instance-specific path, it will read from the specified key path.

 

Hope This Helps!

Vishal