SQLDMO from version 7 having problems with SQL Server 2000 
Author Message
 SQLDMO from version 7 having problems with SQL Server 2000

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



Mon, 07 Feb 2005 04:40:19 GMT
 SQLDMO from version 7 having problems with SQL Server 2000

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


Quote:
> 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



Mon, 07 Feb 2005 05:45:12 GMT
 SQLDMO from version 7 having problems with SQL Server 2000
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.


Quote:
> 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



Mon, 07 Feb 2005 11:44:20 GMT
 SQLDMO from version 7 having problems with SQL Server 2000
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?

Quote:
>     Dim oSQLServer As SQLDMO.SQLServer     ' added for clarity
>     oSQLServer = New SQLDMO.SQLServer
>     Dim eStatus As SQLDMO_SVCSTATUS_TYPE
>     eStatus = oSQLServer.Status

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



Tue, 01 Mar 2005 20:28:28 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Upgrading SQL Server 7.0 to SQL Server 2000 - Meta Data Repository Database Version Problem

2. SQLDMO Problem in SQL Server 2000, Please help

3. beta testing SQL Server 2000 and having problem with timeouts

4. Restore database from SQL Server 2000 to SQL Server 2000 (beta 2 version)

5. moving from Sql server 2000 evaluation to full version of Sql server 2000

6. Problem connecting to different versions of SQL server from SQL 2000 client

7. Problem in Bulk Copy from SQLDMO object, Server 2000

8. SQL Server 2000 Trial Version Installation Problem

9. Blind user having problems with Oracle 8i and Windows 2000 Server

10. Linking SQL Server version 7.0 to SQL Server 2000

11. Problem upgrading from SQL Server 2000 Trial to Developer Version

12. SQLDMO not working in SQL Server 2000 installation


 
Powered by phpBB® Forum Software