SQL: Read from the registry

Now that you have your SQL Agent accounts set up to use DatabaseMail let’s check the registry.

--
-- We have to know the output values to prevent return nothing
--
DECLARE @nvcREG_SZ NVARCHAR(4000),
    @nREG_DWORD INT,
    @vbREG_BINARY VARBINARY

EXEC [master].[dbo].[xp_instance_regread]
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
    N'UseDatabaseMail',
    @nREG_DWORD OUTPUT
SELECT  @nREG_DWORD AS [UseDatabaseMail]

EXEC [master].[dbo].[xp_instance_regread]
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
    N'DatabaseMailProfile',
    @nvcREG_SZ OUTPUT
SELECT  @nvcREG_SZ AS [DatabaseMailProfile]

The nice thing about xp_instance_regread is it will figure out what instance of SQL you are running and so e.g.

 N'HKEY_LOCAL_MACHINE',
 N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent' 

becomes

 N'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\SQLServerAgent'