Search code examples
oraclevbscriptodbctnsnamestns

ODBC Data Source Connection Successful but TNS Unresolved in VBScript

I am trying to make a connection to Oracle DB from VBScript. I've added all the environment variables and the code can locate the file of TNSNAMES.ora but cannot resolve the connection.

When I test the connectivity from the ODBC Data Source Administrator, I get a 'Success' result, but I still cannot access this from the code. 

con.ConnectionString = "DSN=DATA;Uid=wh;Pwd=pwd;"
con.Open    'This is where it fails

I have tried many connection strings but all fail

OracleConnString = "Driver={Oracle in instantclient_18_3};server=server;database=db;trusted_connection=Yes;"

DB_CONN_STRING = "Driver={Oracle in instantclient_18_3}; " & _
    "(DESCRIPTION=" & _
    "(ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = port)" & _
    "(CONNECT_DATA=(SERVICE_NAME=srv_name)))"

CONN_STRING = "CONNECT wh/pwd@//host/db"

How can I connect to the database from the script?

ODBC Success

ODBC Admin

Error

Solution

  • You mix several topics.

    You hide the DSN name and the "Data Source Name", this makes it impossible for use to provide you the correct connection string.

    If you like to use the ODBC DSN then the connection string must be 

    DSN=▇▇▇▇DATA;Uid=myUsername;Pwd=myPassword

    However, you must create a System DSN, not a User DSN (see ODBC DSN). However, typically you don't use the ODBC DSN because you have to create the DSN on the target machine which is additional configuration work.

    Connection string without DSN would be 

    Driver={Oracle in instantclient_18_3};Dbq=?????;Uid=myUsername;Pwd=myPassword;
or
Driver={Oracle in instantclient_18_3};Server=?????;Uid=myUsername;Pwd=myPassword;

    see https://www.connectionstrings.com/oracle-in-oraclient11g_home1/

    If you use full DB name like (DESCRIPTION=... then you don't need any tnsnames.ora file. The purpose of this file is to resolve an alias to this full DB name.

    Ensure that the ODBC driver is the same architecture, i.e. 32-bit or 64-bit as your VBS environment.

    Either use %windir%\system32\odbcad32.exe + %windir%\system32\cscript.exe

    or %windir%\SysWOW64\odbcad32.exe + %windir%\SysWOW64\cscript.exe