Oracle ODBC in VBScript

At first, I was using this connection string, so that I wouldn’t have to go through TNS because we might probably not have it. The machine I was running this on, didn’t have the oracle client installed.

strConnect = "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)(HOST=mysrv)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=MYDB))); " & _ "uid=user;pwd=pass;"

Dim oConnect: Set oConnect = WScript.CreateObject("ADODB.Connection")
oConnect.Open strConnect

This failed with the errors:

Microsoft OLE DB Provider for Oracle (0x80004005)
Oracle client and networking components were not found. These
components are supplied by Oracle Corporation and are part of the
Oracle Version 7.3.3 or later client software installation. Provider
is unable to function until these components are installed.
Error Description: [Microsoft][ODBC Driver Manager] Driver's
SQLAllocHandle on SQL_HANDLE_ENV failed

One solution for this is to only run this on machines with the Oracle Client installed.  The client will install whatever dlls needed and then it’ll work.  Doing so, and then running the script again gave me this error.

ORA-12154: TNS:could not resolve service name

The most common problem associated with this error code is that the connect string is incorrect. So, I tried to do a

tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=MYDB)))

I ended up with another error.

TNS-12533: TNS: invalid ADDRESS-Parameter

Clearly, this means my connection string is wrong. I managed to get a copy of a working database connection, using oracle developer studio, and looked at what it was doing to connect correctly. I looked at the connection string and tried to do a tnsping on that connection string.

tnsping hostname:port/service_or_db_name

That responded with the whole connect string I needed with an OK(80 msec) response. So I was able to update my connect string in the vbscript now and it works.

Identifying wscript.exe

The problem with running vbscripts is that they will all be identified as wscript.exe in the task manager. This makes it extremely hard to figure out which processes to kill when things go wrong. There doesn’t seem to be any easy way to identify the process to kill in task manager. One way to actually allow us to have different process names other than wscript.exe is to actually make a copy of wscript.exe from windows\system32 folder and rename them appropriately. Then call your vbscript with the new executable.

I found this functionality while looking at wbem objects. This vbscript will identify the actual command line arguments associated with each wscript telling you what the actual vbscripts are associated with each process.

strComputer = "."

Set wbem = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set wbemObjects = wbem.ExecQuery("Select * from Win32_Process where Name='wscript.exe'")

For Each wbemObject in wbemObjects
strLine = strLine & Right(" " & wbemObject.processid, 6) & " " & wbemObject.CommandLine & vbNewLine
Next

msgbox strLine