Re: CFMX Oracle and SQL Server Fun
I would use a DTS package.I have a process here that runs nightly and it is a DTS package that connects to the Oracle 9i server and pulls data out of views and places that data into tables on the SQL Server then manipulates that data into the final tables that their application accesses. On Tue, 10 Aug 2004 10:51:50 -0400, James Blaha [EMAIL PROTECTED] wrote: CFers, I'm need some advice. I have a few tables in Oracle 9i that I need to get into SQL Server 7 daily and when needed, the basic function of the process is get the data from Oracle then perform some joins followed by creating a new table in SQL Server with the results each time this job is run. What's the best way to do this? Right now I have a CFMX server with a JDBC connection to Oracle then I CFQUERY my way through this task using a CFM template. I was thinking if I could use a SQL Server SPROC this might be a better method. Does anyone out here do anything like this? By what I've been researching using a SQL Server Linked Server may be the option I'm looking for. Regards, -JB [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: CFMX Oracle and SQL Server Fun
I'm need some advice. I have a few tables in Oracle 9i that I need to get into SQL Server 7 daily and when needed, the basic function of the process is get the data from Oracle then perform some joins followed by creating a new table in SQL Server with the results each time this job is run. What's the best way to do this? Right now I have a CFMX server with a JDBC connection to Oracle then I CFQUERY my way through this task using a CFM template. I was thinking if I could use a SQL Server SPROC this might be a better method. Does anyone out here do anything like this? By what I've been researching using a SQL Server Linked Server may be the option I'm looking for. I'd second Aaron Rouse's suggestion to use DTS. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CFMX Oracle and SQL Server Fun
My first thought was DTS but arent SPROCs much faster to execute? Plus I need to execute the DTS via a Cold Fusion template I know how to due this but I always run through loops with my DBA about the setup with all the permissions I need to have setup. Regards, -JB Dave Watts wrote: I'm need some advice. I have a few tables in Oracle 9i that I need to get into SQL Server 7 daily and when needed, the basic function of the process is get the data from Oracle then perform some joins followed by creating a new table in SQL Server with the results each time this job is run. What's the best way to do this? Right now I have a CFMX server with a JDBC connection to Oracle then I CFQUERY my way through this task using a CFM template. I was thinking if I could use a SQL Server SPROC this might be a better method. Does anyone out here do anything like this? By what I've been researching using a SQL Server Linked Server may be the option I'm looking for. I'd second Aaron Rouse's suggestion to use DTS. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CFMX Oracle and SQL Server Fun
Why do you need to execute the DTS from CFMX?I suppose you could do a combination of DTS and stored procedures.I personally have never used an SP to connect to another flavor database and pull out date, honestly not even sure how that would be done, so could not say if it is faster or not to do it solely through an SP. On Tue, 10 Aug 2004 11:25:13 -0400, James Blaha [EMAIL PROTECTED] wrote: My first thought was DTS but aren't SPROCs much faster to execute? Plus I need to execute the DTS via a Cold Fusion template I know how to due this but I always run through loops with my DBA about the setup with all the permissions I need to have setup. Regards, -JB [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CFMX Oracle and SQL Server Fun
Aaron, Check this out: http://www.winnetmag.com/SQLServer/Article/ArticleID/22264/SQLServer_22264.html Regards, -JB Aaron Rouse wrote: Why do you need to execute the DTS from CFMX?I suppose you could do a combination of DTS and stored procedures.I personally have never used an SP to connect to another flavor database and pull out date, honestly not even sure how that would be done, so could not say if it is faster or not to do it solely through an SP. On Tue, 10 Aug 2004 11:25:13 -0400, James Blaha [EMAIL PROTECTED] wrote: My first thought was DTS but aren't SPROCs much faster to execute? Plus I need to execute the DTS via a Cold Fusion template I know how to due this but I always run through loops with my DBA about the setup with all the permissions I need to have setup. Regards, -JB [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CFMX Oracle and SQL Server Fun
DTS can run stored procedures as well. The two options aren't exclusive. If you're really worried about speed, then you should definitely cut CF out of the loop and have SQL Server pull the data directly from Oracle. If you have SQL Server talk to Oracle directly, then you will probably need to set up an Oracle connection for your SQL Server database, as your article says. See http://www.orafaq.com/faqodbc.htm for more details. Unless you need real-time data, for performance issues, it's better to make a local copy of the Oracle table(s) that you need rather than having to fetch the table across the network all the time. Again, this scheduled import is best done with DTS. --- James Blaha [EMAIL PROTECTED] wrote: Aaron, Check this out: http://www.winnetmag.com/SQLServer/Article/ArticleID/22264/SQLServer_22264.html Regards, -JB Aaron Rouse wrote: Why do you need to execute the DTS from CFMX?I suppose you could do a combination of DTS and stored procedures.I personally have never used an SP to connect to another flavor database and pull out date, honestly not even sure how that would be done, so could not say if it is faster or not to do it solely through an SP. On Tue, 10 Aug 2004 11:25:13 -0400, James Blaha [EMAIL PROTECTED] wrote: My first thought was DTS but aren't SPROCs much faster to execute? Plus I need to execute the DTS via a Cold Fusion template I know how to due this but I always run through loops with my DBA about the setup with all the permissions I need to have setup. Regards, -JB [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CFMX Oracle and SQL Server Fun
Interesting, though I can not think of any data center I have worked with that would be happy with a request to do such a link.Then again the guys around here in the data centers are set in their ways and never like change.I'd have to wonder if after linking databases if it is faster than just copying the data over with a DTS package then manipulating it however you need to. On Tue, 10 Aug 2004 11:41:10 -0400, James Blaha [EMAIL PROTECTED] wrote: Aaron, Check this out: http://www.winnetmag.com/SQLServer/Article/ArticleID/22264/SQLServer_22264.html Regards, -JB [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
