porting a full Ms Sql Server to postgres

On 24 Sep 2004 at 17:23, pwbyrne@hotmail.com wrote:

Hi,

I'm looking for details, or tools about porting a full Ms Sql Server
2000 database to Postgres on Linux.

Is this possible? We have the whole nine yards, stored procedures,
triggers, and all.

Is there a way to automate this process?

You can probably to a semi-automated process. I have sucessfully
transfereed the bulk of the data from SQlServer to PostgreSQL using
the SQLServers Data Transformation Services (DTC) and the
PostgreSQL ODBC driver. As long as you defined the tables and
columns the same in both databases you should be OK.

If you need to create the schema first, you can "script" the SQLServer
database and hack the output file.

Put indexes and foreign keys on the PostgreSQL database *after* you
transfer the data.

You would need to redefine the triggers and stored procedures
manually.

For our own specific databases I have written a small program (in C#)
to dump the contents of SQLServer tables into a PostgreSQL COPY
command format. This speeds up the actual data transfer by an order of
magnitude or so!

Cheers,
Gary.

pwbyrne@hotmail.com wrote:

Hi,

I'm looking for details, or tools about porting a full Ms Sql Server
2000 database to Postgres on Linux.

Is this possible? We have the whole nine yards, stored procedures,
triggers, and all.

Is there a way to automate this process?

Thanks

Totally automate - no.

There are tools to help you along, however. They are not as complete as
I would like them, and I need lots of help in maintaining the site. It's
located at http://pgfoundry.org/projects/sql2pg/, but there are
currently no released files. In CVS, however, you will find a perl
script that translates the exported database description from MS SQL to
a more-or-less PG friendly format. Also you will find there support for
two data types - varcharci (case insensitive) and utinyint (1 byte
unsigned integer).

Any help with maintaining this project would be greatly appreciated.

I do have a few questions for you. What drivers do you currently use to
access your database? Do you use the stored procedure's ability to
return more than one result?

Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/

It's not possible to easily port functions and triggers. Tables, views and data are pretty easy.

<<< <pwbyrne@hotmail.com> 9/26 4:10p >>>
Hi,

I'm looking for details, or tools about porting a full Ms Sql Server
2000 database to Postgres on Linux.

Is this possible? We have the whole nine yards, stored procedures,
triggers, and all.

Is there a way to automate this process?

Thanks

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

hi!
Everybody.

I have written a code to connec to postgre sql
databse.But i am geting some error.

import java.sql.*;
import java.util.*;
import java.lang.*;

public class LoginPage {
public static void main(String[] args)
{
Connection conn;
try
{

Class.forName("org.postgresql.Driver");
String url =
"jdbc:postgresql://localhost:5432/test";
conn = DriverManager.getConnection(url,
"postgres", "viwotec");

Statement s = conn.createStatement();
ResultSet r = s.executeQuery("select
oid,id,name,password from myschema.login");
while( r.next() )
{

int id = r.getInt(2);
System.out.println("Row " + id + ":");

int oid = r.getInt(2);
System.out.println("Rowoid " + oid + ":");

/*String nam = r.getString();
System.out.println("Name of the person" + nam
+ ":");

String pass = r.getString();
System.out.println("Password you entered was"
+ pass + ":"); */

}
s.close();
conn.close();
}
catch( Exception e )
{
e.printStackTrace();
}
}
}

and the error is
java.lang.ClassNotFoundException:
org.postgresql.Driver
at
java.net.URLClassLoader$1.run(URLClassLoader.java:199)
at
java.security.AccessController.doPrivileged(Native
Method)
at
java.net.URLClassLoader.findClass(URLClassLoader.java:187)
at
java.lang.ClassLoader.loadClass(ClassLoader.java:289)
at
sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:274)
at
java.lang.ClassLoader.loadClass(ClassLoader.java:235)
at
java.lang.ClassLoader.loadClassInternal(ClassLoader.java:302)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:141)
at LoginPage.main(LoginPage.java:14)

please tell me solution for this...How i can load the
driver and where

--- Shachar Shemesh <shachar@lingnu.com> wrote:

pwbyrne@hotmail.com wrote:

Hi,

I'm looking for details, or tools about porting a

full Ms Sql Server

2000 database to Postgres on Linux.

Is this possible? We have the whole nine yards,

stored procedures,

triggers, and all.

Is there a way to automate this process?

Thanks

Totally automate - no.

There are tools to help you along, however. They
are not as complete as
I would like them, and I need lots of help in
maintaining the site. It's
located at http://pgfoundry.org/projects/sql2pg/,
but there are
currently no released files. In CVS, however, you
will find a perl
script that translates the exported database
description from MS SQL to
a more-or-less PG friendly format. Also you will
find there support for
two data types - varcharci (case insensitive) and
utinyint (1 byte
unsigned integer).

Any help with maintaining this project would be
greatly appreciated.

I do have a few questions for you. What drivers do
you currently use to
access your database? Do you use the stored
procedure's ability to
return more than one result?

Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/

---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please
send an appropriate
subscribe-nomail command to
majordomo@postgresql.org so that your
message can get through to the mailing list
cleanly

__________________________________
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo