Prev: Cannot remove filegroup
Next: www.EvilSQL.com
From: bkgroups on 25 May 2007 07:50
Greetings all,

I'm unsuccessfully trying to create a linked server from SQL Server
2005 to Oracle 9i using OraOLEDB.Oracle. Here's the lovely error
message that I get:


OLE DB provider "OraOLEDB.Oracle" for linked server "test" returned
message "ORA-12504: TNS:listener was not given the SERVICE_NAME in
CONNECT_DATA".


I am able to connect via SQL*PLUS, no problem.


In the Linked Server dialog box I have these entries:


Provider: Oracle Provider for OLE DB
Product Name: Oracle
Data Source: MILAB.WINDSORLOCKS.AHLSTROM.COM
Provider String: " "


Here's the TNSNAMES entry:


MILAB.WINDSORLOCKS.AHLSTROM.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(HOST =
10.1.0.237)(PORT = 1522))
)
(CONNECT_DATA =
(SID = milab)
)
)


Here's the SQLNET entry:


NAMES.DEFAULT_DOMAIN = adagnet.net
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)


HELP is greatly appreciated.


Thanks,


BK

From: Roy.N.Latham on 25 May 2007 10:18
On May 25, 7:50 am, bkgro...(a)yahoo.com wrote:
> Greetings all,
>
> I'm unsuccessfully trying to create a linked server from SQL Server
> 2005 to Oracle 9i using OraOLEDB.Oracle. Here's the lovely error
> message that I get:
>
> OLE DB provider "OraOLEDB.Oracle" for linked server "test" returned
> message "ORA-12504: TNS:listener was not given the SERVICE_NAME in
> CONNECT_DATA".
>
> I am able to connect via SQL*PLUS, no problem.
>
> In the Linked Server dialog box I have these entries:
>
> Provider: Oracle Provider for OLE DB
> Product Name: Oracle
> Data Source: MILAB.WINDSORLOCKS.AHLSTROM.COM
> Provider String: " "
>
> Here's the TNSNAMES entry:
>
> MILAB.WINDSORLOCKS.AHLSTROM.COM =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(HOST =
> 10.1.0.237)(PORT = 1522))
> )
> (CONNECT_DATA =
> (SID = milab)
> )
> )
>
> Here's the SQLNET entry:
>
> NAMES.DEFAULT_DOMAIN = adagnet.net
> SQLNET.AUTHENTICATION_SERVICES= (NTS)
> NAMES.DIRECTORY_PATH= (TNSNAMES)
>
> HELP is greatly appreciated.
>
> Thanks,
>
> BK

I linked MS SQL 2005 to Oracle 10g last night.
My first attempt was to use the Oracle OLE DB driver/provider like
you.
My second attempt was to use the Microsoft for Oracle OLE DB driver.
(I forget the exact wording.)
For me, the drivers were not in alphabetical sequence for SQL Server
2005 but they were for SQL Server 2000, and since the same driver had
just worked for 2000, I looked harder to find the Microsoft Oracle
driver.

Your mileage may vary.

Best of luck

Roy

From: Fari on 30 May 2007 08:54
Hi!
Have you enabled Allow inprocess (Linked Servers, Providers,
OraOleDB.Oracle) ?

/*
Fari
*/

From: bkgroups on 1 Jun 2007 13:21
On May 30, 8:54 am, Fari <F...(a)discussions.microsoft.com> wrote:
> Hi!
> Have you enabled Allow inprocess (Linked Servers, Providers,
> OraOleDB.Oracle) ?
>
> /*
> Fari
> */

I found a solution!

I set the "Data Source" and "Provider String" on the linked server
like this:

"IP Address" + ":" + "Port/" + "alias".

EXAMPLE: 10.1.0.222:1522/alias

Now, it works like a charm. Hopefully, others can benefit from this
posting.

Thanks,

BK




 | 
Pages: 1
Prev: Cannot remove filegroup
Next: www.EvilSQL.com