Tracing ODBC Connections to an Oracle Database

Various applications can be configured to connect to an Oracle database using an ODBC connection. When there are problems with the connection, it can sometimes be useful to enable ODBC tracing. 

This is a pretty straightforward task and can often highlight useful information to diagnose issues such as incorrect ODBC drivers or driver versions, or attempting to use incorrect database connection information. 

The Scenario
————-
To demonstrate ODBC tracing, we’ll first log into an Oracle 11.2.0.1.0 Enterprise edition database called “ORCL11″ and create an account called “odbc1″:   

create user odbc1 identified by odbc1;

(Note: You may want to make your password more secure than this! Remember also that 11g has case-sensitive passwords by default)

grant create session, create table to odbc1;
alter user odbc1 default tablespace users;
alter user odbc1 quota 10M on users;

Next we’ll connect as our new user and create a test table with a small amount of data.

connect  odbc1/odbc1

create table odbc_test_tab (col1 varchar2(40));
insert into odbc_test_tab values (’TEST’);
insert into odbc_test_tab values (’TEST2′);
commit;


Then we create an ODBC connection to our ORCL11 database.  Create the connection using a System DSN called “EXCEL_TEST11″. Use the Oracle ODBC Driver 11.2.00.01 and the odbc1 database account to connect.

The last step is to create a new Excel 2010 spreadsheet called “odbctesting.xlsx” 

Note: To keep this post brief, I haven’t included full details of the steps to create the ODBC connection, or of setting up the connection in Excel. If anyone wants detailed instructions on how to do this, please let me know.  For the Excel connection the main steps are to go to the Data tab - “From Other Sources” - “From Data Connection Wizard” - “ODBC DSN” - Next and then select the “EXCEL_TEST11″ ODBC connection.  (Even though you’ll see a large listing of database objects, the ODBC1.ODBC_TEST_TAB is in the list - near the end. All the other objects are database views and tables to which PUBLIC - i.e. all users - have been  granted access).   Don’t select the option to save the password to the file.
 

Turning on tracing
——————
Next we’ll turn on ODBC tracing, so that we can see what’s happening when the connection is being made. From a Windows XP client, as you need to do is:

Start - control panel - Administrative Tools - Data Sources (ODBC)   (or you can go  Start - run - odbcad32)

Then: Go to the “Tracing” tab - Click on the “Start Tracing Now” button

Once you’ve clicked on the “Start Tracing Now” button, you’ll notice it will change to be a “Stop Tracing Now” button - Apply - OK.   This closes the ODBC Data Source Administrator.
Notes:
(i) To change the location of the logfile, click on the “Browse” button on the Tracing tab. You can also change the name of the log file. Then click Save.
(ii) Be aware, that this will turn on tracing for ALL ODBC connections running on this client.
(iii) Tracing could have a serious performance impact on your application, so only enable it if neccessary. 
(iv) Microsoft support article ID: 942976 notes that 64-bit versions of windows have two versions of the ODBC Administrator tool:

%systemdrive%\Windows\SysWoW64 folder.   - 32-bit version of Odbcad32.exe
%systemdrive%\windows\System32 folder.   - 64-bit version - also called Odbc32.exe

If running odbcad32 to edit 32-bit DSN’s, then specify the full path to the executable. 32-bit System DSN’s will only appear in the 32-bit version of odbc32.exe and 64-bit System DSN’s will only appear in the 64-bit version  of odbc32.exe.  However, be aware that User DSN’s will appear in both versions. Please refer to the Microsoft support note for more details.  
 

Viewing a successful connection
——————————-
To see a successful connection, we can open our spreadsheet and select the “Refresh All” option on the Data tab.
The ODBC tracing logfile contains a large amount of information. An extract is shown below. (I’ve added comments pre-fixed by “#”, but you obviously won’t see these in the logfile).


e               8fc-dd0 EXIT  SQLAllocConnect  with return code 0 (SQL_SUCCESS)   # successfully connected to the database.

  WCHAR *             0×03B9F460 [      37] “SELECT * FROM “ODBC1″.”ODBC_TEST_TAB”"  # select statement to run against our table.

e               8fc-dd0 EXIT  SQLDescribeColW  with return code 0 (SQL_SUCCESS) # Description of column data within the table follows
  …
  WCHAR *             0×0013FA20 [       4] “COL1″                # Column name

  SQLULEN *           0×024FEA68 (40)                             # Column length      

e               8fc-dd0 EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)  # Successfully executed SQL select statement

e               8fc-dd0 EXIT  SQLFetch  with return code 0 (SQL_SUCCESS) # Successful fetch of data from the first row in our table

e               8fc-dd0 EXIT  SQLFetch  with return code 0 (SQL_SUCCESS) # Successful fetch of data from the second row in our table

e               8fc-dd0 EXIT  SQLFetch  with return code 100 (SQL_NO_DATA_FOUND) # No more data to be fetched from the table - there are only 2 rows.

e               8fc-dd0 EXIT  SQLDisconnect  with return code 0 (SQL_SUCCESS)  #Disconnect from the database
 

Viewing an unsuccessful connection attempt due to an incorrect password
————————————————————————
Here we refresh our spreadsheet again, but deliberately use an incorrect password:

Excel error messages:

[Oracle][ODBC][Ora]ORA-01017:invalid username/password; logon denied
[Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed

ODBC Trace file error messages:

  DIAG [28000] [Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied
 (1017)
  DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed (0)

 

Wrong Database Service name specified
————————————-
We refresh our connection, but put an incorrect entry in the “Service Name” box:

Excel error messages:

[Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout occurred
[Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed

ODBC Trace file error messages:
  DIAG [S1000] [Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout occurred (12170)
  DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed (0)

Listener is down
—————-
We shut down the listener and then refresh the spreadsheet. Excel error messages:

[Oracle][ODBC][Ora]ORA-12541: TNS:no listener

ODBC Trace file error messages:

DIAG [S1000] [Oracle][ODBC][Ora]ORA-12541: TNS:no listener (12541)
DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed (0)

 

 

Database is down
—————–
Startup the listener and shut down the 11g database. Refresh the Excel spreadsheet:
Excel error messages:

[Oracle][ODBC][Ora]ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

ODBC Trace file error messages:

DIAG [S1000] [Oracle][ODBC][Ora]ORA-12514: TNS:listener does not currently know of service requested in connect descriptor (12514)
DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed (0)

Note: You might think, why bother tracing ODBC if you get the error messages in Excel anyway?  That’s fine, but the main purpose of this post is to illustrate that if you are using an application which doesn’t supply detailed messages, you may be able to find out the cause of any issues by turning on ODBC tracing.
 

 

Turning off ODBC Tracing
————————
To stop tracing, you can then just open up the ODBC Data Source Administrator as before - go to the “Tracing” tab and click on the “Stop Tracing Now” button - Apply - OK. 
Note: Don’t leave tracing turned on permanently, otherwise it could fill up the local drive on the client. ODBC tracing can generate a lot of information. Remember to delete or archive old trace files.

Other references:
http://support.microsoft.com/kb/268591/EN-US

One Response to “Tracing ODBC Connections to an Oracle Database”

  1. Tracing ODBC Connections to an Oracle Database | Canning IT LTD Says:

    […] Tracing ODBC Connections to an Oracle Database […]

Leave a Reply

You must be logged in to post a comment.


Buy Xanax Buy Cipro Xanax On Line Buy Oxycontin Buy Adipex Buy Tramadol Xanax Soma Buy Bontril Adderall Buy Adipex Buy Online Xanax Buy Valium Prozac Buy Tramadol Online Bontril Alprazolam Zyprexa Fioricet Buy Line Xanax Tenuate Buy Line Xanax Buy Viagra Buy Ambien Levitra Vicodin Oxycontin Buy Viagra Online Buy Viagra Online Buy Fioricet Buy Ultram Buy Tramadol Online Buy Cheap Phentermine Generic Viagra Buy Diflucan Buy Vicodin Online Buy Flexeril Buy Butalbital Buy Lortab Valium Zyban Ambien Buy Seroquel Buy Tramadol Nexium Buy Zithromax Buy Ativan Buy Zovirax Buy Lipitor Buy Paxil Buy Nexium Zyban Buy Diazepam Lipitor Buy Prozac Buy Ativan Paxil Lexapro Buy Tramadol Buy Valium Xanax Online Buy Seroquel Generic Viagra Butalbital Buy Diazepam Darvocet Didrex Buy Tenuate Nexium Zithromax Ultracet Buy Zyrtec Soma Percocet Buy Diflucan Soma Buy Norco Buy Prozac Diazepam Didrex Buy Propecia