Re: Invoke Oracle function from SQL SERVER

On Aug 17, 12:19 pm, DA Morgan <damor..._at_psoug.org> wrote:
> ind_123 wrote:
> > How I can call an oracle function from SQL SERVER 2000 stored
> > procedure. Anyone?
>
> I would really hope it is impossible but I know that is likely not
> the case. Look at Oracle's Heterogeneous Services and whatever
> offering Microsoft may sell.
>
> PS: Version information is important.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

It is very easy to query Oracle from SQL Server 2000 and probably it is the same in 2005.

First you need to install the Oracle client and configure tnsnames.ora to contain an entry for the target Oracle. After performing the runtime client install you need to go back and do a custom install selecting the Oracle for Windows (OLE) option.

Then in SQL Server you just create a linked database. You will need a valid Oracle username and password with the necessary privileges for use in the link security tab.

You can create the linked db via the EM or using commands. You will want to look in Books Online at the sample to find the correct entries for the EM or command parameters.

HTH -- Mark D Powell -- Received on Fri Aug 17 2007 - 17:28:48 CDT