Hi,

I have inherited an application that was running against a SQL 7.0
database.  This application uses the SQLDMO to do things like add
tables to the database if changes to the app have been made, etc.  The
first problem we have run into in trying to run against the SQL Server
2k database is with a routine (see code below) that checks to see if
the database server is started:

Public Function StartSQLServer(ServerName As String, LoginName As
String, LoginPassword As String) As Boolean
    Dim ErrorString As String
    Dim SQLServer As New SQLDMO.SQLServer
    Dim Temp As String
    On Error Resume Next
    SQLServer.LoginTimeout = 60 ' 60 Seconds
    Err.Clear
    ' Start the Service

    SQLServer.Start True, ServerName, LoginName, LoginPassword
    If Err.Number > 0 Then
        If Err.Number = -2147023840 Or Err.Number = 1056 Or Err.Number
= 440 Then
            ' Server was already running.   Skip over this error
        Else
            ' Server cannot be started
            Temp = "Server Failed to Start." & vbCr & vbCr & "Error
Number: " & Err.Number & vbCr
            Temp = Temp & Err.Description
            MsgBox (Temp)
            StartSQLServer = False
            Exit Function
        End If
    End If
    ' Connect to Server
    Err.Clear
    SQLServer.Connect ServerName, LoginName, LoginPassword
    ' Verify Connection
    Err.Clear
    StartSQLServer = SQLServer.VerifyConnection
    Exit Function
End Function

This always returns false when running against the SQL Server 2k
server.  I thought the SQLDMO was supposed to work with 2000 even
thought it comes from 7.0.  Obviously there is a problem and perhaps
it is with the way the code above is written.  Does anyone have any
ideas?

I apologize if I have not been clear enough.  I don't entirely
understand the usage of the SQLDMO library.  If you need clarification
to help me solve this problem, please ask.

Thanks!

Mike Mahony

Mike,
 SQLDMO from 7.0 will return a version incompatibility error when used
against SQL 2000.  Just update your client and things should work ok.

--
I hope this helps
regards
Greg O MCSD
Document any SQL server database
AGS SQL Scribe http://www.ag-software.com/ags_scribe_index.asp

What is failing or does return an error is not really clear from your
message.

To take a guess. In SQL Server 2000 on a default installation standard SQL
Server security is turned off, only NT integrated security works, you
function will only work with standard security. There is a that can cause
you this pain. If you did specified a blank user id and password in 7.0 you
automatically got integrated security, where in 2000 you have to specify
LoginSecure = True.

Another one that I see often is the use of . (dot) as the server name. This
is a named pipes based representation of the local server, however in SQL
2000 the default client protocol is change to be TCP/IP where . (dot) has no
meaning, always use (local) or (local)\instancename instead.

Besides that what I do not like about the function that it tries to start
first, instead of checking the status of the service fist. If you would do
that you can dump the {*filter*}
If Err.Number = -2147023840 Or Err.Number = 1056 Or Err.Number = 440 Then
and replace it with something decant like

    Dim eStatus As SQLDMO_SVCSTATUS_TYPE
    eStatus = oSQLServer.Status

    cmdControl(2).Caption = "Pause"

    Select Case eStatus
        Case SQLDMOSvc_Continuing
            txtStatus = "Continuing"

        Case SQLDMOSvc_Paused
            txtStatus = "Paused"
            cmdControl(2).Caption = "Continue"
        Case SQLDMOSvc_Pausing
            txtStatus = "Pausing"
            cmdControl(2).Caption = "Continue"
        Case SQLDMOSvc_Running
            txtStatus = "Running"
        Case SQLDMOSvc_Starting
            txtStatus = "Starting"
        Case SQLDMOSvc_Stopped
            txtStatus = "Stopped"
        Case SQLDMOSvc_Stopping
            txtStatus = "Stopping"
        Case SQLDMOSvc_Unknown
            txtStatus = "Unknown"
    End Select

Also for checking service status you do not login information except when
working on Windows 9x, on NT this is not used.

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright ? SQLDev.Net 1991-2002 All rights reserved.

I'm also having major problems with (SQL V7.0 and the DMO) determining
the status of an SQLServer and its Agent (i.e. the JobServer).

In theory, what Gert says sounds logical but how can you check the
status of a server without having a link (not to be confused with a
'connection') to one.

In your example code you try and check the status of 'a' server, but
which one?

When you do this and you haven't even tried to Start a server then the
method call fails!

Correct me if I'm wrong but you have to at least know which server you
what to check the status of, and that means a call to either Connect
and/or Start.

The only way I managed to get the thing working is with liberal use of
the On Error Resume Next statement (which means no practical error
handling) and throwing Connects and Starts at the servers until they
start working.

If someone has a cleaner, better way to use the DMO, I'm all eyes.

Ivan