subject:"RE\: Transferring from MS SQL \(2000\) to PostgreSQL"

Okay, so far I'm *really* liking Pg.  I have successfully imported all of my
tables and data, and have everything fixed with one exception: updating the
currval() of the sequence for the 'auto_number' fields.  I can go through
each sequence one at a time with the GUI and update it, but I'd obviously
prefer to do so via script.  Here's what I've tried, but it throws a syntax
error:

ALTER SEQUENCE seq_name RESTART WITH (SELECT max(id) + 1 FROM table_name);

Apparently it does not like the query to be used as the value.  I'm still
reading through the docs to see if I can find where/how to do what I'm
trying to do, but figured I'd cheat and post back to you guys as well.


Thanks,

Matt


~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266422
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

I don't know why i wrote the '+1' in there when I wrote the email.  I'm not
using it in the actual statement.  The specific syntax error is that the (
is invalid.

On 1/12/07, Matt Quackenbush [EMAIL PROTECTED] wrote:

 Okay, so far I'm *really* liking Pg.  I have successfully imported all of
 my tables and data, and have everything fixed with one exception: updating
 the currval() of the sequence for the 'auto_number' fields.  I can go
 through each sequence one at a time with the GUI and update it, but I'd
 obviously prefer to do so via script.  Here's what I've tried, but it throws
 a syntax error:

 ALTER SEQUENCE seq_name RESTART WITH (SELECT max(id) + 1 FROM table_name);

 Apparently it does not like the query to be used as the value.  I'm still
 reading through the docs to see if I can find where/how to do what I'm
 trying to do, but figured I'd cheat and post back to you guys as well.


 Thanks,

 Matt




~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266425
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

2007-01-12 Thread Jon Clausen

Matt,

I sent in a previous e-mail but you may have missed it:

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence 
('#table#','#table_id#'), (SELECT max(#table_id#) FROM #table#), true);

That way you don't have  to provide the sequence name.

HTH,
Jon

On Jan 12, 2007, at 2:41 PM, Matt Quackenbush wrote:

 I don't know why i wrote the '+1' in there when I wrote the email.   
 I'm not
 using it in the actual statement.  The specific syntax error is  
 that the (
 is invalid.

 On 1/12/07, Matt Quackenbush [EMAIL PROTECTED] wrote:

 Okay, so far I'm *really* liking Pg.  I have successfully imported  
 all of
 my tables and data, and have everything fixed with one exception:  
 updating
 the currval() of the sequence for the 'auto_number' fields.  I can go
 through each sequence one at a time with the GUI and update it,  
 but I'd
 obviously prefer to do so via script.  Here's what I've tried, but  
 it throws
 a syntax error:

 ALTER SEQUENCE seq_name RESTART WITH (SELECT max(id) + 1 FROM  
 table_name);

 Apparently it does not like the query to be used as the value.   
 I'm still
 reading through the docs to see if I can find where/how to do what  
 I'm
 trying to do, but figured I'd cheat and post back to you guys as  
 well.


 Thanks,

 Matt




 

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266430
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

Jon,

Thanks for the reply.  Indeed I did miss it.  I was hoping to write one long
..sql script and run it from my query editor and update all tables in a
single connection.  Are you aware of a way to do that?  If not, I'll use
your CF solution :-)


Matt


On 1/12/07, Jon Clausen [EMAIL PROTECTED] wrote:

 Matt,

 I sent in a previous e-mail but you may have missed it:

 SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence
 ('#table#','#table_id#'), (SELECT max(#table_id#) FROM #table#), true);

 That way you don't have  to provide the sequence name.

 HTH,
 Jon

 On Jan 12, 2007, at 2:41 PM, Matt Quackenbush wrote:

  I don't know why i wrote the '+1' in there when I wrote the email.
  I'm not
  using it in the actual statement.  The specific syntax error is
  that the (
  is invalid.
 
  On 1/12/07, Matt Quackenbush [EMAIL PROTECTED] wrote:
 
  Okay, so far I'm *really* liking Pg.  I have successfully imported
  all of
  my tables and data, and have everything fixed with one exception:
  updating
  the currval() of the sequence for the 'auto_number' fields.  I can go
  through each sequence one at a time with the GUI and update it,
  but I'd
  obviously prefer to do so via script.  Here's what I've tried, but
  it throws
  a syntax error:
 
  ALTER SEQUENCE seq_name RESTART WITH (SELECT max(id) + 1 FROM
  table_name);
 
  Apparently it does not like the query to be used as the value.
  I'm still
  reading through the docs to see if I can find where/how to do what
  I'm
  trying to do, but figured I'd cheat and post back to you guys as
  well.
 
 
  Thanks,
 
  Matt
 
 
 
 
 

 

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266442
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transferring from MS SQL (2000) to PostgreSQL

2007-01-12 Thread Jon Clausen

Truthfully,I've always done it with CF so I don't know what the fully  
query would be with postgres.

You can run the following query to get the table names and primary  
key names (where they are defined)

select table_name, column_name from  
information_schema.constraint_column_usage ;

Then just loop the query.

HTH,
Jon

On Jan 12, 2007, at 3:38 PM, Matt Quackenbush wrote:

 Jon,

 Thanks for the reply.  Indeed I did miss it.  I was hoping to write  
 one long
 ..sql script and run it from my query editor and update all tables  
 in a
 single connection.  Are you aware of a way to do that?  If not,  
 I'll use
 your CF solution :-)


 Matt


 On 1/12/07, Jon Clausen [EMAIL PROTECTED] wrote:

 Matt,

 I sent in a previous e-mail but you may have missed it:

 SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence
 ('#table#','#table_id#'), (SELECT max(#table_id#) FROM #table#),  
 true);

 That way you don't have  to provide the sequence name.

 HTH,
 Jon

 On Jan 12, 2007, at 2:41 PM, Matt Quackenbush wrote:

 I don't know why i wrote the '+1' in there when I wrote the email.
 I'm not
 using it in the actual statement.  The specific syntax error is
 that the (
 is invalid.

 On 1/12/07, Matt Quackenbush [EMAIL PROTECTED] wrote:

 Okay, so far I'm *really* liking Pg.  I have successfully imported
 all of
 my tables and data, and have everything fixed with one exception:
 updating
 the currval() of the sequence for the 'auto_number' fields.  I  
 can go
 through each sequence one at a time with the GUI and update it,
 but I'd
 obviously prefer to do so via script.  Here's what I've tried, but
 it throws
 a syntax error:

 ALTER SEQUENCE seq_name RESTART WITH (SELECT max(id) + 1 FROM
 table_name);

 Apparently it does not like the query to be used as the value.
 I'm still
 reading through the docs to see if I can find where/how to do what
 I'm
 trying to do, but figured I'd cheat and post back to you guys as
 well.


 Thanks,

 Matt








 

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266458
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

Jon,

Your solution worked just fine actually :-)  Thanks again!


Matt


~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266459
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transferring from MS SQL (2000) to PostgreSQL

2007-01-12 Thread Jon Clausen

Glad to help.  Have fun.  :-)

-Jon

On Jan 12, 2007, at 4:52 PM, Matt Quackenbush wrote:

 Jon,

 Your solution worked just fine actually :-)  Thanks again!


 Matt




~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266463
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

All-

I am very pleased to announce that I have the entire SQL Server database
duplicated on PostgreSQL, indexed, PKs, FKs, clustered, etc, and it ROCKS!
Not only that, but my 43-line queries all work properly and are lightening
fast!  Unless I come across something completely unexpected, I feel
confident in saying that you can add me to the ever-growing list of
PostgreSQL converts!

Thanks for ALL of you guys' help!  And thank you to those who enticed me to
open my eyes to a solution other than MS SQL.  :-)


Matt


~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266472
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transferring from MS SQL (2000) to PostgreSQL

Hmmm... I setup PostgreSQL on one of my production (remote to me) servers.
It's all working wonderfully from localhost. To prepare it for allowing
remote access, I added the following line to the pg_hba.conf file:

hostsameuserall (the IP, e.g. 555.555.555.555)password

I then added the following line to the postgresql.conf file, and restarted
the service:

listen_addresses = '*'

I then downloaded/installed ADS here on my test box, and setup the
connection. After the connection timed out, it dawned on me that the port
was blocked. So, I contacted my host and had them open up the appropriate
port. Now that the correct port is open, virtually instantly upon trying to
connect, I receive the following error message:

Connection failed: FATAL: missing or erroneous pg_hba.conf file

Surely it's not looking for the pg_hba file on my local machine whilst
trying to connect to a remote address? I know I must be doing something
wrong, and that it should be really obvious to me as to what it is, but I
cannot seem to clear the clutter in the brain and figure it out. Any help
would be appreciated.

Thanks,

Matt

~|
Create robust enterprise, web RIAs.
Upgrade integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266274
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transferring from MS SQL (2000) to PostgreSQL

2007-01-11 Thread Jochem van Dieten

Matt Quackenbush wrote:
Hmmm... I setup PostgreSQL on one of my production (remote to me) servers.
It's all working wonderfully from localhost. To prepare it for allowing
remote access, I added the following line to the pg_hba.conf file:

hostsameuserall (the IP, e.g. 555.555.555.555)password

This is a bad combination because they result in a cleartext communication and
even a cleartext password. Best would be to install a certificate and use
hostssl instead of host, but at least you should use md5 instead of psssword.

I then added the following line to the postgresql.conf file, and restarted
the service:

listen_addresses = '*'

You don't need to restart the service, you can signal it with pg_ctl reload.

Now that the correct port is open, virtually instantly upon trying to
connect, I receive the following error message:

Connection failed: FATAL: missing or erroneous pg_hba.conf file

That should have generated an error in PostgreSQLs log file or even the Event
Log. Does that error give a line number?

Restore your backup of pg_hba.conf, signal the server and try to connect
locally. Then try your modifications to pg_hba.conf and try to connect locally
again. Then try connecting remotely.

Jochem

~|
Create robust enterprise, web RIAs.
Upgrade integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266287
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transferring from MS SQL (2000) to PostgreSQL

Jochem,

This is a bad combination because they result in a cleartext communication
and even a cleartext password. Best would be to install a certificate and
use hostssl instead of host, but at least you should use md5 instead of
psssword.

Thank you for the tip on the ssl/md5/password issue. At the moment, I am
setting this up on a server purely for my testing purposes, and the server
in question does not have an SSL cert. I did however change it to 'md5'.

You don't need to restart the service, you can signal it with pg_ctl
reload.

I ran the 'pg_ctl reload' command, and it returned the following message:

pg_ctl: no database directory specified and environment variable PGDATA
unset

I then ran the 'pg_ctl --help' command (as instructed), but I'm not
intelligent enough to figure out what the issue is from the info that it
responded with. ;-)

Now that the correct port is open, virtually instantly upon trying to
connect, I receive the following error message:

Connection failed: FATAL: missing or erroneous pg_hba.conf file

That should have generated an error in PostgreSQLs log file or even the
Event Log. Does that error give a line number?

The log files show the following:

LOG: invalid IP mask password in file C:/Program
Files/PostgreSQL/8.2/data/pg_hba.conf line 72: Unknown host

After changing the METHOD to md5, the log files show the updated, but
virtually identical message:

LOG: invalid IP mask md5 in file C:/Program
Files/PostgreSQL/8.2/data/pg_hba.conf line 72: Unknown host

Restore your backup of pg_hba.conf, signal the server and try to connect
locally. Then try your modifications to pg_hba.conf and try to connect
locally again. Then try connecting remotely.

Connecting locally is still a breeze. Remotely, well, I'm obviously still
doing something wrong :-)

Thank you for your assistance in my education.

Matt

~|
Create robust enterprise, web RIAs.
Upgrade integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266293
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transferring from MS SQL (2000) to PostgreSQL

2007-01-11 Thread Jochem van Dieten

Matt Quackenbush wrote:
This is a bad combination because they result in a cleartext communication
and even a cleartext password. Best would be to install a certificate and
use hostssl instead of host, but at least you should use md5 instead of
psssword.

Generate your own certificate with OpenSSL.

I ran the 'pg_ctl reload' command, and it returned the following message:

pg_ctl: no database directory specified and environment variable PGDATA
unset

I then ran the 'pg_ctl --help' command (as instructed), but I'm not
intelligent enough to figure out what the issue is from the info that it
responded with. ;-)

It needs to know which PostgreSQL instance to reload. The directory it wants is
the directory with your pg_hba.conf in it. From there it can figure out the
rest.

LOG: invalid IP mask password in file C:/Program
Files/PostgreSQL/8.2/data/pg_hba.conf line 72: Unknown host

You are missing a column in your pg_hba.conf. Do you have the real one
somewhere?

Jochem

~|
Create robust enterprise, web RIAs.
Upgrade integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266318
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

On 1/11/07, Jochem van Dieten [EMAIL PROTECTED] wrote:

  LOG:  invalid IP mask password in file C:/Program
  Files/PostgreSQL/8.2/data/pg_hba.conf line 72: Unknown host

 You are missing a column in your pg_hba.conf. Do you have the real one
 somewhere?

 Jochem



Which column am I missing?  According to the docs (
http://www.postgresql.org/docs/8.2/static/auth-pg-hba-conf.html), the record
can take one of seven forms:

local  *database*  *user*  *auth-method*  [*auth-option*]
host   *database*  *user*  *CIDR-address*  *auth-method*  [*auth-option*]
hostssl*database*  *user*  *CIDR-address*  *auth-method*  [*auth-option*]
hostnossl  *database*  *user*  *CIDR-address*  *auth-method*  [*auth-option*]
host   *database*  *user*  *IP-address*  *IP-mask*  *auth-method*
[*auth-option*]
hostssl*database*  *user*  *IP-address*  *IP-mask*  *auth-method*
[*auth-option*]
hostnossl  *database*  *user*  *IP-address*  *IP-mask*  *auth-method*
[*auth-option*]


My pg_hba.conf file contains the following lines (below all of the
instructions):

# TYPEDATABASEUSERIP-ADDRESSMETHOD
hostall all 127.0.0.1/32  md5
hostsameuserall(my.IP.addy.here)md5

The first line allows me to connect from localhost (CF DSN works
wonderfully, etc).  The second, as I understand it, *should* allow my IP to
connect.

Am I completely misunderstanding something here?


Thanks,

Matt


~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266325
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transferring from MS SQL (2000) to PostgreSQL

2007-01-11 Thread Jochem van Dieten

Matt Quackenbush wrote:

 host   *database*  *user*  *CIDR-address*  *auth-method*  [*auth-option*]
 host   *database*  *user*  *IP-address*  *IP-mask* *auth-method* 
 [*auth-option*]

 My pg_hba.conf file contains the following lines (below all of the
 instructions):
 
 # TYPEDATABASEUSERIP-ADDRESSMETHOD
 hostall all 127.0.0.1/32  md5
 hostsameuserall(my.IP.addy.here)md5
 
 The first line allows me to connect from localhost (CF DSN works
 wonderfully, etc).  The second, as I understand it, *should* allow my IP to
 connect.

How did you write your IP address? Did you add the /32 to use *CIDR-address* 
notation?

Jochem

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266330
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

Jochem,

Thank you.  That was the problem.  I didn't realize that it was needed.
Remote connection up and running; time to play!  :-)


Matt


On 1/11/07, Jochem van Dieten [EMAIL PROTECTED] wrote:

 How did you write your IP address? Did you add the /32 to use
 *CIDR-address* notation?

 Jochem



~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266335
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

2007-01-11 Thread David Livingston

If you just want a single IP to be able to connect the format is  
555.555.555.555/0 .
Obviously you replace the 555 stuff with your IP.

Dave


On Jan 11, 2007, at 4:14 PM, Matt Quackenbush wrote:

 Jochem,

 Thank you.  That was the problem.  I didn't realize that it was  
 needed.
 Remote connection up and running; time to play!  :-)


 Matt


 On 1/11/07, Jochem van Dieten [EMAIL PROTECTED] wrote:

 How did you write your IP address? Did you add the /32 to use
 *CIDR-address* notation?

 Jochem



 

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266350
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

I have found this software, but it appears to be brand new.  Has anyone had
any experience with it?  Or with something similar that you would recommend?

http://www.download3k.com/Business-Finance/Database-Management/Download-MS-SQL-Server-PostgreSQL-Import-Export-Convert-Software.html


~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266171
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Transferring from MS SQL (2000) to PostgreSQL

2007-01-10 Thread Joshua Cyr

I have used Aqua Data Studio for just that task. (mysql and mssql to
postgre).  I had to do it table by table, but I did it for millions of
records with relative ease.  Export to delimited text file or file with
insert statements, then import to the other db.

http://www.aquafold.com/


Joshua Cyr
Savvy Software
866.870.6358
www.besavvy.com

-Original Message-
From: Matt Quackenbush [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 10, 2007 3:50 PM
To: CF-Talk
Subject: Transferring from MS SQL (2000) to PostgreSQL

Hello,

I have a heavily used and ever-growing MS SQL database that I would like to
transfer over to PostgreSQL.  It is currently 143mb (plus about 67mb in log
files).  The database is relatively basic, in that it does not currently use
any stored procedures, and the tables makeup is pretty sql-generic.  In a
nutshell, I am looking for info and advice on how to best handle the
transfer.


Thanks in advance,

Matt




~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266172
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transferring from MS SQL (2000) to PostgreSQL

Joshua Cyr wrote:
 I have used Aqua Data Studio for just that task. (mysql and mssql to
 postgre).  I had to do it table by table, but I did it for millions of
 records with relative ease.  Export to delimited text file or file with
 insert statements, then import to the other db.
 
 http://www.aquafold.com/
 

I'll +1 Aqua Data Studio...but I don't think you will need to do it 
table by table...if you right click on the database, and go to 
Tools/Script Generator, it will bring up a wizard that will create a 
single file with CREATE statements for all of your tables, views, 
indexes, etc. and INSERT statements for all of your data (basically a 
file like mysqldump or pg_dump creates).  It allows you some 
customization in the export, such as what the statement separator is and 
what to wrap object names in ([] vs ``)...I haven't used it going from 
MS-SQL --- PostgreSQL, but I have gone MS-SQL --- MySQL and the 
created file needed little modification.

Unfortunately they just raised the price

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266176
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Transferring from MS SQL (2000) to PostgreSQL

2007-01-10 Thread Joshua Cyr

Wasn't Aqua Data Studio free at one point?  I swear I didn't buy it... but
could be forgetting.  I don't do the warez thing, so either it was
legitimately free at one point or very inexpensive or I am losing too many
brain cells on WOW.

-Original Message-
From: Jim Wright [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 10, 2007 4:17 PM
To: CF-Talk
Subject: Re: Transferring from MS SQL (2000) to PostgreSQL

Joshua Cyr wrote:
 I have used Aqua Data Studio for just that task. (mysql and mssql to 
 postgre).  I had to do it table by table, but I did it for millions of 
 records with relative ease.  Export to delimited text file or file 
 with insert statements, then import to the other db.

 http://www.aquafold.com/

I'll +1 Aqua Data Studio...but I don't think you will need to do it table by
table...if you right click on the database, and go to Tools/Script
Generator, it will bring up a wizard that will create a single file with
CREATE statements for all of your tables, views, indexes, etc. and INSERT
statements for all of your data (basically a file like mysqldump or pg_dump
creates).  It allows you some customization in the export, such as what the
statement separator is and what to wrap object names in ([] vs ``)...I
haven't used it going from MS-SQL --- PostgreSQL, but I have gone MS-SQL
--- MySQL and the created file needed little modification.

Unfortunately they just raised the price

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266177
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

Joshua Cyr wrote:
 Wasn't Aqua Data Studio free at one point?  I swear I didn't buy it... but
 could be forgetting.  I don't do the warez thing, so either it was
 legitimately free at one point or very inexpensive or I am losing too many
 brain cells on WOW.
 
Prior to the current version 6, it was free for personal use.  The 
previous version (4.7) is still available from their download page.  A 
fully functional/non-expiring copy of version 6 can also be downloaded, 
you just aren't supposed to use it for anything other than evaluation.

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266178
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

Joshua,

Thanks for the pointer and the link.

Jim,

Are you referring to v6 RC1?  That's all I've seen so far on their site.


Thanks,

Matt


On 1/10/07, Jim Wright [EMAIL PROTECTED] wrote:

 Joshua Cyr wrote:
  Wasn't Aqua Data Studio free at one point?  I swear I didn't buy it...
 but
  could be forgetting.  I don't do the warez thing, so either it was
  legitimately free at one point or very inexpensive or I am losing too
 many
  brain cells on WOW.
 
 Prior to the current version 6, it was free for personal use.  The
 previous version (4.7) is still available from their download page.  A
 fully functional/non-expiring copy of version 6 can also be downloaded,
 you just aren't supposed to use it for anything other than evaluation.

 

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266181
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

Matt Quackenbush wrote:
 Jim,
 
 Are you referring to v6 RC1?  That's all I've seen so far on their site.
 

Yes, I believe that is the latest version...but I think the 4.7 version 
also had the export capability I was describing.  I went ahead and 
started using the RC because I wanted to use the new schema diff tool.

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266184
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transferring from MS SQL (2000) to PostgreSQL

2007-01-10 Thread David Livingston

Here is what I have done in the past. Use enterprise manager to
script out the tables to a sql script. Then do a find an replace on
the script to convert the data types and remove all of the MS
specific stuff. Once you have a good generic SQL script run it on
postgres to create all of your tables.
Then on your MS SQL server setup an ODBC connection to the postgres
server. Once you have that setup you can use Enterprise Managers
export (DTS) functions to map the tables and move the data over into
the postgres server. The hardest data types are id fields and dates.
For Id fields set the postgres field to and integer then after the
data is moved over you can flip it to and auto incrementing id. It's
been a while since I have done this so I can't remember what I did
about dates but maybe the odbc drivers have gotten better and will
transfer better.
Hope that helps.
Dave

On Jan 10, 2007, at 2:49 PM, Matt Quackenbush wrote:

Hello,

I have a heavily used and ever-growing MS SQL database that I would
like to
transfer over to PostgreSQL. It is currently 143mb (plus about
67mb in log
files). The database is relatively basic, in that it does not
currently use
any stored procedures, and the tables makeup is pretty sql-
generic. In a
nutshell, I am looking for info and advice on how to best handle the
transfer.

Thanks in advance,

Matt

~|
Create robust enterprise, web RIAs.
Upgrade integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266185
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

Am I correctly understanding what I'm reading on their site that Aqua Data
Studio supports ALL of the listed databases in the same client?  (As opposed
to having a different one for each DB?)


~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266188
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

Matt Quackenbush wrote:
 Am I correctly understanding what I'm reading on their site that Aqua Data
 Studio supports ALL of the listed databases in the same client?  (As opposed
 to having a different one for each DB?)
 
Yes...it is very nice having the same interface for multiple database 
systems (and multiple operating systems).  It was actually SQL 2005 that 
drove me to it...Management Studio pissed me off so much when I first 
started using it, and ADS allowed me to connect to SQL 2000 and 2005 
with an interface that was much more like Enterprise Manager than 
Management Studio.  I've since come around on Management Studio...but 
I'm still more likely to fire up ADS, even with a SQL 2005 database.



~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266192
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transferring from MS SQL (2000) to PostgreSQL

2007-01-10 Thread Jon Clausen

On Jan 10, 2007, at 5:13 PM, Matt Quackenbush wrote:
 Am I correctly understanding what I'm reading on their site that  
 Aqua Data
 Studio supports ALL of the listed databases in the same client?   
 (As opposed
 to having a different one for each DB?)

Yes.  It rocks!

Dave L's instructions are probably the easiest to migrate the data -  
if you can use DTS. If you don't want to use DTS you can set your  
export preferences in Aqua Data Studio to format the query for  
Postgres compatibility.You'll still need to do some tweaking  
especially on date/time formats of the data (most of the time you can  
simply place {} around the timestamps), but if you use Enterprise  
Manager or Studio Express to generate the export script, you'll have  
a lot more to clean up since it generates a bunch of MS-specific  
stuff in the export query.

Some common datatype conversions for your adventure:  :-)
MSSQL   |   Postgres

IDENTITY(1,1)   =   serial
varchar =   character varying
datetime=   timestamp without time zone
int =   integer
text=   text

If you migrate the data using DTS and then change the column types to  
serial later, you may need to run the following script on each table  
to establish the next sequence value for auto-incrementing (With your  
variables inserted for the table variables of course):

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('#table#',  
'#table_id#'), (SELECT max(#table_id#) FROM #table#), true);

Good luck!

Jon





~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266197
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transferring from MS SQL (2000) to PostgreSQL

Sweet (regarding the single interface)!

Jon, I was under the impression that varchar() was still a valid datatype in
Postgre, and would not require changing to 'character varying'.

http://www.postgresql.org/docs/8.2/static/datatype-character.html

Am I mis-reading that?

Dave,

Thanks for the tips!


Thanks,

Matt


On 1/10/07, Jon Clausen [EMAIL PROTECTED] wrote:


Some common datatype conversions for your adventure:  :-)
 MSSQL   |   Postgres
 
 IDENTITY(1,1)   =   serial
 varchar =   character varying
 datetime=   timestamp without time zone
 int =   integer
 text=   text

 If you migrate the data using DTS and then change the column types to
 serial later, you may need to run the following script on each table
 to establish the next sequence value for auto-incrementing (With your
 variables inserted for the table variables of course):

 SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('#table#',
 '#table_id#'), (SELECT max(#table_id#) FROM #table#), true);

 Good luck!

 Jon





 

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266203
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transferring from MS SQL (2000) to PostgreSQL

2007-01-10 Thread Jon Clausen

No you're right - now days.  I've had some issues with older drivers  
where they would throw a syntax error on me so I always use character  
varying with Pg  just to be on the safe side.

On Jan 10, 2007, at 6:10 PM, Matt Quackenbush wrote:

 Sweet (regarding the single interface)!

 Jon, I was under the impression that varchar() was still a valid  
 datatype in
 Postgre, and would not require changing to 'character varying'.

 http://www.postgresql.org/docs/8.2/static/datatype-character.html

 Am I mis-reading that?

 Dave,

 Thanks for the tips!


 Thanks,

 Matt


 On 1/10/07, Jon Clausen [EMAIL PROTECTED] wrote:


 Some common datatype conversions for your adventure:  :-)
 MSSQL   |   Postgres
 
 IDENTITY(1,1)   =   serial
 varchar =   character varying
 datetime=   timestamp without time zone
 int =   integer
 text=   text

 If you migrate the data using DTS and then change the column types to
 serial later, you may need to run the following script on each table
 to establish the next sequence value for auto-incrementing (With your
 variables inserted for the table variables of course):

 SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('#table#',
 '#table_id#'), (SELECT max(#table_id#) FROM #table#), true);

 Good luck!

 Jon







 

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266206
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transferring from MS SQL (2000) to PostgreSQL

2007-01-10 Thread David Livingston

I concur with Jon. I'd use Character Varying.

Dave

On Jan 10, 2007, at 5:31 PM, Jon Clausen wrote:

 No you're right - now days.  I've had some issues with older drivers
 where they would throw a syntax error on me so I always use character
 varying with Pg  just to be on the safe side.

 On Jan 10, 2007, at 6:10 PM, Matt Quackenbush wrote:

 Sweet (regarding the single interface)!

 Jon, I was under the impression that varchar() was still a valid
 datatype in
 Postgre, and would not require changing to 'character varying'.

 http://www.postgresql.org/docs/8.2/static/datatype-character.html

 Am I mis-reading that?

 Dave,

 Thanks for the tips!


 Thanks,

 Matt


 On 1/10/07, Jon Clausen [EMAIL PROTECTED] wrote:


 Some common datatype conversions for your adventure:  :-)
 MSSQL   |   Postgres
 
 IDENTITY(1,1)   =   serial
 varchar =   character varying
 datetime=   timestamp without time zone
 int =   integer
 text=   text

 If you migrate the data using DTS and then change the column  
 types to
 serial later, you may need to run the following script on each table
 to establish the next sequence value for auto-incrementing (With  
 your
 variables inserted for the table variables of course):

 SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence 
 ('#table#',
 '#table_id#'), (SELECT max(#table_id#) FROM #table#), true);

 Good luck!

 Jon









 

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266207
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Transferring from MS SQL (2000) to PostgreSQL

2007-01-10 Thread Dave Watts

 Am I correctly understanding what I'm reading on their site 
 that Aqua Data Studio supports ALL of the listed databases in 
 the same client?  (As opposed to having a different one for each DB?)

Sure. It's a JDBC client, just like CF. I doubt it provides GUI management
functionality found in most platform-specific management tools, though.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!


~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266214
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transferring from MS SQL (2000) to PostgreSQL