-1

I need to use the results from an Oracle query in a SQLServer query within ASP.NET. Can I do this without exporting the Oracle query results to a temp file, e.g. csv, and creating a new table in SQLServer using the csv file? Thanks!

Steve Kopa
  • 1
  • 1
  • What do you mean by "use the results ... in a SQLServer query"? Are you using the results to select certain rows from SQL Server (i.e. a where clause)? Are you trying to join the results together? – Craig W. May 20 '15 at 16:25
  • There's an entiry BOL section on Oracle-SQL Server migration: https://msdn.microsoft.com/en-us/library/hh313202%28v=sql.110%29.aspx. Did you not consider searching Google first? – John Bell May 20 '15 at 16:29
  • 1
    possible duplicate of [Connecting to Oracle Database using Sql Server Integration Services](http://stackoverflow.com/questions/18096409/connecting-to-oracle-database-using-sql-server-integration-services) – Bob Jarvis - Слава Україні May 20 '15 at 16:32
  • I am limited to performing queries on my client's databases. I can't modify connections or create stored procedures. The equivalent of what I'm trying to accomplish would be something like. Select ID from Table1 where ID in (Select ID from Table2 where Client = ClientNum). However, my Table2 exists in Oracle, and my Table1 exists in SQLServer. The Oracle results need to be used in my SQLServer query, – Steve Kopa May 21 '15 at 18:05

1 Answers1

0

There are probably several different ways to do this, including connecting the SQL Server to the Oracle server using Linked servers, But if that's not possible for some reason, or if you need to use the Oracle resultset as well as the SQL Server resultset in your asp.net page, you can fill a DataSet with the Oracle query results, and send a DataTable as a table valued parameter to a stored procedure in the SQL Server.

Look at this page in MSDN, explaining how to use table valued parameters in sql server.

You can also look here for a step by step instruction on how to create and use table valued parameters in c#.

Community
  • 1
  • 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Thanks, Zohar. We may be able to use the dataset methodology even though we can't use a stored proc. Our client has now informed us that they will probably just duplicate the Oracle table (they're running Oracle 8) into the SQLServer database which is newer technology. So this question may be moot. Thanks to all for the constructive feedback. – Steve Kopa May 21 '15 at 18:19
  • Sorry to hear that your client is... well... let's just say holding you back. Using sql server without being able to even write procedures is like riding a ferrary in a school zone... – Zohar Peled May 21 '15 at 20:07