question

DataJoe avatar image
DataJoe asked

How do you check for presence of registry entry?

I have looked over several threads including this one from this site without success. Can someone point me to a working script or what is it I am doing wrong. I am getting error message below. I am unable to trap this error with Try Catch. Thanks for ANY suggestion. I am specifically looking for error logs if present. Declare @datapath varchar(500); Declare @outvar varchar(500); Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014IT\MSSQLServer', @datapath Output; Print @datapath; Error Message: RegQueryValueEx() returned error 2, 'The system cannot find the file specified.' Msg 22001, Level 1, State 1
registry
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
You are missing a parameter for xp_regread. The parameters are - `@root` - in your case 'HKEY_LOCAL_MACHINE' - `@key` - in your case 'Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014IT\MSSQLServer' - `@value_name` - this is what is missing - `@value` - where to store the result So for example on my PC with a named instance SQL2014, I can get the default login with Declare @datapath varchar(500); exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer', N'DefaultLogin', @datapath output; print @datapath; Not sure what you mean by error logs, but the path to my ERRORLOG files can be found at Declare @datapath varchar(500); exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\Parameters', N'SQLArg1', @datapath output; print @datapath; but it's just a coincidence that my [startup parameter of -e][1] is the 2nd parameter listed (starts at 0), so it could be SQLArg0, SQLArg2, SQLArg3 etc... gives me -eD:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Log\ERRORLOG [1]: https://msdn.microsoft.com/en-us/library/ms190737.aspx#Anchor_1
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DataJoe avatar image
DataJoe answered
This script works providing you first enable logs by right clicking on SQL server logs. if not SQLs default number of logs is set to 6. So without that setting you get my previous error message that it can not find the file. What I am doing is running a script to check for CIS SQL2014 Compliancy. Thus is logs is not enabled I need to report it. How do you get Try/Catch or whatever script to trap that error? it is simply eluding me!! EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', 'Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014IT\MSSQLServer', 'NumErrorLogs', @NumErrLogs OUTPUT; Print @NumErrlogs; --SELECT ISNULL(@NumErrorLogs, 0) AS [NumberOfLogFiles];
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.