Re: Miigration Wizard ignores not nulls & defau

Thanks Roman. Do you mind if I incorporate this into the pgAdmin docs?

Regards, Dave.

> -----Original Message-----
> From: Roman Fail [mailto:rfail@posportal.com]
> Sent: 20 May 2003 15:41
> To: Corneliu GALEV
> Cc: pgadmin-support@postgresql.org; Roman Fail
> Subject: Re: [pgadmin-support] Miigration Wizard ignores not
> nulls & defau
>
>
> I have a little HOWTO on migrating binary data from MSSQL
> when I moved a huge table over.  I offered it up on
> techdocs.postgresql.org about 6 months ago, but no one
> responded.   For anything not involving binary data, I found
> the pgAdmin2 Migration Wizard to be my favorite tool!
>
>
>
> 1. On MSSQL, create a User-Defined Function that can convert
> each binary byte into an escaped-octal byte (which is the
> only way PostgreSQL can read in binary data).  Use the
> following statement as a guideline:
>
>
>
> CREATE FUNCTION [dbo].[vbin2oct] (@vbin VARBINARY(255))
>
> RETURNS VARCHAR(1275)
>
> AS
>
> BEGIN
>
> /* Converts a VARBINARY to a string of escaped octal values
>
> suitable for import into PostgreSQL.  Written for MS SQL Server 2000.
>
> This should also work on SQL 7.0, but the return length must
> be shortened
>
> to 255 characters (which means the input varbinary should be
> no more than
>
> 50 characters, since every input character results in 5 for
> the output).
>
> 07 JAN 2003 - Roman Fail (rfail@posportal.com)
>
> */
>
> DECLARE @p1 INTEGER, @p8 INTEGER, @p64 INTEGER,
>
>                   @int INTEGER, @i INTEGER, @len INTEGER,
>
>                   @octal VARCHAR(1275)
>
> SET ANSI_PADDING OFF
>
> IF @len IS NULL
>
>     SELECT @len = LEN(@vbin)
>
> SET @i = 1
>
> SET @octal = ''
>
> WHILE @i <= @len
>
>   BEGIN
>
>     SELECT @int = substring(@vbin, @i, 1)
>
>     SET @p64 = FLOOR(@int/64)
>
>     SET @p8 = (@int - (@p64 * 64))/8
>
>     SET @p1 = @int % 8
>
>     SET @octal = @octal + '\\'+
>
>            CAST(@p64 AS CHAR(1)) +
>
>            CAST(@p8 AS CHAR(1)) +
>
>            CAST(@p1 AS CHAR(1))
>
>     SET @i = @i + 1
>
>   END
>
> SET ANSI_PADDING ON
>
> RETURN(RTRIM(LTRIM(@octal)))
>
> END
>
>
>
> IMPORTANT NOTE: If the last byte of a binary field is equal
> to the hexadecimal value '20' (same as decimal '32' or ASCII
> 'space'), the MSSQL LEN() function will truncate it (so no
> octal character is created).  The SET ANSI_PADDING OFF
> statement is essential to prevent this problem.
>
>
>
> 2. Start bcp for the table.  My server took about 1.5 hours
> to generate 24 million rows (must enter the actual sa
> password & server name in correct spot).
>
>        a. Execute bcp_batchdetail.bat on MSSQL server, which
> contains only one command:
>
>         bcp "SELECT primaryKeyID,
> dbo.vbin2oct(myVarBinaryField), otherField FROM myTable"
> queryout c:\tabledata.txt -k -U "sa" -P "<sapassword>" -c -S
> <myservername>
>
>
>
>
>
> 3. Use FTP to transfer tabledata.txt from MSSQL server to PG
> server.  One of the servers must have an FTP server daemon
> installed.  Make sure to use FTP ASCII mode (this will strip
> Carriage Return characters from the file, which psql COPY
> won't recognize).  You can use other methods to transfer the
> file, but the CR's must be removed somehow.  Unfortunately,
> after much research I could not get 'bcp' to output JUST line
> feeds at the end of each line.
>
>
>
> 4. Using pgAdmin2, psql, or the tool of your choice create
> the table definition on the PostgreSQL server.  If you have
> an MSSQL IDENTITY column in your table, you will first need
> to manually CREATE SEQUENCE and set the appropriate start
> value for the sequence, then include a DEFAULT
> nextval(<mysequence>) for the column.  For example:
>
>    CREATE SEQUENCE mytable_primarykeyid_key
>     START 24492190
>     INCREMENT 1
>     MAXVALUE 9223372036854775807
>     MINVALUE 1
>     CACHE 1;
>
>
>
>   CREATE TABLE mytable (
>     primarykeyid integer DEFAULT
> nextval('"mytable_primarykeyid_key"'::text) NOT NULL,
>     myvarbinaryfield bytea,
>     otherfield character varying(16)
>
>    );
>
>
> (Note the proper use of single/double quotes, and all names
> have been folded to lower case)
>
>
>
> 5. On the PostgreSQL server, run psql and issue the command:
>
> COPY batchdetail FROM '/home/postgres/tabledata.txt';
>
>
>
> For me, this took 45 minutes for 24 million rows - about a 10GB file.
>
>
>
> 6. Re-create indexes, foreign keys, and permissions manually.
>
>
>
> 7. Run VACUUM FULL VERBOSE and then ANALYZE to get the house in order.
>
>
>
> 8. Test database queries using pgAdminII or psql command
> line.  Use EXPLAIN ANALYZE for execution plans to be displayed.
>
>
>
> Hope this helps you out.
>
>
>
> Roman Fail
>
> POS Portal, Inc.
>
>
>
>     -----Original Message-----
>     From: Corneliu GALEV [mailto:Corneliu.Galev@cefin.com]
>     Sent: Mon 5/19/2003 11:16 PM
>     To: Roger Gordon
>     Cc: pgadmin-support@postgresql.org
>     Subject: Re: [pgadmin-support] Miigration Wizard
> ignores not nulls & defau
>
>
>     I also noticed that varbinary fields are not migrated
> properly. I maped varbinary to bytea and fileds values are
> not migrated at all (I'm migrating from MSSQL7)
>
>         -----Original Message-----
>         From: Roger Gordon [mailto:Roger@emojo.com]
>         Sent: Monday, 19 May 2003 12:46
>         To: pgadmin-support@postgresql.org
>         Subject: [pgadmin-support] Miigration Wizard
> ignores not nulls & default values
>
>
>
>         Hi,
>
>
>
>         Noticed 2 problems migrating from SQLServer
> 2000 using pgAdmin 1.4.12:
>
>         1.    NOT NULLs other than the primary key
> code migrate over as NULLs
>         2.    Default values are not migrated
>
>         I'm writing a script to fix these errors, but
> any advice would be most appreciated....
>
>
>
>         Thanks,
>
>         Roger
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>