Skip site navigation (1) Skip section navigation (2)

Re: Miigration Wizard ignores not nulls & defau

From: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
To: "Roman Fail" <rfail(at)posportal(dot)com>,"Corneliu GALEV" <Corneliu(dot)Galev(at)cefin(dot)com>
Cc: <pgadmin-support(at)postgresql(dot)org>
Subject: Re: Miigration Wizard ignores not nulls & defau
Date: 2003-05-20 14:59:05
Message-ID: 03AF4E498C591348A42FC93DEA9661B825A19E@mail.vale-housing.co.uk (view raw or flat)
Thread:
Lists: pgadmin-support
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)
> 

pgadmin-support by date

Next:From: Roman FailDate: 2003-05-20 15:07:15
Subject: Re: Miigration Wizard ignores not nulls & defau
Previous:From: Roman FailDate: 2003-05-20 14:40:54
Subject: Re: Miigration Wizard ignores not nulls & defau

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group