Hello,
Small world. Thats actually my code or a derivative of code I wrote. I recognize the naming conventions. Anyway...
It could be a few things.
-Do you have oracle client installed on the machine?
-Can you connect to the schema WPSQA manually using Toad or SQL plus?
-If both of the above or true, then open the tnsnames.ora file in your oracle installation. Search for WPSQA in that file. Make sure the service name, host, ports, user name, and password all match what is in the tnsnames file. You dont need the tnsnames for this code to work, but the connection info does need to be correct.
[quote]
thisUser = "loginid"
thisPW = "pwd"
Environment("CURRENT_SERVICE_NAME") = "WPSQA"
sql = "select * from tablename;"
ConStr = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=db-qa)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME = " & Environment("CURRENT_SERVICE_NAME") & "))); uid=" & thisUser & ";pwd=" & thisPW & ";"
Set conn1 = CreateObject("ADODB.Connection")
Set thisObjRS = CreateObject("ADODB.recordset")
conn1.ConnectionString = ConStr
conn1.Open
Set thisObjRS = conn1.Execute(sql,,adExecuteNoRecords)
varArray = thisObjRS.GetRows
But I get an error on the conn1.Open line. See error message below. Any ideas?
[Microsoft][ODBC driver for Oracle][Oracle]ORA-12154: TNS:could not resolve service name
Line (35): "conn1.Open".
Thanks & Regards,
Anita
Small world. Thats actually my code or a derivative of code I wrote. I recognize the naming conventions. Anyway...
It could be a few things.
-Do you have oracle client installed on the machine?
-Can you connect to the schema WPSQA manually using Toad or SQL plus?
-If both of the above or true, then open the tnsnames.ora file in your oracle installation. Search for WPSQA in that file. Make sure the service name, host, ports, user name, and password all match what is in the tnsnames file. You dont need the tnsnames for this code to work, but the connection info does need to be correct.
[quote]
thisUser = "loginid"
thisPW = "pwd"
Environment("CURRENT_SERVICE_NAME") = "WPSQA"
sql = "select * from tablename;"
ConStr = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=db-qa)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME = " & Environment("CURRENT_SERVICE_NAME") & "))); uid=" & thisUser & ";pwd=" & thisPW & ";"
Set conn1 = CreateObject("ADODB.Connection")
Set thisObjRS = CreateObject("ADODB.recordset")
conn1.ConnectionString = ConStr
conn1.Open
Set thisObjRS = conn1.Execute(sql,,adExecuteNoRecords)
varArray = thisObjRS.GetRows
But I get an error on the conn1.Open line. See error message below. Any ideas?
[Microsoft][ODBC driver for Oracle][Oracle]ORA-12154: TNS:could not resolve service name
Line (35): "conn1.Open".
Thanks & Regards,
Anita
