Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server VB.NET
I have a routine that checks to see if the SQL server is running on a network system before I attempt to run any SQL commands. During the check I get 2 different results: 1. Users have no issue, all works fine. 2.for other users the check of the controller status returns an exception "Cannot open MSSQLSERVER service on computer..." Again I am just checking the status of the service. I am not attempting to stop or start; just check. Any suggestions? Here is the code:
 
    Public Sub CheckForSQL(ByVal Server As String)
        Try
            Dim controller As New System.ServiceProcess.ServiceController("MSSQLSERVER")
 
            controller.MachineName = Server
 
            If controller.Status.Equals(System.ServiceProcess.ServiceControllerStatus.Running) AndAlso controller.CanStop Then
                'All good continue on
                SQLExists = True
                Return
            ElseIf controller.Status.Equals(System.ServiceProcess.ServiceControllerStatus.Stopped) Then
                controller.Start()
                SQLExists = True
                Return
            Else
                MsgBox("SQL Controller Status: " & controller.Status)
                SQLExists = False
                Return
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
            SQLExists = False
            Return
        End Try
    End Sub
Posted 27-Sep-12 6:15am
cmdErrX295
Comments
Sergey Alexandrovich Kryukov at 27-Sep-12 12:39pm
   
I voted 5 for this important question. The solution you suggested does not seem satisfactory to me, but the fact that you are asking a question tells me that you understand it. Your question even contains the seed of the resolution...
--SA

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

The problem with your solution is that in principle, the system might run more than one process under the name "MSSQLSERVER", and it could even be unrelated application with the same process name.
 
I would suggest a really strict thing. One of the communication method a service application can suggest would be the "present service". Perhaps, the simplest way it to self-host WCF and provide WCF service, presumably over the IPC channel (which, in turn, is based on OS named pipes). But the TCP channel is also good enough.
 
The client application tries to connect to the service application, to this service, and the failure to connect means the service is not there. This is a very clear method. In this way, not only you check up that the service is "formally" working, you make sure it is really working: all initializations were successful, etc.
 
In real life, I never created a special "presence service", because such service is could be added to some semantically significant service. For example, in one of the latest Windows service related architectures I designed, I created a separate "metadata service". It was used to pass the data model representing machine configuration from the service to the client. And, as a side dish, connection to this service served to indicate to the client application (with UI and everything) that the service is operational.
 
It's the best to create a remote object which is use for identification of the "presence" in a very last step of the service initialization, to make the client sure that everything is already operational, not just the fact of having the service running.
 
I think this is a clear resolution of your problem.
 
—SA
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 235
1 Sergey Alexandrovich Kryukov 144
2 Rob Philpott 100
3 Peter Leow 70
4 CHill60 70
0 OriginalGriff 6,902
1 Sergey Alexandrovich Kryukov 5,623
2 Maciej Los 3,479
3 Peter Leow 3,373
4 DamithSL 2,505


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 27 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100