Using SQLDMO to backup a database.

Question

Using SQLDMO to backup a database.

Keith Garwood

SSC Veteran

Points: 244
More actions
March 2, 2006 at 1:25 pm

#90619

I have the following VB6 code that used to work but now fails on
    Set orss = Application.ServerGroups(1).RegisteredServers
with
-2147199735 [SQL-DMO]The passed ordinal is out of range of the specified collection.
I have tried everything I can think of including stopping my firewall (though not the XP firewall)
Any help would be most gratefully received.
The whole code
Public Sub sugBackUp()
    Dim nIndex As Long
    Dim obu     As SQLDMO.Backup2
    Dim odb     As SQLDMO.Database2
    Dim odbs    As SQLDMO.Databases
    Dim ors     As SQLDMO.RegisteredServer
    Dim orss    As SQLDMO.RegisteredServers
    Dim osvr    As SQLDMO.SQLServer2

    'Get registered server
    Set orss = Application.ServerGroups(1).RegisteredServers
    Set ors = orss.Item(1)
    Set osvr = New SQLDMO.SQLServer2
    osvr.Login = ors.Login
    osvr.LoginTimeout = 5
    osvr.Name = ors.Name
    osvr.Password = ors.Password

    'Get database
    On Error GoTo ConnectSecure
    osvr.Connect
    On Error GoTo 0
    Set odbs = osvr.Databases
    For nIndex = 1 To odbs.Count
        Set odb = odbs.Item(nIndex)
        If odb.Name = "JD" Then
            Exit For
        End If
    Next nIndex
    If Not odb.Name = "JD" Then
        MsgBox "Failed to find database."
        Exit Sub
    End If

    Set obu = New SQLDMO.Backup2
    obu.Database = "JD"
    obu.Files = "d:\dbbu\dbbu"
    obu.Action = SQLDMOBackup_Database
    Call obu.SQLBackup(osvr)

    Exit Sub
ConnectSecure:
    If osvr.LoginSecure Then
        MsgBox "Failed to connect to server."
    Else
        Set osvr = New SQLDMO.SQLServer2
        osvr.LoginSecure = True
        osvr.Name = ors.Name
        Resume
    End If

    Exit Sub
End Sub

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 1

You do not have any SQL Servers registered for the current user (which ever the user is when the program is run). If you open SQL Server Enterprise Manager when logged on the machine as that user, you will see no servers registered in the Console pane (left side).

This is often a problem the programmer doesn't consider when using DMO objects. The RegisteredServers object is based on the HKCurrentUser hive of the registry. If that user on that machine does not have any registered servers (or if SQL Server EM is not installed for that user/machine), then that registry key will have anything in it.

If you can, it would be better to to either explicitly declare the servers for your SQLDMO.SQLServer2 objects. Or, you can use the ListAvailableSQLServers method of the SQLDMO.Application object to query the network for servers. However, depending on network connection type and ports, some servers may not be discoverable in this way.

Hope this helps

Mark

Keith Garwood SSC Veteran Points: 244 More actions · Answer 2

Thanks for your reply. When I run EM I can see the database and previous backups made by running the code that now fails. It is a long time (approx. 18 months) since I last ran the code or EM for that matter. Might it help if I reinstall SQL Server ? The database is local and the machine is not part of a domain and only has one logon.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

Unless the machine is Windows 98 or prior, it can still have multiple logon accounts. The key is what account is the program running under. If it is being run under a task scheduler for example, it well could be a different account than what you had run EM under. The problem is that the program is depending on HK_CurrentUser registry hive for its account it is running under, and for some reason, that registry entry for RegisteredServers is not available.

I suggest you may be better off by changing the program to use another method, instead of depending on anything in HK_CurrentUser. An ini file, hardcode arraylist in the program, or the ListAvailableSQLServers method.

Mark

Keith Garwood SSC Veteran Points: 244 More actions · Answer 4

Many thanks. The machine runs XP Pro. The service logon is the same as the machines only logon.

barsuk SSChampion Points: 12280 More actions · Answer 5

Not sure if the following is true in your case, but: about 6 months ago I created few VB6/SQLDMO projects on my computer to run against our 250+ SQL Server 2000. With installation of SQL Server 2005 in our environment, SQLDMO stopped working ( it is a known fact), so I had to rewrite it in SQL2005 SMO by using VisualStudio2005. SQL Server 2005 also installed on my machine. When I was trying to run my old VB6/SQLDMO project couples weeks ago I got a a similar message. So if (IF) VS2005/SQL Server 2005 are installed on your box, you might want to re-write it by using SQL Server SMO...

Keith Garwood SSC Veteran Points: 244 More actions · Answer 6

Thanks for your reply. I installed VS2005 beta sometime ago. After playing around with some other SQLDMO projects I also found that the server name was incorrect when retrieved using SQLDMO.Application.ListAvailableServers. I can connect to the server (using its correct name) using SQLDMO.SQLServer2. I guess I will just have to do a workaround for the app. when running on my machine. Thanks again.