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!
4
-
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.– Craig W.2015-05-20 16:25:38 +00:00Commented May 20, 2015 at 16:25
-
There's an entiry BOL section on Oracle-SQL Server migration: msdn.microsoft.com/en-us/library/hh313202%28v=sql.110%29.aspx. Did you not consider searching Google first?John Bell– John Bell2015-05-20 16:29:19 +00:00Commented May 20, 2015 at 16:29
-
1possible duplicate of Connecting to Oracle Database using Sql Server Integration ServicesBob Jarvis - Слава Україні– Bob Jarvis - Слава Україні2015-05-20 16:32:32 +00:00Commented May 20, 2015 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– Steve Kopa2015-05-21 18:05:30 +00:00Commented May 21, 2015 at 18:05
Add a comment
|
1 Answer
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#.
2 Comments
Steve Kopa
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.
Zohar Peled
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...