February 4, 2010 at 10:36 am
Try changing “HOST = xxxxxxxxx” to ”HOST = ip address”.
Oracle might not be resolving the host address correctly.
It that does not work you might try
1.renaming the tnsnames file,
2.make sure that tnsping does not work,
3.bring up Oracle Net manager and recreate the tnsnames file
4.try tnsping again
5.try sqlplus again
6. If sqlplus works try the linked server again
Sometimes Oracle is very sensitive to a space or tab in the wrong place in the tnsnames file.
Also there might be some control character in the tnsnames file that you do not see.
David Weil
February 4, 2010 at 12:11 pm
dweil (2/4/2010)
Try changing “HOST = MyServer” to ”HOST = ip address”.Oracle might not be resolving the host address correctly.
It that does not work you might try
1.renaming the tnsnames file,
2.make sure that tnsping does not work,
3.bring up Oracle Net manager and recreate the tnsnames file
4.try tnsping again
5.try sqlplus again
6. If sqlplus works try the linked server again
Sometimes Oracle is very sensitive to a space or tab in the wrong place in the tnsnames file.
Also there might be some control character in the tnsnames file that you do not see.
David Weil
I changed the Server to the IP Address and it worked fine as below
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\lislj>cd
C:\>E:
E:\>cd E:\Oracle\product\10.2.0\client_1etwork\admin
E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>tnsping MyDB
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 04-FEB-2
010 18:11:51
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = IPAddress)(PORT = 1535))) (CONNECT_DATA = (SERVICE_NAME = MyDB)))
OK (30 msec)
E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>sqlplus usnme/pwd@MyDB
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 4 18:12:36 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
I tried it on the LinkedServer in Management Studio and it brought this error
OLE DB provider "OraOLEDB.Oracle" for linked server "MyLinkServer" returned message "ORA-12541: TNS:no listener".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "MyLinkServer".
I rebooted the Server before trying the LinkServer again.
How can it connect through sqlplus but not through Management Studio?
February 4, 2010 at 12:49 pm
I think you are getting closer now that you can connect via sqlplus.
Search the entire computer to see if there is more then one copy of tnsnames on it.
In SMSS, right click on the linked server
Click script linked server as
Click create to
Click new query editor window
You might get a hint from the sql to create the linked server
You might also want to post the sql without any confidential information hidden
February 4, 2010 at 1:13 pm
dweil (2/4/2010)
I think you are getting closer now that you can connect via sqlplus.Search the entire computer to see if there is more then one copy of tnsnames on it.
In SMSS, right click on the linked server
Click script linked server as
Click create to
Click new query editor window
You might get a hint from the sql to create the linked server
You might also want to post the sql without any confidential information hidden
Cool, Ill have a go at that now 😀
I forgot to change it, can you edit the HOST and put MyServer on your previous post please, it wont let me edit it 🙂
Jez
February 4, 2010 at 3:33 pm
jez.lisle (2/4/2010)
dweil (2/4/2010)
I think you are getting closer now that you can connect via sqlplus.Search the entire computer to see if there is more then one copy of tnsnames on it.
In SMSS, right click on the linked server
Click script linked server as
Click create to
Click new query editor window
You might get a hint from the sql to create the linked server
You might also want to post the sql without any confidential information hidden
Cool, Ill have a go at that now 😀
I forgot to change it, can you edit the HOST and put MyServer on your previous post please, it wont let me edit it 🙂
Jez
Ok, I did a search for all the tnsnames and found them in the folders below
E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN
E:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN
Should I delete or rename the one in db_1?
I also follow your instructions to create a LinkServer through SMSS I ended up with the same error,
OLE DB provider "OraOLEDB.Oracle" for linked server "SINGLEPLATFORM" returned message "ORA-12541: TNS:no listener".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SINGLEPLATFORM".
so I added the port onto the IP Address and tried again. I still got an error
OLE DB provider "OraOLEDB.Oracle" for linked server "HSP" returned message "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "HSP".
When I create my LinkServer name in SMSS can I use any name? and using OraOLEDB.Oracle is this correct?
dweil - can you edit the post you last wrote and over type the Server name. Thanks
February 4, 2010 at 4:05 pm
Actually you can replace the tnsnames in \db_1etwork\admin with the tnsnames in client_1etwork\admin.
The linked server name does not mater but the data source has to be correct, might even be case sensitive.
In the security tab are you using "Be made using this security context"?
Still try scripting the linked server and see if you find anything unusual and if you post the script, I’ll take a look.
February 5, 2010 at 2:29 am
dweil (2/4/2010)
Actually you can replace the tnsnames in \db_1etwork\admin with the tnsnames in client_1etwork\admin.The linked server name does not mater but the data source has to be correct, might even be case sensitive.
In the security tab are you using "Be made using this security context"?
Still try scripting the linked server and see if you find anything unusual and if you post the script, I’ll take a look.
This is how I set my tnsnames.ora out,
MyDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IPAddress)(PORT = 1535))
)
(CONNECT_DATA =
(SERVICE_NAME = MyDB)
)
)
In SMSS I have done this
LinkedServer name - MyLinkedServerName
Provider - Oracle Provider for OLE DB
Product Name - Oracle
Data Source - IPAddress (I have changed that to the Server name too, I also tried with IPAddrees & Port)
Under Security I have used
Be made using this security context - used the Username & Password I have used in sqlplus
After trying again this is the Create To
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'IPAddress'
GO
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'use remote collation', @optvalue=N'true'
February 5, 2010 at 7:19 am
For the linked server in data source use “TMyDB”
Change any tnsnames files to
TMyDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IPAddress)(PORT = 1535))
)
(CONNECT_DATA =
(SERVICE_NAME = MyDB)
)
)
I put the “T” for testing to make sure you are pointing at the right place.
First test tnsping TMyDB and sqlplus user/password@TMyDB
David Weil
February 5, 2010 at 9:28 am
Do you mean this below in SMSS?
For the linked server in data source use “TMyDB”
February 5, 2010 at 9:33 am
Do you mean this below in SMSS?
For the linked server in data source use “TMyDB”
Yes but don't change the linked server name.
February 5, 2010 at 9:41 am
I tried the tnsping again and these are the results.
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\lislj>cd
C:\>E:
E:\>cd E:\Oracle\product\10.2.0\client_1\NETWORK\admin
E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>tnsping @TMyDB
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 05-FEB-2
010 16:33:14
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
TNS-03505: Failed to resolve name
E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>sqlplus usnme/pwd@TMyDB
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 5 16:33:51 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
February 5, 2010 at 9:45 am
Leave out the @ in the tnsping
E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>tnsping TMyDB
not
E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>tnsping @TMyDB
February 5, 2010 at 10:16 am
dweil (2/5/2010)
Leave out the @ in the tnspingE:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>tnsping TMyDB
not
E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>tnsping @TMyDB
Apologies, I realised that afterwards.
I tried the TMyDB in SMSS and BINGO it works 😀
Brilliant, I am chuffed to bits, I thought I was going to have to create the tables manually 😀
Thank you so much for the help. Sorry I been a pest 😀
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply