Re: Miigration Wizard ignores not nulls & defau - Mailing list pgadmin-support

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.orgabout 6 months ago, but no one responded.   For anything not involving binary data, I found the
pgAdmin2Migration 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
onlyway 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
statementis 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'trecognize).  You can use other methods to transfer the file, but the CR's must be removed somehow.  Unfortunately,
aftermuch 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
anMSSQL IDENTITY column in your table, you will first need to manually CREATE SEQUENCE and set the appropriate start
valuefor 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
notmigrated 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