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(at)posportal(dot)com]
> Sent: 20 May 2003 15:41
> To: Corneliu GALEV
> Cc: pgadmin-support(at)postgresql(dot)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(at)posportal(dot)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(dot)Galev(at)cefin(dot)com]
> Sent: Mon 5/19/2003 11:16 PM
> To: Roger Gordon
> Cc: pgadmin-support(at)postgresql(dot)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(at)emojo(dot)com]
> Sent: Monday, 19 May 2003 12:46
> To: pgadmin-support(at)postgresql(dot)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(at)postgresql(dot)org)
>