| Author |
Topic |
|
a_r_satish
Yak Posting Veteran
84 Posts |
 Posted - 2006-02-15 : 04:25:26
|
| Hi All,I need the fetch the details from oracle db. I am doing this in my front end.Now i have to move this details fetched from other application's db(oracle) to my applications db that is SQL Server in a temp table.Can any one suggest a simplest way to do that.i have a option of moving the details to a dataset and then move the records from there. But i need an alternative.Nb: I don use any linked server or stored procedure to fetch the other db's details.Regards,satish.r |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-15 : 04:46:30
|
| You can use DTS or export the data from Oracle and import into SQL Server using BCP [command line utility] or if you set up a Linked Server you can use OPENQUERY:INSERT INTO MyLocalTableSELECT *FROM OPENQUERY(MyOracleServer, 'SELECT * FROM MyOracleTable')Kristen |
 |
|
|
a_r_satish
Yak Posting Veteran
84 Posts |
Posted - 2006-02-15 : 05:02:08
|
As i mentioned, i don have any linked server options I have fetched the oracle details using a query in the front end. Now i have to move that to my db[Ms-SQL] from the front end.Regards,satish.r |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-02-15 : 05:30:30
|
| Use DTS then as suggested by kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-15 : 13:14:06
|
"I have fetched the oracle details using a query in the front end."Does that mean you have a login to Oracle? Because if so you should be able to use that to create a Linked Server from your SQL Box  Kristen |
|
|
|
a_r_satish
Yak Posting Veteran
84 Posts |
Posted - 2006-02-15 : 23:21:32
|
| To fetch the oracle db details i am using a seperate connection.This is based on the project i am connecting. When i am connecting to any other application, i use the connection string and based on the connection string get the other db details.So i use 2 connections. 1 to connect to my database (MS-SQL) and2 to connect to any other db (ORACLE).Now how to do?I don have the Linked server option. Thats the reason i am using 2 connections. Moreover my purpose is to sync my db(MS SQL) with actual live DB(ORACLE) and display the changes in the db over a period of time. (NO DTS OPTION)I hope i am very much clear.Regards,satish.r |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-02-16 : 01:09:26
|
| Check for OpenRowset.. specify the Provider , datasource etc and you can join it with your Sql server table. |
|
|
|
a_r_satish
Yak Posting Veteran
84 Posts |
Posted - 2006-02-16 : 05:06:40
|
| The openrowset works fine with the same database. All the schema within the Database can be connected.But to connect to a different server you need to have a linked server which i dont haveRegards,satish.r |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-02-16 : 05:15:09
|
| For executing OpenRowset you dont require to have a linked serverFrom BOLThis method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. Check BOL for More info on how to use OpenROwset for retrieving data from different databases. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-16 : 05:37:14
|
| "I don't have the Linked server option"Do you mean that for some (e.g. security) reason you are not allowed to create one?Kristen |
|
|
|
a_r_satish
Yak Posting Veteran
84 Posts |
Posted - 2006-02-16 : 07:02:48
|
| Yeah. You are absolutely right. for security reasons i don have permission to use.I don understand BOL. Ya i ll try to search and get back soonRegards,satish.r |
|
|
|
|
simplest way to move data to sql server from other