Testing a SQL Server Connection on a Remote Server

If you are establishing a web server or an application server and need to test the database connectivity from the new server back to the database server, writing a quick visual basic script that produces the feedback needed is an easy solution. Below is the code from a vbs file with the connection information left out. Just complete the connection information and run the script from the new remote server.

dim local_connection
msgbox (db_connect(local_connection, "Provider=sqloledb;" & _ 
    "Data Source=myServerName;" & _ 
    "Initial Catalog=myDatabaseName;" & _ 
    "User Id=myUsername;" & _ 
    "Password=myPassword"))

Function db_connect( byRef curSession, connection_string)

    dim connection

    on error resume next
    ' Opening connection
    set connection = CreateObject("ADODB.Connection")

    If Err.Number <> 0 then
       db_connect= "Error # " & CStr(Err.Number) & " " & Err.Description
        err.clear
        Exit Function
    End If
  
    connection.Open connection_string
    If Err.Number <> 0 then
        db_connect= "Error # " & CStr(Err.Number) & " " & Err.Description
        err.clear
        Exit Function
    End If

    set curSession=connection
    db_connect=0

End Function