call sproc in Oracle

Question

call sproc in Oracle

sqlfriend

SSC Guru

Points: 52769
More actions
May 14, 2013 at 10:02 am

#277531

We have a .net application, when a new entry is inserted or updated in SQL server table, we would also need to do the same thing on remote oracle server on our domain.
A stored procedure is ready in the oracle side. We just need to call that stored procedure.
What is the best way to do it? In code or in SQL server?
Thanks,

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I'd probably have the application do the update, but that's just me. The app could be coded such that the update needs to occur in both or fail if either fails to update.

Depends on your requirements.

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

Lynn Pettis (5/14/2013)
I'd probably have the application do the update, but that's just me. The app could be coded such that the update needs to occur in both or fail if either fails to update.
Depends on your requirements.

The requirement is updates needs to occur in both or fail if either fails to update.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

sqlfriends (5/14/2013)
Lynn Pettis (5/14/2013)
I'd probably have the application do the update, but that's just me. The app could be coded such that the update needs to occur in both or fail if either fails to update.
Depends on your requirements.
The requirement is when updates needs to occur in both or fail if either fails to update.

You are looking for distributed transaction across different RDBMS's. Check this one, if you want to do it from code:

http://www.techrepublic.com/article/distributed-transactions-span-sql-server-and-oracle/1054237

or this one, if using SSIS (advisable):

http://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

Thanks, but this is .net application we probably not going to use SSIS.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

sqlfriends (5/14/2013)
Lynn Pettis (5/14/2013)
I'd probably have the application do the update, but that's just me. The app could be coded such that the update needs to occur in both or fail if either fails to update.
Depends on your requirements.
The requirement is updates needs to occur in both or fail if either fails to update.

Then I would do this in code, not the database.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

sqlfriends (5/14/2013)
Thanks, but this is .net application we probably not going to use SSIS.

than use the first link from my previous post.

You can use ADO.NET transaction to do so, but you may find some different issues and limitation while doing so. Try to open Oracle connection first...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

sqlfriend SSC Guru Points: 52769 More actions · Answer 7

Thanks, that is an article in 2002, I wonder if there is newer technology in it, we really don't want to use linked server option. Thanks,

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

sqlfriends (5/14/2013)
Thanks, that is an article in 2002, I wonder if there is newer technology in it, we really don't want to use linked server option. Thanks,

If you are doing it in the .NET code you should be able to connect directly to both databases and not use a linked server on the SQL Server system.

sqlfriend SSC Guru Points: 52769 More actions · Answer 9

Any sample code on internet we can take a look?

Thanks,

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

sqlfriends (5/14/2013)
Any sample code on internet we can take a look?
Thanks,

Use Google or Bing, I only wrote the Oracle database code, not the code that accessed it.