locked
Query the registry RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Good afternoon,

    I've got a little puzzle that hopefully has a simple solution, however, as the registry is involved, I'm betting it's not the case.

    Now, I'm aware of how to query the registry for certain keys, and subsequently I'm able to query my local settings to determine the default profile for SQL Server Agent alarms.  However, I want to roll a similar thing out to all our production servers.

    We run a combination of SQL 2005 and 2008, some are x86 and some x64, some servers have multiple instances, some dont.

    I'm able to run the below to find the correct value within my local system, 

    declare @outvar varchar(100)
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\SQLServerAgent','DatabaseMailProfile',@outvar OUTPUT
print @outvar

    however, when I do this on a server, then this doesnt work as the key path is different.

    Does anybody know how to query the registry through t-sql (or to query SQL itself) to determine the location of the 'DatabaseMailProfile' registry entry.

    What I really need to do is to be able to derive the 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\SQLServerAgent' part of the code so that this will run on any server, no matter the version/install type.

    Ideally I could do "select * from sys.<table> where RegistryEntryName = 'DatabaseMailProfile'" and it'll return 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\SQLServerAgent', tho I bet that isnt likely :)

    Regards

    Andy

    Thursday, September 13, 2012 11:20 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    Hello Andy,

    You can use the (undocumented) xp_instance_regread to query Registry independend of the instance/version:

    DECLARE @returnValue NVARCHAR(500)

EXEC   master..xp_instance_regread 
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
       @value_name   = N'DatabaseMailProfile',
       @value        = @returnValue output
 
PRINT @returnValue

    Olaf Helper
    Blog Xing

    • Proposed as answer by Naomi N Thursday, September 13, 2012 12:51 PM
    • Marked as answer by AndyB1978 Thursday, September 13, 2012 2:40 PM
    Thursday, September 13, 2012 12:42 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Andy

    It is supported by via SQL Server 2012 

    SELECT *

    FROM sys.dm_server_registry

     sys.dm_server_services

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance


    Thursday, September 13, 2012 11:27 AM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks Uri,

    Sadly those views arent present in SQL 2005 or 2008 tho.

    Regards

    Thursday, September 13, 2012 11:48 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Well, time to upgrade :-))))

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Thursday, September 13, 2012 11:49 AM
    Answerer
  • Question
    Sign in to vote
    1
    Sign in to vote

    The key is different depening on the instances installed on the server, so maybe you can try to build the reg key dynamically. If you look in 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceNames\SQL then you have the different instances installed on the server. The data value for these listed instances, is the one you need in building your string to the SQLServerAgent key.

    I can't remember how different these paths are from SQL version to SQL version (and OS version) but maybe you can get around this. It might be a bit cumbersone to get working but it should be possible..:-)

    Steen Schlüter Persson (DK)

    Thursday, September 13, 2012 12:16 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks Steen,

    A little investigation shows me the following info :

    SQL 2008 R2 : 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\SQLServerAgent'

SQL 2008 : 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\SQLServerAgent'

SQL 2005 : 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent'

    So I can probably work around this.  If we have multiple instances, I'll have to figure out something around that too.
    Thursday, September 13, 2012 12:21 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    It's on the cards, tho I've only worked at my current place for 3 months.. perhaps a little early to suggest spending $1,000,000 on a company wide upgrade ;)

    I'll give that another couple of weeks I think...

    Thursday, September 13, 2012 12:23 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    You should be able to solve the instance issue by querying the ..\InstanceNames\ key and get the Instance info from there.

    Steen Schlüter Persson (DK)

    Thursday, September 13, 2012 12:23 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    $1,000,000????

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Thursday, September 13, 2012 12:29 PM
    Answerer
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hello Andy,

    You can use the (undocumented) xp_instance_regread to query Registry independend of the instance/version:

    DECLARE @returnValue NVARCHAR(500)

EXEC   master..xp_instance_regread 
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
       @value_name   = N'DatabaseMailProfile',
       @value        = @returnValue output
 
PRINT @returnValue

    Olaf Helper
    Blog Xing

    • Proposed as answer by Naomi N Thursday, September 13, 2012 12:51 PM
    • Marked as answer by AndyB1978 Thursday, September 13, 2012 2:40 PM
    Thursday, September 13, 2012 12:42 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Exactly what I was looking for Olaf, thanks...

    Lack of sleep and excess coffee had helped me get some form of solution, but yours is FAR better, thanks.

    My version (if you want to amuse yourself at how lack of sleep affects the brain) is below.

    -- DECLARE THE VARIABLES
DECLARE @returnValue NVARCHAR(500)
DECLARE @SQL VARCHAR(MAX) -- Might not need to be a MAX here... depends how many results are returned.

-- TEMP TABLE TO HOLD RESULTS 
CREATE TABLE #tempInstanceNames
(
      InstanceName      NVARCHAR(100),
      RegPath           NVARCHAR(100),
      SQLAgentMailProfile   NVARCHAR(MAX)
)

-- POPULATE THE CURRENT INSTANCE INSTALL LOCATION
EXEC   master..xp_instance_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\Microsoft\MSSQLServer\Setup',
       @value_name   = N'SQLDataRoot', 
       @value        = @returnValue output
--PRINT @returnValue
 
-- GET A LIST OF ALL SERVER INSTANCES
INSERT INTO #tempInstanceNames (InstanceName, RegPath)
EXEC   master..xp_instance_regenumvalues
       @rootkey = N'HKEY_LOCAL_MACHINE',
       @key     = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'
 
-- Build dynamic SQL
SET @SQL = 'DECLARE @returnValue NVARCHAR(100)'

/*
BASED ON THE CURRENT INSTANCE CONFIGURATION, QUERY THE REGISTRY FOR THE DatabaseMailProfile KEY AND VALUES.
UPDATE temp TABLE WITH THIS INFORMATION
*/
SELECT @SQL = @SQL + CHAR(13) +
'EXEC   master.dbo.xp_regread
@rootkey      = N''HKEY_LOCAL_MACHINE'',
@key          = N''SOFTWARE\Microsoft\Microsoft SQL Server\' + RegPath + '\SQLServerAgent'',
@value_name   = N''DatabaseMailProfile'', 
@value        = @returnValue OUTPUT;
 
UPDATE #tempInstanceNames SET SQLAgentMailProfile = @returnValue
 WHERE RegPath = ''' + RegPath + '''' + CHAR(13) FROM #tempInstanceNames
 
-- RUN THE SQL
EXEC (@SQL)

-- RETRIEVE THE DATA REQUIRED AND MESS AROUND WITH THE STRING TO GRAB THE INFO I WANT
-- IF THE SQLAgentMailProfile is NULL then the database server probably cannot send emails out to operators when a job fails.  This should be corrected.
SELECT     SQLAgentMailProfile -- InstanceName, RegPath, SQLAgentMailProfile
 FROM        #tempInstanceNames 
WHERE RegPath = RIGHT(left(@returnValue,len(@returnValue)-6),CHARINDEX('\',REVERSE(left(@returnValue,len(@returnValue)-6)))-1)

DROP TABLE #tempInstanceNames

    Thursday, September 13, 2012 2:43 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Very poor reference to Austin Powers I'm afraid.. too little sleep over the last few days :D
    Thursday, September 13, 2012 2:44 PM