X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B14B55289C for ; Tue, 9 Aug 2005 10:45:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09200-04 for ; Tue, 9 Aug 2005 13:44:58 +0000 (GMT) Received: from vms040pub.verizon.net (vms040pub.verizon.net [206.46.252.40]) by svr1.postgresql.org (Postfix) with ESMTP id 9600E52806 for ; Tue, 9 Aug 2005 10:44:57 -0300 (ADT) Received: from jfradkin ([66.15.127.235]) by vms040.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IKY00LK4JJ1GL0D@vms040.mailsrvcs.net> for pgsql-admin@postgresql.org; Tue, 09 Aug 2005 08:45:02 -0500 (CDT) Date: Tue, 09 Aug 2005 09:46:49 -0400 From: "Joel Fradkin" Subject: Re: SQL Server 2000 to PostgreSQL 8.0.3 In-reply-to: <42F8A369.60402@segrestfarms.com> To: , Message-id: <000501c59ce8$caf88ec0$797ba8c0@jfradkin> MIME-version: 1.0 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.878 tagged_above=0 required=5 tests=AWL, MSGID_DOLLARS X-Spam-Level: ** X-Archive-Number: 200508/104 X-Sequence-Number: 18423 Only thing you may run into moving data is field types. I had to convert my bit fields to binary and my datestammp to timestamp. I would just be sure the field types are supported or you may have to do = a search replace on the text file created by aquastudio. Although when I used aqua I exported using aqua and used pgadmin to do = the inserts so I had to also change their go to a ; (which was actually an option on the export page I did not see at first). Best of luck with the conversion. Also here is my cheat sheet of stuff I looked at often while converting = it involves some sql differences. The isnull is now coalesce, and date diff is a just math, so I had to reverse the order of my date diffs and convert to date - date date_part('epoch',date-date) returns in secs so /60 to_char( i.opendate,'yyyy') as year, to_char( i.opendate,'q') as quarter, to_char( i.opendate,'MM') as month, to_char( i.opendate,'D') as weekday, to_char( i.opendate,'WW') as week, to_char( i.opendate,'HH24:MI') as time coalesce(to_char(tblresponsesection.feedbackcompleteddate,'Mon DD = YYYY'), 'not completed') as completed Left 105 chars = substring(tblquestions.question,1,105) as question, Right 5 chars substring(c.casenum::text, 1, 11), length("substring"(c.casenum::text, 1, 11))-5, 5) coalesce(c.apprehdate, c.opendate) as apprehdate, to_char( coalesce(c.apprehdate,c.opendate),'yyyy') = as year, to_char( coalesce(c.apprehdate, c.opendate),'q') = as quarter, to_char( coalesce(c.apprehdate, c.opendate),'MM') = as month, to_char( coalesce(c.apprehdate, c.opendate),'D') = as weekday, to_char( coalesce(c.apprehdate, c.opendate),'WW') = as week, to_char( coalesce(c.apprehdate, = c.opendate),'HH24:MI') as time, ~* is a =3D that is not case sensitive same as =3D is mssql ilike is a case insensitive like current_timestamp SET ENABLE_SEQSCAN=3DFALSE; You will want to get used to doing=20 explain analyze in front of your sql to see what you may need to modify = or key. I would subscribe to the performance list sql list to see how folks = do stuff. I had to de-normalize some of my larger tables sets and now my performance is as good as my SQL server was. Ex: explain analyze select * from tblcase where clientnum =3D 'WAZ' = select * from tblcase where clientnum =3D 'WAZ' Will display the joins etc involved in getting the data. Joel Fradkin =20 -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of = josh@segrestfarms.com Sent: Tuesday, August 09, 2005 8:37 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] SQL Server 2000 to PostgreSQL 8.0.3 Greetings. I am having difficulty moving a SQL Server 2000 database over to=20 PostgreSQL 8.0.3. In SQL Server, I am performing a backup of the database I want to move. = I save that backup on my Desktop. I am then opening up PGAdminIII and attempting to perform a Restore of=20 the database saved on my Desktop. The first step I take is to create a=20 new database, MyDB. Next, I highlight my new database and then select=20 Restore from the Tools menu. The Restore Database window pops up and I=20 Browse to the database backup I've saved on my Desktop. After selecting = the backup, the 'OK' button stays grayed and I am unable to proceed. Any ideas as to how I can work around this? Thanks in advance. Josh in Tampa ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings