Click here to Skip to main content
12,452,015 members (24,454 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB 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 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160826.1 | Last Updated 27 Sep 2012
Copyright © CodeProject, 1999-2016
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