Home » RDBMS Server » Networking and Gateways » connection to sqlserver using odbc (oracle 11g R2 Windows 7 profissional, )
connection to sqlserver using odbc [message #649492] Tue, 29 March 2016 01:13 Go to next message
hisham99
Messages: 98
Registered: October 2008
Location: united arab emirates
Member
im trying to connect the oracle 11g release 2 to sqlserver
-i already installed oracle 11g release 2 database and form and report (C:\app\Administrator\product\11.2.0\dbhome_1)
-i make odbc connection in my computer
-i install the oracle database gateway for microsoft sql server on my computer (C:\product\11.2.0\tg_1)
i foloow this step
1) i create initatten.ora in oracle home C:\app\Administrator\product\11.2.0\dbhome_1\hs\admin
HS_FDS_CONNECT_INFO = atten
HS_FDS_TRACE_LEVEL = off

2)i add on oracle tnsname.ora in oracle home (C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN)
atten =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SID_NAME = atten)
(HS=OK)
)
)
3) i add on oracle listener.ora in oracle home (C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME = atten)
(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM =dg4odbc)
)
)
4) in oracle database gateway for microsoft sql server on my computer (C:\product\11.2.0\tg_1\hs\admin)
i create the same file initatten.ora
HS_FDS_CONNECT_INFO = atten
HS_FDS_TRACE_LEVEL = off

5) from the controlpanel>>services i restart the oracleoradb11g_home1TNSListener,and oracleorGtw11g_home1TNSListener
6) from sqldeveloper i login as system\manager and i create the following
create user hisham identified by hisham123
grant connect, resource to hisham
CREATE PUBLIC DATABASE LINK multiMAXTxn CONNECT TO
hisham IDENTIFIED BY hisham123 USING 'atten';
7)when i loging AS (HISHAM\HISHAM123@ORCL) i cant see the table on sql server

can any one tell me what is the problem , i attach 2 picture for my odcb connection and database gateway can you please check if i configure the database gateway in a correct way.
thanks

  • Attachment: info.jpg
    (Size: 128.97KB, Downloaded 78 times)
Re: connection to sqlserver using odbc [message #649496 is a reply to message #649492] Tue, 29 March 2016 03:06 Go to previous messageGo to next message
Littlefoot
Messages: 21225
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Maarten described how he did that (using MS Access, though, but it should work with SQL Server too). Have a look at his walkthrough.
Re: connection to sqlserver using odbc [message #649497 is a reply to message #649492] Tue, 29 March 2016 03:15 Go to previous messageGo to next message
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
Member

Which table? I only see you creating the hisham schema.
Re: connection to sqlserver using odbc [message #649500 is a reply to message #649492] Tue, 29 March 2016 04:08 Go to previous messageGo to next message
hisham99
Messages: 98
Registered: October 2008
Location: united arab emirates
Member
thank you Littlefoot
befor i post my problem i follow the same link and its not work , actually i dont know where is my mistake
mr. JNagtzaam
after i log in with hisham\hisham@orcl i run my query

SELECT * FROM AlarmEventTransactionTable@atten;

the table AlarmEventTransactionTable is reside in sql server
Re: connection to sqlserver using odbc [message #649502 is a reply to message #649500] Tue, 29 March 2016 04:23 Go to previous messageGo to next message
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
Member

You are not referencing the db link correctly. I think it should be:

SELECT * FROM AlarmEventTransactionTable@multiMAXTxn
Re: connection to sqlserver using odbc [message #649503 is a reply to message #649502] Tue, 29 March 2016 05:12 Go to previous messageGo to next message
hisham99
Messages: 98
Registered: October 2008
Location: united arab emirates
Member

Error report -
SQL Error: ORA-28500:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified {IM002}
ORA-02063: 2 lines من MULTIMAXTXN
28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
*Cause: The cause is explained in the forwarded message.
*Action: See the non-Oracle system's documentation of the forwarded
message.
this the normal message which i get ,for this reason im asking if my steps are correct
Re: connection to sqlserver using odbc [message #649504 is a reply to message #649503] Tue, 29 March 2016 05:24 Go to previous messageGo to next message
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
Member

It seems to be more a Microsoft then a Oracle problem now.
One last try: did you create a system data source, or a user data source. It should be the first.
Re: connection to sqlserver using odbc [message #649506 is a reply to message #649504] Tue, 29 March 2016 05:32 Go to previous message
hisham99
Messages: 98
Registered: October 2008
Location: united arab emirates
Member
yes check the picture in my first post attachment ,i attach the odcb connection and test successfully,
and the i install oracle gateway
Previous Topic: TNS-12541: Tns no listener
Next Topic: Monitoring Network speed between Target and Catalog Servers.
Goto Forum:
  


Current Time: Sat Nov 18 04:16:21 CST 2017

Total time taken to generate the page: 0.04602 seconds