Thanks for the response, but I finally found the problem yesterday. The real
config.
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 was
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 table
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.
"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/columnists/hji/oracleandsqlserverinteroperabilitypart3.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 MSSQL2005
> > 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
> > Greg
>
