I spent some time this week tightening up the code in a project that will actually be released into the wild. I wanted to make sure that I catch as many errors as possible and put up user friendly messages.
The program uses SMO and RMO to synchronize a local SQL Express database with a master database over the internet. The program would get some pretty cryptic error messages if SQL Express wasn’t installed, if the expected instance weren’t available or if the replication components weren’t installed.
I was easily able to check if SQL Express was installed and to get a list of the installed instances, but I couldn’t find a way to check if the replication components were installed. This was annoying because the replication components aren’t installed by default when SQL Express is installed and I was assuming that this situation would come up often.
After some refactoring, I was able to get it to throw an exception with a pretty straight forward error message: “Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication.” In fact, I could catch the exception and put up whatever error message I wanted. Unfortunately, this exception would not occur until the program spent quite a while preparing to do the synchronization. I really wanted to check for the replication components before I attempted any synchronization.
I spent quite a while googling trying to find something that would work, but couldn’t find anything. Then I tried checking for the existence of several stored procedures, tables and entries in the SysObjects tables, but nothing I found on a server with the replication components was missing on one without them.
Then I came across the sp_MS_replication_installed system stored procedure. It’s on all installations and tells exactly what I needed. I was surprised that with all the searching I did, there was no mention of this handy stored procedure, so I figured I would do my part and document what I found.
The stored procedure is simple enough in that it returns a 0 if the replication components are installed and a 1 if they aren’t. However, under usual circumstances, it goes a step further and raises an exception if the components aren’t installed. This is because the procedure reads an entry in the registry to see if the replication components are installed and if they aren’t, it is likely that the entry was never written in the first place. When it can’t find the registry entry, an exception is thrown. The only time the stored procedure would return a 1 without throwing an exception is if the replication components were installed at some point and then later uninstalled.
1: USE [master]
2: GO
3: /******Object: StoredProcedure [sys].[sp_MS_replication_installed]******/
4: SET ANSI_NULLS ON
5: GO
6: SET QUOTED_IDENTIFIER ON
7: GO
8:
9: --
10: -- Name: sp_MS_replication_installed
11: --
12: -- Descriptions:
13: --
14: -- Parameters: as defined in create statement
15: --
16: -- Returns: 0 - success
17: -- 1 - Otherwise
18: --
19: -- Security:
20: -- Requires Certificate signature for catalog access
21: --
22: ALTER procedure [sys].[sp_MS_replication_installed]
23: as
24: set nocount on
25: declare @isinstalled int
26: select @isinstalled = 0
27: declare @retcode int
28:
29: EXECUTE @retcode = master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
30: 'SOFTWARE\Microsoft\MSSQLServer\Replication',
31: 'IsInstalled',
32: @param = @isinstalled OUTPUT
33:
34: IF ( @retcode <> 0 ) or ( @@ERROR <> 0 )
35: begin
36: raiserror (21028, 16, -1)
37: return (0)
38: end
39:
40: if (@isinstalled is null or @isinstalled = 0)
41: begin
42: raiserror (21028, 16, -1)
43: return (0)
44: end
45:
46: return (1)
I could use the sp_MS_replication_installed stored procedure as is and catch the exception when it got thrown, but I really don’t like code that relies on exceptions to operate correctly. Exceptions should be just that – the exception.
1: private static bool replicationInstalled(string connString) {
2: bool result = false;
3: using (SqlConnection conn = new SqlConnection(connString)) {
4:
5: conn.Open();
6: using (SqlCommand cmd = new SqlCommand()) {
7: cmd.Connection = conn;
8: cmd.CommandType = CommandType.StoredProcedure;
9: cmd.CommandText = "sp_MS_replication_installed";
10: cmd.Parameters.Add("Result", SqlDbType.Int).Direction
11: = ParameterDirection.ReturnValue;
12: cmd.ExecuteNonQuery();
13: result = ((int)cmd.Parameters["Result"].Value) == 0;
14: }
15: conn.Close();
16: }
17: return result;
18: }
I took a look at the sp_MS_replication_installed stored procedure and found that all it is doing is calling xp_instance_regread and doing error handling. Since I didn’t like the error handling that sp_MS_replication_installed was doing, I figured I would call xp_instance_regread myself and do my own error handling.
private static bool replicationInstalled(SqlConnection conn) {
bool result = false;
using (SqlCommand cmd = new SqlCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "xp_instance_regread";
cmd.Parameters.AddWithValue("param1", "HKEY_LOCAL_MACHINE");
cmd.Parameters.AddWithValue("param2", "SOFTWARE\\Microsoft\\MSSQLServer\\Replication");
cmd.Parameters.AddWithValue("param3", "IsInstalled");
cmd.Parameters.AddWithValue("@IsInstalled", 0);
cmd.Parameters["@IsInstalled"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
object returnValue = cmd.Parameters["@IsInstalled"].Value;
if (returnValue != null && returnValue is int) {
result = ((int)returnValue) == 1;
}
}
return result;
}
I changed the replicationInstalled function to call xp_instance_regread directly. If the registry key exists, the @IsInstalled parameter returns the value from the registry. That value tells whether the replication components are installed. If there is no value in the registry, the @IsInstalled parameter returns a null. I take the value of @IsInstalled and check to see if it is null. If it is null, I return false. If it is not null and it is indeed an integer, I return that integer.
Using the new replicationInstalled function my program is able to quickly check if the replication components are installed before attempting to do any synchronization and without throwing any exceptions. Mission accomplished!