Import data from MS SQL Server 2014 to Postgresql 9.6 using dbi-link and fdw (error: utf-8/uft-16)

On 10/11/16 10:34, Juliano wrote:

Hi,

I'm trying to import some data from a MS SQL Server 2014 sequential
database to Postgresql using dbi-link.

Have you tried the foreign data wrapper for MS SQL Server? It's here:

https://wiki.postgresql.org/wiki/Foreign_data_wrappers#Specific_SQL_Database_Wrappers

Not something I've done, but it may be worth a try.

Ray.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I think that ETL utilities will be the right choice. Try Pentaho's Data
Integration tool. It is Java base.

On Nov 10, 2016 6:45 PM, "Raymond O'Donnell" <rod@iol.ie> wrote:

I'd recommend Talend, they have an open source edition that we use. Very
powerful and reliable.

Tim Clarke

On Thu, 2016-11-10 at 05:34 -0500, Juliano wrote:

Hi,
 
I'm trying to import some data from a MS SQL Server 2014 sequential
database to Postgresql using dbi-link.
 
Postgresql 9.6 encoding is utf-8 and does not support utf-16 but, I
need to transfer this data to postgres.
 
I also tried to use tds_fdw version 1.0.8 and the same problem
occurs.
 
ERROR: DB-Library error: DB #: 100, DB Msg: TDS version downgraded to
7.1!, OS #: 0, OS Msg: Success, Level: 1
 
********** Error **********
 
ERROR: DB-Library error: DB #: 100, DB Msg: TDS version downgraded to
7.1!, OS #: 0, OS Msg: Success, Level: 1
SQL state: HV00L
 
Please help me.

The error code HV00L means "unable to create execution".

This appears to have been fixed. See:-

https://github.com/tds-fdw/tds_fdw/issues/83

However, I don't follow "how" the UTF-16 data is being transformed to
UTF-8. I know that I'm thick (at times). If the SQL Server data is in
Simplified Chinese (say), how do you convert it?

Postgres handles some multi-byte codings:-

WIN932 --> SJIS
WIN936 --> GBK
WIN949 --> UHC
WIN950 --> BIG5

So you can run createdb specifying one of those encodings, lc_collate,
etc.

HTH,

Rob

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I updated odbc drivers and it worked.

Thanks everyone

Regards,
Juliano

Sent with [ProtonMail](https://protonmail.com) Secure Email.

-------- Original Message --------
Subject: Re: [GENERAL] Import data from MS SQL Server 2014 to Postgresql 9.6 using dbi-link and fdw (error: utf-8/uft-16)
Local Time: November 10, 2016 1:31 PM
UTC Time: November 10, 2016 1:31 PM
From: floriparob@gmail.com
To: Juliano <jplinux@protonmail.com>, pgsql-general@postgresql.org <pgsql-general@postgresql.org>

On Thu, 2016-11-10 at 05:34 -0500, Juliano wrote:

Hi,

I'm trying to import some data from a MS SQL Server 2014 sequential
database to Postgresql using dbi-link.

Postgresql 9.6 encoding is utf-8 and does not support utf-16 but, I
need to transfer this data to postgres.

I also tried to use tds_fdw version 1.0.8 and the same problem
occurs.

ERROR: DB-Library error: DB #: 100, DB Msg: TDS version downgraded to
7.1!, OS #: 0, OS Msg: Success, Level: 1

********** Error **********

ERROR: DB-Library error: DB #: 100, DB Msg: TDS version downgraded to
7.1!, OS #: 0, OS Msg: Success, Level: 1
SQL state: HV00L

Please help me.

The error code HV00L means "unable to create execution".

This appears to have been fixed. See:-

https://github.com/tds-fdw/tds_fdw/issues/83

However, I don't follow "how" the UTF-16 data is being transformed to
UTF-8. I know that I'm thick (at times). If the SQL Server data is in
Simplified Chinese (say), how do you convert it?

Postgres handles some multi-byte codings:-

WIN932 --> SJIS
WIN936 --> GBK
WIN949 --> UHC
WIN950 --> BIG5

So you can run createdb specifying one of those encodings, lc_collate,
etc.

HTH,

Rob