connecting to linked servers…..urgent help neede | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

connecting to linked servers…..urgent help neede

hi i created a linked server(oracle) with the sql server 2000.
I used
EXEC sp_addlinkedserver
@server = ‘tprod123’,
@srvproduct = ‘Oracle’,
@provider = ‘MSDAORA’,
@datasrc = ‘tprod123’
Here the name of the database is ‘tprod123’ and i used the same name as the linked server name.
then
i used
EXEC sp_addlinkedsrvlogin ‘tprod123’, ‘false’, ‘s’, ‘u’, ‘p’
s is my sqlserverlogin
uis the username and p is the password when i connect through this way i can see the tables and vies in the enterprise manager Now i was using
select * from tprod123..mary.t1 mary-the owner of the table t1.
t1-table in oracle db when i run this select statement it is giving an error message of
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider ‘tprod123’ does not contain table ‘"mary"."t1"’. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=’tprod123′, TableName=’"mary"."t1"’]. and when i use the openquery it is working fine
so can anyone tell me how to use the four letter naming convention for connecting to linked server. please help me out regarding this..
Any help will be thankful

Take help of KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;280106 about issues of Linked server with Oracle.
so can anyone tell me how to use the four letter naming convention for connecting to linked server.
Please refer to the SQL Server books online that is installed along with SQL tools about the syntax and code examples. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
quote:Now i was using
select * from tprod123..mary.t1 mary-the owner of the table t1.
t1-table in oracle db when i run this select statement it is giving an error message of
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider ‘tprod123’ does not contain table ‘"mary"."t1"’. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=’tprod123′, TableName=’"mary"."t1"’].

if Mary is a table owner then is your syntax of query correct ? select * from [tprod123].[tprod123].[mary].t1 , isn’t it ? Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

thank u satya and ghemant
i checkout the link and found the owner or the schema should follow the case convention when using four naming convention.
thank you very much.
]]>