Tag Archives: odbc

How to check version of odbc

12 Mar

To check for correct version of odbc, below is how to access odbc on Windows 64 bits:

  • odbc 32 bit
    • c:\windows\SysWOW64\odbcad32.exe
  • odbc 64 bit
    • c:\windows\System32\odbcad32.exe

 

 

You can also check what verson of odbc are installed by going to:

  • odbc administrator – drivers tab – check if 32/62 dll are installed for the different sw (e.g Oracle, sql server)

 

Read more:

https://community.oracle.com/message/10839853

http://www.connectionstrings.com/download-oracle-11g-release-2-client-odbc-drivers/

http://www.simba.com/wp-content/uploads/2010/10/HOW-TO-32-bit-vs-64-bit-ODBC-Data-Source-Administrator.pdf

 

 

 

Advertisements

Create connection from MS SQL to Oracle (linked server/db link)

6 May

In order to select from MS SQL to a Oracle database, we had to do the following:

  1. Create linked servers in the database
    • EXEC sp_addlinkedserver 
         @server = N'oracledb', 
         @srvproduct = N'',
         @provider = N'MSDASQL', 
         @datasrc = N'oracledb';
      GO
    • server objects – linked servers – new linked servers
      
      
  2. User openquery like e.g
    • select * from openquery(oracledb,’select sysdate from dual’)
    • go

 

Read more:

http://wiki.servicenow.com/index.php?title=Using_ODBC_Driver_in_SQL_Server_2008_and_2012

http://technet.microsoft.com/en-us/library/aa276848(v=sql.80).aspx

 

Connect Excel to Oracle through ODBC

16 Aug

Steps to setup:

  1. Install Oracle client http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
  2. Install odbc http://www.oracle.com/technetwork/topics/winsoft-085727.html
  3. Use the odbc administrator in the syswow64 directory to create a DSN pointing to oracle

 

 

4. Configuring Excel to Connect to Oracle

Open a new book or sheet in Excel. Follow the Data>Import External Data>Import Data path to select a data source. Highlight +Connect to New Data Source and click Open (or double-click it) to bring up the Data Connection Wizard.

 

 

 

 

 

 

 

 

 

 

 

 

Useful links:

http://www.databasejournal.com/features/oracle/article.php/10893_3358411_1/Connecting-with-Oracle-Accessing-Oracle-via-Access-and-Excel.htm

https://forums.oracle.com/thread/2558996

https://forums.oracle.com/message/10703232

 

 

Databaselink from Oracle to MS SQL database (heterogeneous database connections)

14 Feb

You have to do the following (Windows2008R2 is the OS on both of the servers: Oracle server (11G) and MS SQL server 2012):

    1. Install Oracle ODBC driver on the Oracle database server where the instance(database) you are using to access the remote MS SQL server database
    2. Use “ODBC Data Source Administrator” to create “System DSN” for the connection:
      • Choose the SQL Server driver
      • Supply the name for the connection (ODBCNAME)
      • Supply the sql server (and or with instance name like sqlserver1\database1
      • Choose either Windows authentication or create a user in that database to with read access in MS SQL (SQL_USER with db_datareader)
      • Choose default database to access
      • Finish and test the connection
    3. Run this command to check for global names paremeter in Oracle is set to false:
      • show parameter global
    4. Create initODBCNAME.ora in $ORACLE_HOME$\dbhome_1\hs\admin
      • HS_FDS_CONNECT_INFO=ODBCNAME
    5. Modify the listener.ora
      • for Oracle 11g: (SID_DESC = (SID_NAME = ODBCNAME) (ORACLE_HOME = E:\Oracle\product\11.2.0\dbhome_1) (PROGRAM = dg4odbc))
      • for Oracle 10g: (SID_DESC = (SID_NAME = ODBCNAME) (ORACLE_HOME = E:\Oracle\product\10.2.0\dbhome_1) (PROGRAM = hsodbc))
    6. Modify the tnsnames.ora
      • ODBCNAME.WORLD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLESERVER)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ODBCNAME) ) (HS=OK) )
    7. Restart the listener.ora (through services or through the command line)
    8. Create database link in Oracle database
      • CREATE PUBLIC DATABASE LINK “ODBCNAME.WORLD” CONNECT TO “SQL_USER” IDENTIFIED BY “XXX” USING ‘ODBCNAME.WORLD’;
    9. Test run a sql query which uses the database linke e.g
      • select sysdate from dual@ODBCNAME.WORLD

Other useful links:

http://dbaforums.org/oracle/lofiversion/index.php?t21694.html

http://www.dba-oracle.com/t_heterogeneous_database_connections_sql_server.htm

http://www.orafaq.com/forum/t/171463/0/

http://docs.oracle.com/cd/B28359_01/gateways.111/b31042/configodbc.htm

http://www.datadirect.com/resources/odbc/oracle-heterogeneous/common-error.html

http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm