How to check version of odbc

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

 

 

 

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

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

 

How to test user login in MS SQL

There are several methods to check if you have the correct username and password to login to a MS SQL database

1: UDL

• Create an empty file e.g TestSQLLogin.udl (extension needs to be udl) – and double click on it. Click the provider tab, and select “Microsoft OLE DB Provider for SQL Server”. Then click the connection tab, populate your connection details, and hit the “Test Connection” button.

2: ODBC

• create a odbc link and test connection

3: Query from command prompt

• osql -U username
• password
• then you get a prompt if succesful like “1>”

4: sqlcmd (remote and local)

• From remote: sqlcmd -U username -P password -S tcp:dbservername\databasename,portnumber
• or
• Local on the dbserver: sqlcmd -U username -P password -d databasename

Read more

http://blogs.msdn.com/b/steverac/archive/2010/12/14/test-remote-sql-connectivity-easily.aspx

http://www.linickx.com/103/testing-my-ms-sql-username-and-password

http://technet.microsoft.com/en-us/library/ms162773.aspx

Connect Excel to Oracle through ODBC

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)

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