Re: 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