OraFAQ Forum: Networking and Gateways » connection to sqlserver using odbc

Home » RDBMS Server » Networking and Gateways » connection to sqlserver using odbc (oracle 11g R2 Windows 7 profissional, )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

connection to sqlserver using odbc [message #649492]

Tue, 29 March 2016 01:13

hisham99
Messages: 106
Registered: October 2008
Location: united arab emirates

Senior 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 1993 times)

Report message to a moderator

Re: connection to sqlserver using odbc [message #649496 is a reply to message #649492]

Tue, 29 March 2016 03:06

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: connection to sqlserver using odbc [message #649497 is a reply to message #649492]

Tue, 29 March 2016 03:15

JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar

Member

Which table? I only see you creating the hisham schema.

Report message to a moderator

Re: connection to sqlserver using odbc [message #649500 is a reply to message #649492]

Tue, 29 March 2016 04:08

hisham99
Messages: 106
Registered: October 2008
Location: united arab emirates

Senior 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

Report message to a moderator

Re: connection to sqlserver using odbc [message #649502 is a reply to message #649500]

Tue, 29 March 2016 04:23

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

Report message to a moderator

Re: connection to sqlserver using odbc [message #649503 is a reply to message #649502]

Tue, 29 March 2016 05:12

hisham99
Messages: 106
Registered: October 2008
Location: united arab emirates

Senior 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

Report message to a moderator

Re: connection to sqlserver using odbc [message #649504 is a reply to message #649503]

Tue, 29 March 2016 05:24

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.

Report message to a moderator

Re: connection to sqlserver using odbc [message #649506 is a reply to message #649504]

Tue, 29 March 2016 05:32

hisham99
Messages: 106
Registered: October 2008
Location: united arab emirates

Senior Member

yes check the picture in my first post attachment ,i attach the odcb connection and test successfully,
and the i install oracle gateway

Report message to a moderator

Previous Topic:	TNS-12541: Tns no listener
Next Topic:	Monitoring Network speed between Target and Catalog Servers.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 11:47:05 CST 2024