LISTING 3.  Two Procedures That Support the Example Application


Function server_list() As String
On Error GoTo server_list_trap
Dim srv1 As SQLDMO.SQLServer

'Attempt to connect to SQL Server 2000 server
Set srv1 = New SQLDMO.SQLServer
srv1.Connect "cab2000", "sa", ""

'If successful, set server_list
'to 7.0 and 2000 server names
server_list = "cab2000;cab2200;cabarmada"

server_list_exit:
srv1.Close
Set srv1 = Nothing
Exit Function

server_list_trap:
If Err.Number = -2147165949 Then
'If connect attempt fails, set
'server_list to just 7.0 server names
    server_list = "cab2200;cabarmada"
Else
    Debug.Print Err.Number, Err.Description
    Debug.Print server_list
    MsgBox "Unanticipated error with error number and " & _
        "description of: " & Err.Number & ";  " & Err.Description
    server_list = ""
End If

'If connect attemp fails, then you clean up
'without closing the SQLServer object
Set srv1 = Nothing
Exit Function

End Function


Function stored_proc_list(srvname As String, dbsname As String) As String
Dim srv1 As SQLDMO.SQLServer
Dim stp1 As SQLDMO.StoredProcedure
Dim str1 As String

'Initialize str1 and srv1
str1 = ""
connect_to_server srvname

'Loop through
For Each stp1 In Module1.srv1.Databases(dbsname, "dbo").StoredProcedures
    If stp1.SystemObject = False Then
        str1 = str1 & stp1.Name & ";"
    End If
Next
str1 = Left(str1, Len(str1) - 1)
stored_proc_list = str1

End Function