The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Wednesday, September 28, 2022 Login
Public

Postgres to SQL Quick Tips 3/4/2022 7:23:55 AM
Very First make sure your working with a great Postgres DBA.

1.  Convert the schema from Postgres to SQL Server
2.  Install ODBC Drivers 
3.  Use SSIS or it's watered down cousin DTSWizard
4.  If really necessary you can drop down to custom code to transfer data.
5.  Edit the ProvidersDescriptors.xml
6.  Out of Memory Reading tuples ?  (add UseDeclareFetch=1 to connect string).

For data type compatibility
https://www.convert-in.com/docs/mss2pgs/types-mapping.htm 
 
For:  sql server odbc postgres column attribute colum_size is not valid providerdescriptors.xml
MaximumLengthColumnNameNumericPrecisionColumnName, and NumericScaleColumnName attribute values to "LENGTH""PRECISION", and "SCALE", respectively.


The column attribute "COLUMN_SIZE" is not valid.

The column attribute "DECIMAL_DIGITS" is not valid.

The column attribute "COLUMN_SIZE" is not valid.


<dtm:ColumnSchemaAttributes
    NameColumnName = "COLUMN_NAME"
    OrdinalPositionColumnName="ORDINAL_POSITION"
    DataTypeColumnName = "TYPE_NAME"
    MaximumLengthColumnName = "COLUMN_SIZE"
    NumericPrecisionColumnName = "COLUMN_SIZE"
    NumericScaleColumnName = "DECIMAL_DIGITS"
    NullableColumnName="NULLABLE"
    NumberOfColumnRestrictions="4"
/>

... to ...

<dtm:ColumnSchemaAttributes
    NameColumnName = "COLUMN_NAME"
    OrdinalPositionColumnName="ORDINAL_POSITION"
    DataTypeColumnName = "TYPE_NAME"
    MaximumLengthColumnName = "LENGTH"
    NumericPrecisionColumnName = "PRECISION"
    NumericScaleColumnName = "SCALE"
    NullableColumnName="NULLABLE"
    NumberOfColumnRestrictions="4"
/>
sql server postgresql out of memory while reading tuples 
"Driver={PostgreSQL Unicode};Server=ip;Port=port;Database=db_name;Uid=user;Pwd=pass;UseDeclareFetch=1"
https://stackoverflow.com/questions/22532149/vba-and-postgresql-connection


Blog Home