Linked Oracle Server with Public Synonym

[Moebius01]

Ok, this is a bit difficult to describe, but here goes:

In SQL 2k, I have linked servers pointing to the oracle databases on our Aspect ACDs (phone switch). There is a table named calldetail that is not owned by the accessing user account, but is available through a public synonym. If I try to access this table from SQL 2k via the linked server, I get:

Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'acdlink' does not contain table '"dta"."calldetail"'. 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='acdlink', TableName='"dta"."calldetail"'].

If I link to the server from MS Access, or the like, I cannot see the table, but I'm able to query from it. I'm assuming this has something to do with the way SQL Server uses the linked servers, but I'm at a loss as to any way around this.

Has anyone seen a similar instance, or know of a workaround to access a public synonym?

 

[john76]

I'm not sure if this is correct or not, but something to try...

If Oracle's synonyms are akin to a named instance in SQL Server, then perhaps you could try and set up a new linked server to the synonym db?

Might be worth a shot anyway.

John

 

[Moebius01]

Actually a synonym would be closer to adding a user access to a table in SQL.

In effect, the table is owned by a specifc user, but a Public Synonym is created to other users access to the table. I think the problem comes that Aspect did not create the synonym properly (the table type shows as unknown).

The strange thing is that even MS Access can run pass thru queries against the table, but nothing I've tried on SQL server yet has allowed me to hit it.