Synonym to External Databases (Oracle)  
Author Message
CP06





PostPosted: Mon Jul 31 18:40:54 CDT 2006 Top

SQL Server >> Synonym to External Databases (Oracle)

Is it possible to create Synonym of Oracle Procedures & Objects in SQL
Server 2005 and if so will they work through a DBLink.

Regards,

Trevor Benedict R

SQL Server306  
 
 
David





PostPosted: Mon Jul 31 18:40:54 CDT 2006 Top

SQL Server >> Synonym to External Databases (Oracle)


> Is it possible to create Synonym of Oracle Procedures & Objects in SQL
> Server 2005 and if so will they work through a DBLink.
>

Yes, although the equivilent of a DBLink in SQL Server is a "linked server"


EXEC sp_addlinkedserver Server_Remote;
GO
USE tempdb;
GO
CREATE SYNONYM MyEmployee FOR
Server_Remote.AdventureWorks.HumanResources.Employee;
GO

From
CREATE SYNONYM
http://msdn2.microsoft.com/en-us/library/ms177544.aspx

David


 
 
David





PostPosted: Mon Jul 31 18:44:40 CDT 2006 Top

SQL Server >> Synonym to External Databases (Oracle)


> Is it possible to create Synonym of Oracle Procedures & Objects in SQL
> Server 2005 and if so will they work through a DBLink.


Oh, for procedures you can't create synonyms, but you can call the
procedures with the EXEC ... AT syntax

And you can write a wrapper stored procedure in SQL Server that executes the
remote procedure. This would work basically like a synonym. The Oracle
sored procedure will need to use SQL scalar inputs and outputs, however. No
PL/SQL types, object types, ref cursors, etc.

David