Linked Server Oracle 10g problem

I have followed the examples from MS. to create the link between my MSSQL200
5
and Oracle 10g
I have all of the Oracle client installed on the server.
I have a net service defined.
Here is what I ran to create link:
EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD89'
EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr07
'
Here is the query:
select count(*) from OraLinkServer2..sysadm.SUN_PUBT
Here is the error:
OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for
linked server "OraLinkServer2".
What am I doing wrong all the oracle tools connect just fine. I need SQL2005
to connect?
Thanks
GregHob,
I've had the same problem. I used the openquery syntax to work around
the problem. This also has the advantage of pushing the work to the
Oracle Server.
So.. try:
select * from
openquery(OraLinkServer2,
' select count(*) from SUN_PUBT
'
)
No -- you don't need the formating and returns -- I just use them to
make it easy for me to read the Oracle Commands separate from the
MS-SQL commands.
If you find a better solution, please post it.
You also might want to read:
http://www.sqlservercentral.com/col...br />
art3.asp
It has the best set of instructions for getting the Instant Client to
work.
Good luck,
Peter Ryan
Hob_Naggers wrote:
> I have followed the examples from MS. to create the link between my MSSQL2
005
> and Oracle 10g
> I have all of the Oracle client installed on the server.
> I have a net service defined.
> Here is what I ran to create link:
> EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD8
9'
> EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr
07'
>
> Here is the query:
> select count(*) from OraLinkServer2..sysadm.SUN_PUBT
> Here is the error:
> OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
> message "ORA-12154: TNS:could not resolve the connect identifier specified
> ".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "OraLinkServer2".
> What am I doing wrong all the oracle tools connect just fine. I need SQL20
05
> to connect?
> Thanks
> Greg|||Hob,
I've had the same problem. I used the openquery syntax to work around
the problem. This also has the advantage of pushing the work to the
Oracle Server.
So.. try:
select * from
openquery(OraLinkServer2,
' select count(*) from SUN_PUBT
'
)
No -- you don't need the formating and returns -- I just use them to
make it easy for me to read the Oracle Commands separate from the
MS-SQL commands.
If you find a better solution, please post it.
You also might want to read:
http://www.sqlservercentral.com/col...br />
art3.asp
It has the best set of instructions for getting the Instant Client to
work.
Good luck,
Peter Ryan
Hob_Naggers wrote:
> I have followed the examples from MS. to create the link between my MSSQL2
005
> and Oracle 10g
> I have all of the Oracle client installed on the server.
> I have a net service defined.
> Here is what I ran to create link:
> EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD8
9'
> EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr
07'
>
> Here is the query:
> select count(*) from OraLinkServer2..sysadm.SUN_PUBT
> Here is the error:
> OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
> message "ORA-12154: TNS:could not resolve the connect identifier specified
> ".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "OraLinkServer2".
> What am I doing wrong all the oracle tools connect just fine. I need SQL20
05
> to connect?
> Thanks
> Greg|||Hi Peter,
Thanks for the response, but I finally found the problem yesterday. The real
problem was not with SQL server, but rather it was a problem with the Oracle
config.
Though i had the TNSNames.ora file configured correctly the SQLNET.ora file
was where the problem lay. It was configured for ezconnect instead of
tnsnames. After this was ironed out the linking of the oracle db into sql wa
s
easy. One weird not though I found after getting the config right. When
querying the linked db. It is case sensitive with regards to column and tabl
e
names, even though on the Oracle side it is not case sensitive. I'm guessing
it has something to do with the translation being provided by the providers.
Thanks
Greg
"pryan64" wrote:

> Hob,
> I've had the same problem. I used the openquery syntax to work around
> the problem. This also has the advantage of pushing the work to the
> Oracle Server.
> So.. try:
> select * from
> openquery(OraLinkServer2,
> ' select count(*) from SUN_PUBT
> '
> )
> No -- you don't need the formating and returns -- I just use them to
> make it easy for me to read the Oracle Commands separate from the
> MS-SQL commands.
> If you find a better solution, please post it.
> You also might want to read:
> http://www.sqlservercentral.com/col... />
ypart3.asp
> It has the best set of instructions for getting the Instant Client to
> work.
> Good luck,
> Peter Ryan
> Hob_Naggers wrote:
>|||Hi Peter,
Thanks for the response, but I finally found the problem yesterday. The real
problem was not with SQL server, but rather it was a problem with the Oracle
config.
Though i had the TNSNames.ora file configured correctly the SQLNET.ora file
was where the problem lay. It was configured for ezconnect instead of
tnsnames. After this was ironed out the linking of the oracle db into sql wa
s
easy. One weird not though I found after getting the config right. When
querying the linked db. It is case sensitive with regards to column and tabl
e
names, even though on the Oracle side it is not case sensitive. I'm guessing
it has something to do with the translation being provided by the providers.
Thanks
Greg
"pryan64" wrote:

> Hob,
> I've had the same problem. I used the openquery syntax to work around
> the problem. This also has the advantage of pushing the work to the
> Oracle Server.
> So.. try:
> select * from
> openquery(OraLinkServer2,
> ' select count(*) from SUN_PUBT
> '
> )
> No -- you don't need the formating and returns -- I just use them to
> make it easy for me to read the Oracle Commands separate from the
> MS-SQL commands.
> If you find a better solution, please post it.
> You also might want to read:
> http://www.sqlservercentral.com/col... />
ypart3.asp
> It has the best set of instructions for getting the Instant Client to
> work.
> Good luck,
> Peter Ryan
> Hob_Naggers wrote:
>