INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

(OP)
Hi,

My case is : I have an application running on MS SQL 2008 R2 in Server A, which there are a couple of stored procedures in there. I have also a Oracle 10g in Server B. Now that, I created a table in the Oracle 10g DB in Server B and would like to run the stored procedures in Server A, so that the result set of the stored procedures in Server A will be stored in the Server B oracle table.

As I m a newbie in this 2 env, can any one walk me thru how I can do this ?

Many thanks in advance.

RE: Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

Here is a possible solution. You will need some sort of API (like ODBC) for SQL Server to communicate with Oracle. There will also be difficulties in tracking the success of the process (such as network failure).

The easiest solution is to write the output of the SQL Server SP to a table in SQL Server, then write an ODBC job to insert those rows into an Oracle table, and then after success, clean out the SQL Server table to prepare for the next run.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile innovative cognitive big data clouds)


RE: Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

Another possibility is to write the SP results into a SQL Server table and then use Microsoft SSIS to move the table to Oracle. Then run an Oracle SP to load the target table. After everything succeeds, truncate/rebuild the SQL Server and Oracle staging tables.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile innovative cognitive big data clouds)


RE: Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

And the easiest way is to run the sql server stored procedure in sql server and directly write into the oracle table. Not that difficult to do.

Bill
Lead Application Developer
New York State, USA

RE: Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

(OP)
Thanks for all your suggestions.

My original idea is what Bill mentioned, "run the sql server stored procedure in sql server and directly write into the oracle table", but i have no idea that what has to be configured in both server A (SQL server) and server B (Oracle) before that can be done.

I am confused of how to do that. Any advice is welcome.

Thanks again.

RE: Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

approaches are as mentioned
linked servers - I think this was Bill suggestion (although one could also argue that using a SSIS package to execute the stored proc returning a recordset and inserting this on a oracle destination would be what he was suggesting - see below).

SSIS - execute the proc against sql server, returning a recordset (or inserting into a table and then selecting from it) and inserting into a oracle destination.
If volumes are high I would go with this option and if you are using Enterprise version I would also use the Microsoft Attunity driver for Oracle to load the data.
If you don't have Enterprise edition then CozyRoc would be another option to use.

mix of SSIS, flatfile, sqlloader with direct path settings and caching configured


search the net for sql server linked oracle servers and you will find documentation on those.

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

I should have expanded my answer. I was going to suggest a linked server on the SQL server. To see instructions simple query google with the following key words

microsoft linked server oracle

Bill
Lead Application Developer
New York State, USA

RE: Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

Hi

I agree creating a link from SQL server to Oracle is one/might be best option.

Alternatively You can create a database link from Oracle to SQL server using HSODBC and use the link to access SQL server objects

Let's say you want to refresh Table A from SQL server to Table B in Oracle periodically

DBMS_JOB===>CALL ORACLE SP===>READ SQL SERVER TABLE USING HSODBC LINK AND POPULATE ORACLE TABLE B

I think you can also try executing stored procedure in SQL server using the link from oracle


garan

RE: Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

I agree with garani. I have done both. I have oracle reading and updating sql server tables though both oracle sql and T-SQL using the DBMS_HS_PASSTHROUGH package. I also have sql server updating oracle tables using linked servers. Both work fine.

Bill
Lead Application Developer
New York State, USA

RE: Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

(OP)
Thanks for all your suggestions.

As instructed by my manager, I'm not allowed to make changes to the SQL server A and nothing can be done on the SQL server A, NO SSIS and no customized Stored Proc...etc, I am only allowed to run the Stored Proc in SQL server A (sorry not mentioned this part in my previous message). Therefore, the SQL server A needs to be remained as it is while in Oracle 10g Server B, it has to create the HSODBC DB link and some kind of coding in here.

Sum up all your kind suggestions and my preliminary ideas are:

1. Create DB link in Oracle 10g(Server B) using HSODBC
2. Use the DB link in Oracle 10g(Server B) to call and run the Stored Proc in SQL 2008 R2(Server A). For this, I assume the results of the Stored Proc in SQL 2008 R2(Server A) can be populated in the table of Oracle 10g(Server B).

Do you think the above are feasible ? If yes, will that be very complicated for a newbie? Or any special steps I should be aware?

Your input or useful ref link that can help my ideas are always appreciated.

Thanks.

RE: Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

Hi

HSODBC is little complex for a newbie but still you can do again your oracle server should be running on Windows NT/7/8 so that you can configure ODBC connection. If it is running on Unix there might be additional complexity. This was the old method which we used HSODBC.ORA instead of tnsnames.ora for configuring


1. You can try involking SQL server stored procedure using the package DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE i guess this came in oracle 10g


Here hsa is the database link from oracle to sqlserver and create_Receipt is the sql server stored procedure

Please refer below link

https://community.oracle.com/thread/1009253


declare
dummy integer;
string1 varchar2(100);
string2 varchar2(100);
begin
dummy:= DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@hsa('Create_Receipt "'||string1||'" , "'||string2||'"' );
end;


garan

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close