Comments for How to Migrate from Microsoft SQL Server to PostgreSQL

[sethjensen1]

Comments for https://www.endpointdev.com/blog/2019/01/migrate-from-sql-server-to-postgresql/ By Selvakumar Arumugam

To enter a comment:

1. Log in to GitHub
2. Leave a comment on this issue.

[yasinyazici34]

Hi

What is the version of sqlserver and postgres. Because ı faced with a bug. My sql server 2016 and postgre v10

Cannot determine the PostgreSQL's datatype corresponding to public.Name. This is a bug at sqlserver2pgsql.pl line 319 main::convert_type('public.Name', undef, 'Name', 'Department', undef, 'HumanResources') called at sqlserver2pgsql.pl line 1321 main::add_column_to_table('HumanResources', 'Department', 'Name', 'dbo', 'Name', undef, undef, 'NOT NULL') called at sqlserver2pgsql.pl line 1445 main::parse_dump() called at sqlserver2pgsql.pl line 3103

[Seng666]

Hello, This is my first time using Pentaho Data Integration. I'm currently encountering the issue similar to ERROR: column "user_id" of relation "user" does not exist. I see on the insert statement that there is a double quotes on one of the column. How do you exactly resolve that? Thanks

[selvait90]

Hello @Seng666 ,

We have faced this issue when executing a query in Postgres database which is having integer column wrapped with double quotes. For Postgres, the double quotes must be removed from the integer column though the query was working well in SQL Server.

Thanks

[Seng666]

Hi @selvait90 , thank you for your response. I think I have figured out the cause of the error. Pentaho Data Integration inserts double quotes on queries that have column names that are also Postgres reserved keywords (TYPE, VALUE, TIMESTAMP). As a quick fix, I just renamed the columns in the SQL server to use lowercase letters (I just restored it from a backup file). Instead of manually updating all the column names in the SQL server side, is there a faster/better way for that, like an option in Data Integration?

Thanks

[ashirafumiiro]

Thanks a lot. It worked so well though I had to get some additional information from the perl script guide.

[limex]

Hi, thanks for the guide. You didn't cover the migration of stored procedures. Can you comment on that? BR

[selvait90]

thank you @limex. The stored procedures can be migrated similar to functions.

[CrispinOkello]

Hi selvait90, Thank you for your blog How to Migrate from Microsoft SQL Server to PostgreSQL which is by the way very informative and helpful. I have only one question though ,if a table has been partitioned in Microsoft SQL Server, will PostgreSQL recognize that this is a partitioned table since the two have a different way of partitioning?

[gismappingGIT]

Hello - Thank you very much for your blog post. I am new to a lot of these environments outside of Microsoft SQL.

Can you tell me after creating the tables.sql in sql, where does it go in order to be read by the perl script?

Thank you in advance, Mary

[1441998]

Hi,

There are some SQL functions that are not converted into PostgreSQL functions and those are mentioned in unsure.sql file.

Due to this, I am facing an error while importing data.

Kindly guide me on how I can resolve it.

Thanks, awaiting your response impatiently.

[selvait90]

@gismappingGIT

1. Exporting Database from SQL Server

2. On Object Explorer right click database you want to export listed in Databases node.

3. Select Tasks then select Export Data-tier Application

4. Click Export Settings and You can find the path details here to find the tables.sql

https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard?view=sql-server-ver15#start-the-sql-server-import-and-export-wizard-from-sql-server-management-studio-ssms

[selvait90]

Hi @1441998 ,

The functions migration is complex to convert from SQL Server to PostgreSQL in automated way [There are chances for some tools by now]. I would recommend to rewrite the function in PostgreSQL manually since I followed that approach for the content in unsure.sql.

Thanks

[1441998]

Hi,Thank you for your response.What do you suggest change functions manually in unsure.sql file or tables.sql file?After that how I can implement changes?Awaiting your response.-------- Original message --------From: Selvakumar Arumugam @.>Date: Wed, Feb 8, 2023, 6:36 PMTo: EndPointCorp/end-point-blog @.>Cc: 1441998 @.>, Mention @.>Subject: Re: [EndPointCorp/end-point-blog] Comments for How to Migrate from Microsoft SQL Server to PostgreSQL (#1493) Hi @1441998 , The functions migration is complex to convert from SQL Server to PostgreSQL in automated way [There are chances for some tools by now]. I would recommend to rewrite the function in PostgreSQL manually since I followed that approach for the content in unsure.sql. Thanks

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: @.***>

[selvait90]

@1441998 The manual approach is understanding SQL Server functions and rewriting as PostgreSQL functions by having knowledge on both SQL Server & PostgreSQL functions syntaxes and concepts.

[santana-jorge]

Hi, Thanks for the guide. My SQL server database has schemas. In the "Pentaho Data Integration" step I noticed that I can only select tables. I do not know how I can specify the scheme. Could you please guide me?

Thank you in advance. Cheers

[sjothili]

Hi, I am getting this error while establishing the connection to MS SQL server db (version - 2019) from PDI ( version - pdi-ce-9.4.0.0-343.zip installed on SUSE LINUX server ). Please recommend us the right driver to be installed to make the successful connection. Appreciate this help!

Java home is set to: export JAVA_HOME=/home/username/openlogic-openjdk-21.0.3+1-linux-x64 export PATH=$JAVA_HOME/bin:$PATH

Error seen in UI: Caused by: org.pentaho.di.core.exception.KettleDatabaseException: Connection failed. Verify all connection parameters and confirm that the appropriate driver is installed. Network error IOException: Connection refused

at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:605) at org.pentaho.di.core.database.Database.normalConnect(Database.java:481) ... 51 more Caused by: java.sql.SQLException: Network error IOException: Connection refused at net.sourceforge.jtds.jdbc.JtdsConnection.(JtdsConnection.java:436) at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:191) at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:589) ... 52 more Caused by: java.net.ConnectException: Connection refused at java.base/sun.nio.ch.Net.connect0(Native Method) at java.base/sun.nio.ch.Net.connect(Net.java:589) at java.base/sun.nio.ch.Net.connect(Net.java:578) at java.base/sun.nio.ch.NioSocketImpl.connect(NioSocketImpl.java:583) at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:327) at java.base/java.net.Socket.connect(Socket.java:751) at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:288) at net.sourceforge.jtds.jdbc.SharedSocket.(SharedSocket.java:251) at net.sourceforge.jtds.jdbc.JtdsConnection.(JtdsConnection.java:331) ... 56 more