Looking for ideas:
Need some data (within Oracle/Lnux) from SQLServer(2K) on a CONTINUAL basis.

Does NOT need to be Real-Time...can be like Mat. View.

What are my options in impleneting this process ?

thanks
(and Yes I'm also asking the Microsoft guys also)


--
10gR2/Linux

klabu wrote:
How much data? Both companies offer solutions for this. From my
experience the Oracle one is more likely to work with newer versions
of Oracle.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

"DA Morgan"
Just a SELECT.....like < 1000 records

I read this
http://www.oracle.com/technology/pro...y_rel2_ds.html
Transparent Gateway is not an option (10gR2/Linux)

Generic Connectivity - not sure if I read it correctly - restriction :
Installed on the same machine as the database

"klabu" <noone_at_gmail_dot_com> wrote

You could install the Oracle client software on the SQL Server box, and then
create a DTS package to transfer data from SQL Server to the Oracle server.
You have to do the "mapping" of tables one by one though. Scheduling can be
done with the SQL Server agent, and you don't need any extra software
licenses to use this.

Matthias

klabu wrote:
To add another possible solution to the mix, we used the JVM in the
database to do this a few years ago (the SQL Server guys were kind of a
pain to get to do anything). Set autocommit to false on the connection
object and loop through the SQL Server resultset, manually committing
at the end to ensure they all succeed as a group...

<air code>

import java.sql.*;
public class foo {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
Connection con =
DriverManager.getConnection("jdbc:microsoft:sqlser ver://sql_server_host:1433","username","password");
con.autocommit(false);

PreparedStatement ps = con.prepareStatement("{ call
sql_server_proc() }");
//or below if a straight SELECT...
//PreparedStatement ps = con.prepareStatement("select column1 from
sql_server_table");
ps.setFetchSize(100); //or something higher to get more rows at
once...
ResultSet rs = ps.executeQuery();
PreparedStatement psInsert = con.prepareStatement("insert into your
table(column1) values(?)");
while (rs.next())
psInsert.setString(1,rs.getString(1));
psInsert.execute();
}
con.commit();
con.close();
}

</air code>

Regards,

Steve

klabu wrote:

You could install the TG4MSSQL on the windows box and then use a database link
to pull it.


This would use ODBC-type services.


--
Michael Austin.