Querying multiple SQL servers via VB script

mdo8105

Board Regular
Joined
Nov 13, 2015
Messages
83
I am attempting to run a query against multiple servers, but I keep running into a wall. So far I can successfully query just one server, that is MTDVWPDB. I need to to query also MTDVWQDB,CWDVWPDB, and CWDVWQDB. Below is the script I already have. I would love some insight,modifications, or even resources on how to accomplish my goal. Thank you.

Code:
Option Explicit
Private CN As ADODB.Connection


Function Connect(Server As String, _
                 Database As String) As Boolean
 
    Set CN = New ADODB.Connection
    On Error Resume Next
 
    With CN
        ' [COLOR=#008000]Create connecting string[/COLOR]
        .ConnectionString = "Provider=SQLOLEDB.1;" & _
                            "Persist Security Info=False;" & _
                            "User ID=CorpSvc;" & _
                            "Password=P5fZ2y;" & _
                            "Server=" & Server & ";" & _
                            "Database=" & Database & ";"
                            
       [COLOR=#008000] '.ConnectionString = "Provider=SQLOLEDB.1;" & _
                            "Integrated Security=SSPI;" & _
                            "Server=" & Server & ";" & _
                            "Database=" & Database & ";"[/COLOR]
       [COLOR=#008000] ' Open connection[/COLOR]
        .Open
    End With
  [COLOR=#008000]  ' Check connection state[/COLOR]
    If CN.State = 0 Then
        Connect = False
    Else
        Connect = True
    End If
 
End Function


[COLOR=#008000]'Function Inc(ByRef Variable As Variant, _
'             Optional ByVal Number As Variant = 1) As Variant
'    Variable = Variable + Number
'End Function[/COLOR]


Function Query(SQL As String, ws As Worksheet, ByVal uRow As Long) As Boolean
 
    Dim RS As ADODB.Recordset
    Dim field As ADODB.field
    Dim env As Variant
    Dim i As Integer
    
   [COLOR=#008000] ' Open up a recordset / run query[/COLOR]
    Set RS = New ADODB.Recordset
    RS.Open SQL, CN, adOpenStatic, adLockReadOnly, adCmdText
 
    If RS.State Then
        If RS.RecordCount > 0 Then
            For i = 0 To (RS.fields.Count - 1)
                Select Case RS.fields.Item(i).Name
                    Case "EMP", "SER"
                        If RS.fields.Item(i).Value <> vbNullString Then
                            ws.Cells(uRow, Utils.GetColByRow(ws, "MO_" & RS.fields.Item(i).Name, 2)) = "{{CID}}" & RS.fields.Item(i).Value
                        End If
                    Case "ENV"
                        If RS.fields.Item(i).Value <> vbNullString Then
                            env = Split(RS.fields.Item(i).Value, ":")
                            ws.Cells(uRow, Utils.GetColByRow(ws, "MO_" & RS.fields.Item(i).Name, 2)) = UCase(env(2))
                        End If
                End Select
            Next i
            Set RS = Nothing
            Query = True
        End If
    Else
       Query = False
    End If
End Function


Function Disconnect()
  [COLOR=#008000]  ' Close connection[/COLOR]
    CN.Close
End Function


Public Sub getEpicData()
 
    Dim empquery1 As String
    Dim empquery2 As String
    Dim serquery1 As String
    Dim serquery2 As String
    Dim Connected As Boolean
    Dim Queried As Boolean
    Dim user As Variant
    Dim ws As Worksheet
    
    Set ws = Worksheets("USERS")
    
  [COLOR=#008000]  ' Connect to the database[/COLOR]
    Connected = Connect("MTDVWPDB", "Clarity")
 
    If Connected Then
        [COLOR=#008000]' If connected run query and disconnect[/COLOR]
        For Each user In ws.Range("A3:A" & ws.Range("A" & Rows.Count).End(xlUp).row)
            
            empquery1 = "SELECT [CLARITY_EMP].[USER_ID] as [EMP], [CLARITY_EMP].[PROV_ID] as [SER], [ECI_BASIC].[DEPLYMNT_DESC] as [ENV] " & _
                        "FROM [CLARITY_EMP] " & _
                        "LEFT JOIN [EMP_MAP] " & _
                        "ON [EMP_MAP].[CID] = [CLARITY_EMP].[USER_ID] " & _
                        "LEFT JOIN [ECI_BASIC] " & _
                        "ON [ECI_BASIC].[INSTANCE_ID] = [CLARITY_EMP].[CM_PHY_OWNER_ID] " & _
                        "WHERE [EMP_MAP].[INTERNAL_ID] = '" & user & "'"
            
            empquery2 = "SELECT [CLARITY_EMP].[USER_ID] as [EMP], [CLARITY_EMP].[PROV_ID] as [SER] , [ECI_BASIC].[DEPLYMNT_DESC] as [ENV] " & _
                        "FROM [CLARITY_EMP] " & _
                        "LEFT JOIN [ECI_BASIC] " & _
                        "ON [ECI_BASIC].[INSTANCE_ID] = [CLARITY_EMP].[CM_PHY_OWNER_ID] " & _
                        "WHERE [CLARITY_EMP].[SYSTEM_LOGIN] = '" & user & "'"
            
            serquery1 = "SELECT [CID] as [SER] " & _
                        "FROM [SER_MAP] " & _
                        "WHERE [INTERNAL_ID] = '" & user & "'"
                        
            serquery2 = "SELECT [ID1].[PROV_ID] as [SER] " & _
                        "FROM [Clarity].[dbo].[IDENTITY_SER_ID] as [ID1] " & _
                        "WHERE [ID1].IDENTITY_TYPE_ID = '120189' " & _
                        "AND [ID1].IDENTITY_ID = '" & user & "'"
                  
            
            Queried = Query(empquery1, ws, user.row)
            
            If Not Queried Then
                Queried = Query(empquery2, ws, user.row)
                If Not Queried Then
                    Queried = Query(serquery1, ws, user.row)
                    If Not Queried Then
                        Queried = Query(serquery1, ws, user.row)
                    End If
                End If
            End If
            
        Next user
        
        Call Disconnect
    Else
      [COLOR=#008000]  ' Couldn't connect[/COLOR]
        MsgBox "Could Not Connect!"
    End If
 
End Sub
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Watch MrExcel Video

Forum statistics

Threads
1,127,093
Messages
5,622,666
Members
415,917
Latest member
kungsleden

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top