Thread: porting a full Ms Sql Server to postgres
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
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.
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
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/
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