Hi Everyone! How can I automate a “SELECT INTO” query from one table in Oracle and into a table in Sql Server without exporting the result to a flat file? It seems to me, every database activity (like Execute Script) can use only one specified data connection during automation.
Thank for your help!
I’m trying to go the opposite direction – I have 2 MS SQL sources that I cross query, and I’d like to insert the results into an Oracle table
I ended up using the import wizard in TOAD automation and feeding the cross-server query to the “source” portion and selecting an Oracle table as the destination.
What I’d really like this the ability to build a default action into every automation script that upon either successful completion or error, would write the results to a table on the SQL server that I use, regardless of the originating data source.
It would be nice to build some reports on run time and average failure rate, as well as having one report (via SSRS) I could look at to see if one of the 90+ automated reports has failed.
You can. Just create an automation script that calls the automation script that you already created. Use an If condition (system activity) that tests the return value. If it is greater than zero then write out to any database you like and/or send yourself an email. Use the same method for all your jobs just write out to the same place on error each time.
That works, but it requires that the run automation activity be inserted into every script manually - I’d rather it just be built in, fewer points of failure if it’s built in.