0

So I have two systems I often have to join together to display certain reports. I've got one system that stores metadata on documents that is stored in SQL Server, usually by part number. The list of part numbers I would want to get documents for come from an Oracle table in our ERP system. My current method goes like this:

  1. Get data from ERP (Oracle) system into a DataTable.
  2. Compile string[] of part numbers from a column.
  3. Use an IN() statement to get all document information from docs (MSSQLSVR) system into another DataTable.
  4. Add columns to ERP DataTable, loop through rows.
  5. Fill out document info from docs DataTable, if(erpRow["ITEMNO"] == docRow["ITEMNO"])

This, to me feels really inefficient. Now obviously I can't use one connection string to JOIN the two tables, or use a database link, so I assume there will have to be two calls, one to each database. Is there another way to join these two sets together?

Improve this question
2
  • Load the ERP data into a dictionary - keyed on the "Part Number" with each row as the value. Then query the Docs information into a DataReader, and use linq to loop through the datareader, connecting up the associated ERP data from the dictionary ? Commented Jun 24, 2013 at 21:13
  • So you can't use a "database link"? Does this mean you can't use Linked Servers ( msdn.microsoft.com/en-us/library/ms188279.aspx )? Why not? It's definitely the best solution. Commented Jun 24, 2013 at 21:35

2 Answers 2

Reset to default
3

I would suggest a LikedServer approach (http://msdn.microsoft.com/en-us/library/ms188279.aspx). Write a Stored Procedure on the SQL Server side that pulls the data over from an Oracle Linked Server, does the JOIN locally and returns the combined data.

SQL Server has been designed to execute JOINs efficiently. No need to try to recreate that functionality in the app layer.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I've done this with great success to a postgresql db. As long as you have the right OLEDB driver for Oracle, this shouldn't be too hard. oracle.com/technetwork/developer-tools/visual-studio/downloads/… (32bit) or oracle.com/technetwork/database/windows/downloads/… (x64)
2

Since you've ruled out a database link I would do the following

  1. Get data from ERP (Oracle) system into a DataTable.
  2. Pass DataTable as a Parameter to SQL Server via a Table-Valued Parameter
  3. Return your data (no loops updating an older set)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.