Access remote Oracle from SQL Server in query only? (Crystal Reports command)

We have a peculiar challenge with overly-strict use restrictions, and I'm trying to find a way to accomplish it.

We have data in two locations, on different platforms. We are extracting data from application tables, and we aren't allowed to create our own views/procs/etc.

Is there a way to run a query into a remote Oracle DB from within an SQL Server query?

To further complicate issues, we have to make it run through a Crystal Reports database command.

We have ODBC connections defined at the BOXI platform (using Oracle ODBC for the Oracle connection).

I am hoping to use the SQL WITH clause to build in-memory views (for lack of a better term) to:

• Initially extract some circuit IDs from the local SQL Server system,
• Extract ticket numbers based on those circuit IDs, from the remote Oracle system,
• Extract the core of our data from the SQL Server system, joined with the ticket data and return that to Crystal as a result da开发者_如何学JAVAtaset.

If we had our own space, this would be trivial.

BOXI doesn't let us do multiple-server universes.

---

You would need to some way to write and store connection strings, but it doesn't sound like you're able to do this.

If you can't make ANY changes to either source system, you might try creating an MS Access DB and using linked tables to bring in all the information you need and have your Crystal Report run from that. You would then only need to make sure that the machine you're running this on has the ODBC drivers to be able to connect, which are simple enough to configure.