hi all,
we have two servers. we have an oracle 8i and a sql 2000 server.
is it possible to write stored procedures or triggers in oracle 8i that will create, update and delete records in sql server.if so what are the steps to do so and an example of a trigger/stored procedure. i have seen many ways to connect two oracle or two sql servers. i also see ways to link the servers but what we are trying to do is when something commits in the the oracle database that it will also do the same in the sql server. keep in mind out table structures are different due to security rights on the sql server that is why the some fields will not move over. also what is the best method to do this? i also see using vb uding ado rdo. i see ole db and odbc connections between the two, and i see some third party software. any help is much appreciaedI would think that your best bet would be to accomplish this task using an application front end and not trying to do it through a trigger or stored proc on Oracle. But that's probably because I'm more of a developer than a DBA (tho' I'm working on the latter).
Using ADO, you can use BeginTrans and CommitTrans to ensure that a transaction completes on SQL before committing the transaction on Oracle. Set checks for errors and use RollbackTrans on both connections to "undo" everything if a problem occurs.
That said, what really matters is your requirement; are these transactions user-initiated or are they meant to be a part of an automated extract? Though each can be handled by ADO, you would handle each situation a little differently.
Regards,
Hugh Scott
Originally posted by rdavidoff
hi all,
we have two servers. we have an oracle 8i and a sql 2000 server.
is it possible to write stored procedures or triggers in oracle 8i that will create, update and delete records in sql server.if so what are the steps to do so and an example of a trigger/stored procedure. i have seen many ways to connect two oracle or two sql servers. i also see ways to link the servers but what we are trying to do is when something commits in the the oracle database that it will also do the same in the sql server. keep in mind out table structures are different due to security rights on the sql server that is why the some fields will not move over. also what is the best method to do this? i also see using vb uding ado rdo. i see ole db and odbc connections between the two, and i see some third party software. any help is much appreciaed|||thanks for your suggestion,
here is the business process that will give you a better understanding of our problem. see a sql server was purchased becuase a web developer is gong to do web based reports using tables from sql server 2000. the problem is that the company has a legacy 8.04 oracle dtabase that has been installed for years. the web developer wants to use and convinced hi people he wants to use sql server 2000. our job is to come up with a process to make the sql server data be as real time as possible to the oracle database b/c everything is really getting stored there. so whether it be by triggers, stored procedures, or some front end using either ado, ole db, odbc, or whatever api thats out there.
do you have some sort of sample of an ado instance. all out transactions will be user initiated and committed on the oracle side
thanks again,
robert
Originally posted by hmscott
I would think that your best bet would be to accomplish this task using an application front end and not trying to do it through a trigger or stored proc on Oracle. But that's probably because I'm more of a developer than a DBA (tho' I'm working on the latter).
Using ADO, you can use BeginTrans and CommitTrans to ensure that a transaction completes on SQL before committing the transaction on Oracle. Set checks for errors and use RollbackTrans on both connections to "undo" everything if a problem occurs.
That said, what really matters is your requirement; are these transactions user-initiated or are they meant to be a part of an automated extract? Though each can be handled by ADO, you would handle each situation a little differently.
Regards,
Hugh Scott|||Ewww, yuck. I realize decisions have already been made, but there's really nothing wrong with developing web apps using Oracle. I prefer SQL, but that's a different story.
We're doing something that might be considered a bit similar, but it is by no means "realtime". We have a production AS/400. Every fifteen minutes we siphon off selected data to a SQL server. We then use the SQL server to display the data on the web. The customer accepts the fifteen minute delay as a penalty. A side benefit is that the load on the AS/400 is regular and predictable while the customer can run queries to his heart's delight (and they delight in it a lot!) on the SQL server.
To pull the data from Oracle to SQL we use DTS packages that are scheduled on the SQL server.
Option 1
Do you control (own the source code) the application that stores the data on the Oracle Server?
If the answer is "yes", then you have a lot of rewriting to do to make updates to both databases, but it is potentially doable and "real time". Whether the re-write is justifiable is another matter for management to decide.
Option b
A possible alternative is to use a Linked Server (establish the Oracle Server as a linked server on SQL). You can then write distributed Queries that access the Oracle data directly. CAUTION: my experience with distributed queries is not stellar. The more complex they are, the longer they take to run. See SQL Books On Line for more information on Distributed Queries and Linked Servers.
Option iii
Use DTS packages to pull the data into SQL on a scheduled basis. This is doable (we are doing it now) but it requires a LOT of thought into what data is going to be brought across and consideration must be given to the state of the data (ie, open orders versus closed orders, etc).
Option Other
Maybe there is a way to do this in Oracle using triggers or stored procedures. You might even look into replication (now there's an idea!), but I have no idea how to go about setting it up.
Or you could simply find a web developer willing to work with Oracle!!!
Sorry, I hope one of these helps!
Regards,
Hugh Scott
Originally posted by rdavidoff
thanks for your suggestion,
here is the business process that will give you a better understanding of our problem. see a sql server was purchased becuase a web developer is gong to do web based reports using tables from sql server 2000. the problem is that the company has a legacy 8.04 oracle dtabase that has been installed for years. the web developer wants to use and convinced hi people he wants to use sql server 2000. our job is to come up with a process to make the sql server data be as real time as possible to the oracle database b/c everything is really getting stored there. so whether it be by triggers, stored procedures, or some front end using either ado, ole db, odbc, or whatever api thats out there.
do you have some sort of sample of an ado instance. all out transactions will be user initiated and committed on the oracle side
thanks again,
robert|||Originally posted by hmscott
We're doing something that might be considered a bit similar, but it is by no means "realtime". We have a production AS/400. Every fifteen minutes we siphon off selected data to a SQL server. We then use the SQL server to display the data on the web. The customer accepts the fifteen minute delay as a penalty. A side benefit is that the load on the AS/400 is regular and predictable while the customer can run queries to his heart's delight (and they delight in it a lot!) on the SQL server.
I am atempting this very thing. Only difference is it is not Oracle it is a remote Turbo Image Database (ISAM files).
I have a stored procedure written that does a set of queries using OPENQUERY and updates three tables. My plan is to run it once every 10 - 15 minutes or so.
My stumbling block is that the job I scheduled to run the stored procedure is failing, but once I get that worked out it should be fine. I have talked to the owners of the data (it is a registration database for a community college) and the delay is acceptable in this situation.
So, if the data is not life threatining if there is a delay, I'd say that this is your best bet.|||i was reading ur option iii and it really doesn't sound that bad. thanks again for all the advice 15 minutes delayed is still good enough for what we intend to use this data for. after allwe are not a brokerage company that needs streaming real time data. do you have any information or advise pn dts packages and where may i look into this further.
thanks again
Bob
Originally posted by hmscott
Ewww, yuck. I realize decisions have already been made, but there's really nothing wrong with developing web apps using Oracle. I prefer SQL, but that's a different story.
We're doing something that might be considered a bit similar, but it is by no means "realtime". We have a production AS/400. Every fifteen minutes we siphon off selected data to a SQL server. We then use the SQL server to display the data on the web. The customer accepts the fifteen minute delay as a penalty. A side benefit is that the load on the AS/400 is regular and predictable while the customer can run queries to his heart's delight (and they delight in it a lot!) on the SQL server.
To pull the data from Oracle to SQL we use DTS packages that are scheduled on the SQL server.
Option 1
Do you control (own the source code) the application that stores the data on the Oracle Server?
If the answer is "yes", then you have a lot of rewriting to do to make updates to both databases, but it is potentially doable and "real time". Whether the re-write is justifiable is another matter for management to decide.
Option b
A possible alternative is to use a Linked Server (establish the Oracle Server as a linked server on SQL). You can then write distributed Queries that access the Oracle data directly. CAUTION: my experience with distributed queries is not stellar. The more complex they are, the longer they take to run. See SQL Books On Line for more information on Distributed Queries and Linked Servers.
Option iii
Use DTS packages to pull the data into SQL on a scheduled basis. This is doable (we are doing it now) but it requires a LOT of thought into what data is going to be brought across and consideration must be given to the state of the data (ie, open orders versus closed orders, etc).
Option Other
Maybe there is a way to do this in Oracle using triggers or stored procedures. You might even look into replication (now there's an idea!), but I have no idea how to go about setting it up.
Or you could simply find a web developer willing to work with Oracle!!!
Sorry, I hope one of these helps!
Regards,
Hugh Scott|||Some gotcha's that you may want to consider and a site that is well worth looking at:
1. Data Transformation Services (DTS) are essentially mini-programs that are created in a GUI environment. As such, there is a LOT of complexity that is hidden from you. As you can imagine, there are both strong points and weak points to this:
a. Strong point: they are fairly easy to create and work with
b. Strong point: there are a lot of objects to work with and that makes just about any task doable.
c. Weak point: they are sometimes difficult to manage unless you spend a lot of time learning how to use dynamic properties that can be stored on the server
d. Weak point: since it's easy for a novice to work with these, it's easy to make a novice mistake and mess up a bunch of things
2. DTS runs in a separate memory space from the SQL server application. I have recently experienced some very negative side effects from having too many DTS packages running simultaneously. If it is at all possible, consider running DTS on a separate server from the database server.
3. This is a hard one for people new to DTS to understand. DTS always runs in the context of the client machine on which you are viewing the DTS package. Even though you are in Enterprise Manager and you THINK you are executing the DTS package on the SQL server, it is actually running using the DLL context of your client workstation. Send me an e-mail and I'll explain this one further. It's an important concept and not very well explained (in my opinion) in SQL BOL.
4. I have had a VERY bad experience with Meta Data Services. I would recommend avoiding this feature in SQL Server in a production environment.
5. Be sure that your client workstation and the SQL Server where the packages are stored are running the SAME version of SQL Server (same SP, too). DTS pacakges can be migrated from older versions to newer versions, but they are not necessarily backward compatible. DTS pacakges in SQL 7.0 were pretty bad until around SP3.
66. A good website to check out is www.sqldts.com. I found a really great tool for backing up DTS pacakges on there.
Good luck,
Hugh Scott
Originally posted by rdavidoff
i was reading ur option iii and it really doesn't sound that bad. thanks again for all the advice 15 minutes delayed is still good enough for what we intend to use this data for. after allwe are not a brokerage company that needs streaming real time data. do you have any information or advise pn dts packages and where may i look into this further.
thanks again
Bob
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment