a tough one, regarding sql server databases

[JimMuglia]

Greetings,

OK, here's one that's got me intimidated. Someone just pointed me in the right direction regarding allowing the user to browse for an mdb (Microsoft Access database) file, and it works great.

Now, I need to allow the user to select any of the existing SQL Server 7.0 databases on the system. I know its not the same as browsing for a file. So what would I need to do for the event cmdSQLServer_Click to allow the user to select a SQL Server 7.0 database?

Thank you,
Jim

[JimMuglia]

Would it still somehow be possible to use the browse for file dialog box for this somehow, or does it require another direction?

[JimMuglia]

What? N0 one has an answer after several hours?

[freewilly]

What you are asking is quite straight forward if you are using ADO or ODBC. How are you connecting to the mdb?

[JimMuglia]

Originally posted by freewilly
What you are asking is quite straight forward if you are using ADO or ODBC. How are you connecting to the mdb?

ADO. But how can we browse for soemthing that is not a file?

[freewilly]

I'll do bit of research to find out how to get a list of available SQL Servers. What you will end up with is a list of Servers similar to the list you get when making an ODBC connection.

I'll have a look within the hour. Get back to me me if you find the answer before that.

[JimMuglia]

Hey Freewilly, or Anybody,

Did anyone figure anything out. I keep looking around and finding nothing.

Heeelllppp!!!

I just need to click on abutton and have a list of SQL Server databases displayed for the user to choose from. How do we do it since we can't necesarily browse for one?

Thank you,
Jim

[techgnome]

Use SQLDMO ... I know it's possible to get not only a list of SQL Servers, but once connected, a list of databases on that server.
Give me a few and I will see if I can find the code.

[seoptimizer2001]

I don't have the code for this, but my thoughts are that you would first look for the available DSN's on the computer and then connect using each, which you would then be able to find out more about the DB, i.e. Databases, tables, etc.

[JimMuglia]

Originally posted by seoptimizer2001
I don't have the code for this, but my thoughts are that you would first look for the available DSN's on the computer and then connect using each, which you would then be able to find out more about the DB, i.e. Databases, tables, etc.

So what's the code for searching for a DSN, since its not a directory search?

[seoptimizer2001]

File DSN's are in C:\Program Files\Common Files\ODBC\Data Sources

[techgnome]

okay.... here it is......

Private Sub cmdConnect_Click()
    If chkUseNT Then
        Login cboServername, "", "", chkUseNT
    Else
        Login cboServername, txtUserName, txtPassWord, chkUseNT
    End If
End Sub
 
Sub FillComboFromCollection(oCollection As Object, cbo As ComboBox)
    On Error GoTo HandleError
    cbo.Clear
    
    Dim oObject As Object
    For Each oObject In oCollection
        Select Case oObject.TypeOf
        Case SQLDMOObj_TargetServer
            cbo.AddItem oObject.ServerName
            
        Case SQLDMOObj_RemoteLogin
            cbo.AddItem oObject.LocalName
            
        Case SQLDMOObj_LinkedServerLogin
            cbo.AddItem oObject.LocalLogin
            
        Case Else
            cbo.AddItem oObject.Name
        End Select
NoValue:
    Next oObject
    
    Exit Sub
    
HandleError:
    Debug.Print Err, Err.Description
    Resume NoValue
 
End Sub
 
Public Sub Login(ServerName As String, UserName As String, Password As String, Optional UseNT As Boolean = False)
    
    NL = Chr$(13) & Chr$(10)
    bConnected = False
    
    Set oSQLServer = New SQLDMO.SQLServer
    oSQLServer.LoginTimeout = 10
    oSQLServer.ODBCPrefix = False
    oSQLServer.ApplicationName = "SQL Documentor"
    oSQLServer.LoginSecure = UseNT
    oSQLServer.Connect ServerName, UserName, Password
    oSQLServer.Application.GroupRegistrationServer = ""
    
    oSQLServer.Configuration.ShowAdvancedOptions = True
    
    Set oJobServer = oSQLServer.JobServer
    Set oReplication = oSQLServer.Replication
    Set oConfiguration = oSQLServer.Configuration
    
    
    FillComboFromCollection oSQLServer.Databases, frmTableSelector.cboDatabases
End Sub

It's going to take some tweaking..... but I think you can follow it ..... it's a chunk from an Excell add in we created to login to a SQL Server, then allows the user to select a DB, then run some DB functions..... but anyway.... the key pieces are here.
Good luck!

[techgnome]

Originally posted by seoptimizer2001
File DSN's are in C:\Program Files\Common Files\ODBC\Data Sources

Not every one uses DSNs....

[seoptimizer2001]

Originally posted by techgnome
Not every one uses DSNs....

Well I would think that it would be a good place to start looking. Besides that I don't know how else you could check "Available" SQL servers to the machine if you don't know what servers you are looking for.

[JimMuglia]

Originally posted by techgnome
okay.... here it is......

Private Sub cmdConnect_Click()
    If chkUseNT Then
        Login cboServername, "", "", chkUseNT
    Else
        Login cboServername, txtUserName, txtPassWord, chkUseNT
    End If
End Sub
 
Sub FillComboFromCollection(oCollection As Object, cbo As ComboBox)
    On Error GoTo HandleError
    cbo.Clear
    
    Dim oObject As Object
    For Each oObject In oCollection
        Select Case oObject.TypeOf
        Case SQLDMOObj_TargetServer
            cbo.AddItem oObject.ServerName
            
        Case SQLDMOObj_RemoteLogin
            cbo.AddItem oObject.LocalName
            
        Case SQLDMOObj_LinkedServerLogin
            cbo.AddItem oObject.LocalLogin
            
        Case Else
            cbo.AddItem oObject.Name
        End Select
NoValue:
    Next oObject
    
    Exit Sub
    
HandleError:
    Debug.Print Err, Err.Description
    Resume NoValue
 
End Sub
 
Public Sub Login(ServerName As String, UserName As String, Password As String, Optional UseNT As Boolean = False)
    
    NL = Chr$(13) & Chr$(10)
    bConnected = False
    
    Set oSQLServer = New SQLDMO.SQLServer
    oSQLServer.LoginTimeout = 10
    oSQLServer.ODBCPrefix = False
    oSQLServer.ApplicationName = "SQL Documentor"
    oSQLServer.LoginSecure = UseNT
    oSQLServer.Connect ServerName, UserName, Password
    oSQLServer.Application.GroupRegistrationServer = ""
    
    oSQLServer.Configuration.ShowAdvancedOptions = True
    
    Set oJobServer = oSQLServer.JobServer
    Set oReplication = oSQLServer.Replication
    Set oConfiguration = oSQLServer.Configuration
    
    
    FillComboFromCollection oSQLServer.Databases, frmTableSelector.cboDatabases
End Sub

It's going to take some tweaking..... but I think you can follow it ..... it's a chunk from an Excell add in we created to login to a SQL Server, then allows the user to select a DB, then run some DB functions..... but anyway.... the key pieces are here.
Good luck!

I'm running your code and creating the controls as necessary; however, I'll need your user type SQLDMO definition, used in your Login method.

Set oSQLServer = New SQLDMO.SQLServer

Thank you,
Jim

[techgnome]

It's not a User Type...... Add a reference to SQLDMO Object Library. In fact, I think that's what it's called.

[JimMuglia]

Hmm, OK you're right on the SQLDMO reference; now I'm thinking that when the form loads, a list of servers should be displayed, so that one can be selected for connection, sot ht we can then view its databases?

Where, or how, are the servers loaded into the combo box?
Jim

[JimMuglia]

Hey techgnome, are you out there? I posted aquestion for you in the above post last night.

[techgnome]

Sorry, didn't see it until this AM......
This snippet is from another internal app we use. it uses the same SQLDMO to get the SQL Servers on the form load.

Private Sub Form_Load()
    Dim dmoApp As SQLDMO.Application
    Dim dmoServerGroup As ServerGroup
    Dim dmoServer As RegisteredServer
    
    Set dmoApp = New SQLDMO.Application
    lstServers.AddItem "(Local)"
    For Each dmoServerGroup In dmoApp.ServerGroups
        For Each dmoServer In dmoServerGroup.RegisteredServers
            lstServers.AddItem dmoServer.Name
        Next
    Next
    lstServers.Text = "(Local)"
    
    Set dmoApp = Nothing
    Set dmoServerGroup = Nothing
    Set dmoServer = Nothing
End Sub

Let me know how this works for ya.

[JimMuglia]

Originally posted by techgnome
Sorry, didn't see it until this AM......
This snippet is from another internal app we use. it uses the same SQLDMO to get the SQL Servers on the form load.

Private Sub Form_Load()
    Dim dmoApp As SQLDMO.Application
    Dim dmoServerGroup As ServerGroup
    Dim dmoServer As RegisteredServer
    
    Set dmoApp = New SQLDMO.Application
    lstServers.AddItem "(Local)"
    For Each dmoServerGroup In dmoApp.ServerGroups
        For Each dmoServer In dmoServerGroup.RegisteredServers
            lstServers.AddItem dmoServer.Name
        Next
    Next
    lstServers.Text = "(Local)"
    
    Set dmoApp = Nothing
    Set dmoServerGroup = Nothing
    Set dmoServer = Nothing
End Sub

Let me know how this works for ya.

Thanks! Let me give it a shot right now, and I'll get back with ya in a few.
Jim

[JimMuglia]

Hey techgnome,

Thanks for tfopr th eFomLoad code, it seemst tpwork. Can you also please send me the frmTableSelector form, which is called in the line below?

FillComboFromCollection oSQLServer.Databases, frmTableSelector.cboDatabases


Thank you,
Jim

[techgnome]

Unfortunatly, we're starting to tread into proprietary code areas. Since it was an internaly developed app, I can't just give it all away, but I'll give you what I can (stupid NDS's). The form you referenced, is basically just the main form, and the call fills the specified combo box with the list of databases.
Once you have the login info, the server name and a database name, what you want to do with it is completely up to you.

[WALDO]

is there a way to do this with ADO? I just want a list of the databases on a server.

[PhilRob56]

I found this.....

Option Explicit
 
Private Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
 
Private Declare Function NetServerEnum Lib "netapi32" ( _
        strServername As Any, _
        ByVal level As Long, _
        bufptr As Long, _
        ByVal prefmaxlen As Long, _
        entriesread As Long, _
        totalentries As Long, _
        ByVal servertype As Long, _
        strDomain As Any, _
        resumehandle As Long) As Long
 
Private Declare Function NetApiBufferFree Lib "Netapi32.dll" (ByVal lpBuffer As Long) As Long
 
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
 
Private Const SV_TYPE_SERVER As Long = &H2
Private Const SV_TYPE_SQLSERVER As Long = &H4
 
Private Type SV_100
    platform As Long
    name As Long
End Type
 
Public Sub GetSQLServers()
    '
    ' You could change this to be a function returning
    ' a list of the SQL servers in a ADOR Recordset or an array etc.
    '
    ' At present, it just does a debug.print of all the
    ' SQL servers on the network.
    '
    '
    Dim l As Long
    Dim entriesread As Long
    Dim totalentries As Long
    Dim hREsume As Long
    Dim bufptr As Long
    Dim level As Long
    Dim prefmaxlen As Long
    Dim lType As Long
    Dim domain() As Byte
    Dim i As Long
    Dim sv100 As SV_100
 
    level = 100
    prefmaxlen = -1
 
    lType = SV_TYPE_SQLSERVER
    domain = "placeYourDomainNameHere" & vbNullChar
    l = NetServerEnum(ByVal 0&, _
            level, _
            bufptr, _
            prefmaxlen, _
            entriesread, _
            totalentries, _
            lType, _
            domain(0), _
            hREsume)
 
    If l = 0 Or l = 234& Then
        For i = 0 To entriesread - 1
            CopyMemory sv100, ByVal bufptr, Len(sv100)
            Debug.Print Pointer2stringw(sv100.name)
            bufptr = bufptr + Len(sv100)
        Next i
    End If
    NetApiBufferFree bufptr
    '
End Sub
 
Private Function Pointer2stringw(ByVal l As Long) As String
    Dim buffer() As Byte
    Dim nLen As Long
    '
    nLen = lstrlenW(l) * 2
    If nLen Then
        ReDim buffer(0 To (nLen - 1)) As Byte
        CopyMemory buffer(0), ByVal l, nLen
        Pointer2stringw = buffer
    End If
End Function

[WALDO]

though I can't remember where, how to do it with ADO. just use a SQL string of:

SELECT RTrim(name) AS Name FROM master.dbo.sysdatabases