August 25, 2010 at 1:23 pm
Hello everyone,
I'm trying to write a script that will provide the state of each SQL Server service installed on a server and I need to properly handle an error if a particular SQL Server service is not installed.
Here is basically what I'm trying to do:
BEGIN TRY
EXEC master.dbo.xp_servicecontrol N'QUERYSTATE', N'ReportServer'
END TRY
BEGIN CATCH
PRINT 'Reporting Service is not service installed on this server.'
END CATCH
When executed I getting the following error
Msg 22003, Level 16, State 1, Line 0
OpenService() returned error 1060, 'The specified service does not exist as an installed service.'
Anyone have an idea how to trap this error so that a proper error message can be displayed without stopping the rest of the T-SQL code?
By the way, I have xp_cmdshell turned off and using the GUI is not an option as I would like to execute the final code against many SQL servers.
Thank you in advance for your time and efforts,
Rudy
Rudy
August 25, 2010 at 2:36 pm
You could use xp_regread and parse the RS instance name keys in the registry!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 25, 2010 at 2:40 pm
Interesting. I'll have to look into that.
The issues with this solution is that depending on the OS installed on the server, the location of that information may be in different places.
But it's worth a try.
Thanks.
Rudy
August 25, 2010 at 2:45 pm
Not just the OS but the version of SQL too. You'll have to select the sql server version and bind to the appropriate keys in the registry!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 25, 2010 at 2:46 pm
Not just the OS but the version of SQL too. You'll have to select the sql server version and bind to the appropriate keys in the registry!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 25, 2010 at 4:05 pm
For the title of the thread, how does this work?
select *
from sys.databases --(or dbo.sysdatabases for SQL 2000)
where name like 'ReportServer%'
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 25, 2010 at 8:31 pm
That doesn't really work if the RS database is remote to the RS instance. It also makes assumptions regarding the naming context of the RS database.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 25, 2010 at 10:06 pm
Perry Whittle (8/25/2010)
That doesn't really work if the RS database is remote to the RS instance. It also makes assumptions regarding the naming context of the RS database.
You can have a reporting service instance on a server with all of it databases on a completely separate server. Using SQL queries won't work in all scenarios. You will need to write code that asks Windows what is installed.
There are plenty of examples of how to do this - search for something like "wmi to get services". You should find something that will do the job for you.
August 25, 2010 at 10:21 pm
Ya, wmi is good but you can get just about any info from the registry ( if you know which keys to find it in) and SQL Server provides a set of extended stored procedures to manipulate the registry!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2010 at 4:37 am
If Reporting Services is installed using a SQL Server database on a different server, the extended stored procedures won't help you. They can't be used to read the registry on a different server.
August 26, 2010 at 6:06 am
not worried about the database, just need to enumerate the RS instance registry keys on the server where RS is installed, this will provide the info required
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2010 at 6:15 am
I'm assuming that this would be the same if you wanted to check for Analysis Services, Integration Service and Full Text Search.
Do you know where I could find the information in the registry? If not I would have to do some poking around.
Thanks again for all the great help.
Rudy
August 26, 2010 at 9:24 am
For SQL Server 2008 the main hive is at the following location
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names
If SSRS is installed there will be an RS key in here
If DE is installed there will be an SQL key in here
If SSAS is installed there will be an OLAP key in here
For windows version check
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion
All of these may be checked using the extended stored procedures within SQL Server!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2010 at 9:35 am
Thanks for help.
What about this location:
HKLM\System\CurrentControlSet\Services
It seems to have all the information even for the instances
Rudy
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply