support for MS SQL Server to PostgreSQL

[bharanisvng]

Referring to ora2pg 24.1, i can not see any fixes applied for MS SQLSERVER to postgreSQL conversion using ora2pg.

Referring to old ticket " [https://github.com/darold/ora2pg/issues/1663] ( https://github.com/darold/ora2pg/issues/1663 )", we had issues with ora2pg ver 24.0 and later based on your suggestion, had applied master dev fixes and inspite of applying your latest DEV master fixes, we cannot make progress with our testing on MS SqlServer to PostgreSQL conversion using ora2pg tool.

Any suggestion or hint's, if Ver 24.1 had fixed issues with MS SqlServer to postgreSQL conversion or any future release e.g. ver 24.2 (or) ver 25.0 will fix the MS SQL Server to postgreSQL conversion issue's.

[darold]

I don't know what issues you are talking about if this is a connection problem like in #1668 you should review your settings I can not reproduce with a MS SQL Server 2019:

/home/git/test_ora2pg/mssql-db$ sh export_schema.sh 
[2023-10-04 13:28:52] [========================>] 16/16 tables (100.0%) end of scanning.           
[2023-10-04 13:28:52] [========================>] 6/6 objects types (100.0%) end of objects auditing.               
Running: ora2pg -p -t SEQUENCE -o sequence.sql -b ./schema/sequences -c ./config/ora2pg.conf
[2023-10-04 13:28:52] [========================>] 0/0 sequences (100.0%) end of output.
Running: ora2pg -p -t SEQUENCE_VALUES -o sequence_value.sql -b ./schema/sequence_values -c ./config/ora2pg.conf
[2023-10-04 13:28:52] [========================>] 0/0 sequences (100.0%) end of output.
Running: ora2pg -p -t TABLE -o table.sql -b ./schema/tables -c ./config/ora2pg.conf
[2023-10-04 13:28:52] [========================>] 16/16 tables (100.0%) end of scanning.           
[2023-10-04 13:28:52] [========================>] 16/16 tables (100.0%) end of table export.  
Running: ora2pg -p -t PACKAGE -o package.sql -b ./schema/packages -c ./config/ora2pg.conf
Action type PACKAGES is not available for MSSQL.
Aborting export...
Running: ora2pg -p -t VIEW -o view.sql -b ./schema/views -c ./config/ora2pg.conf
[2023-10-04 13:28:52] [========================>] 5/5 views (100.0%) end of output.                  
Running: ora2pg -p -t GRANT -o grant.sql -b ./schema/grants -c ./config/ora2pg.conf
WARNING: Exporting privilege as non DBA user is not allowed, see USER_GRANT
Running: ora2pg -p -t TRIGGER -o trigger.sql -b ./schema/triggers -c ./config/ora2pg.conf
[2023-10-04 13:28:53] [========================>] 0/0 triggers (100.0%) end of output.
Running: ora2pg -p -t FUNCTION -o function.sql -b ./schema/functions -c ./config/ora2pg.conf
[2023-10-04 13:28:53] [========================>] 0/0 functions (100.0%) end of functions export.
Running: ora2pg -p -t PROCEDURE -o procedure.sql -b ./schema/procedures -c ./config/ora2pg.conf
[2023-10-04 13:28:53] [========================>] 0/0 procedures (100.0%) end of procedures export.
Running: ora2pg -p -t TABLESPACE -o tablespace.sql -b ./schema/tablespaces -c ./config/ora2pg.conf
WARNING: Exporting tablespace as non DBA user is not allowed, see USER_GRANT
Running: ora2pg -p -t PARTITION -o partition.sql -b ./schema/partitions -c ./config/ora2pg.conf
[2023-10-04 13:28:53] [========================>] 0/0 partitions (100.0%) end of output.
Running: ora2pg -p -t TYPE -o type.sql -b ./schema/types -c ./config/ora2pg.conf
[2023-10-04 13:28:54] [========================>] 0/0 types (100.0%) end of output.
Running: ora2pg -p -t MVIEW -o mview.sql -b ./schema/mviews -c ./config/ora2pg.conf
[2023-10-04 13:28:54] [========================>] 0/0 materialized views (100.0%) end of output.
Running: ora2pg -p -t DBLINK -o dblink.sql -b ./schema/dblinks -c ./config/ora2pg.conf
[2023-10-04 13:28:54] [========================>] 0/0 dblink (100.0%) end of output.
Running: ora2pg -p -t SYNONYM -o synonym.sql -b ./schema/synonyms -c ./config/ora2pg.conf
[2023-10-04 13:28:54] [========================>] 0/0 synonyms (100.0%) end of output.
Running: ora2pg -p -t DIRECTORY -o directorie.sql -b ./schema/directories -c ./config/ora2pg.conf
[2023-10-04 13:28:54] [========================>] 0/0 directory (100.0%) end of output.
Running: ora2pg -t PACKAGE -o package.sql -b ./sources/packages -c ./config/ora2pg.conf
Action type PACKAGES is not available for MSSQL.
Aborting export...
Running: ora2pg -t VIEW -o view.sql -b ./sources/views -c ./config/ora2pg.conf
[2023-10-04 13:28:54] [========================>] 5/5 views (100.0%) end of output.                  
Running: ora2pg -t TRIGGER -o trigger.sql -b ./sources/triggers -c ./config/ora2pg.conf
[2023-10-04 13:28:55] [========================>] 0/0 triggers (100.0%) end of output.
Running: ora2pg -t FUNCTION -o function.sql -b ./sources/functions -c ./config/ora2pg.conf
[2023-10-04 13:28:55] [========================>] 0/0 functions (100.0%) end of functions export.
Running: ora2pg -t PROCEDURE -o procedure.sql -b ./sources/procedures -c ./config/ora2pg.conf
[2023-10-04 13:28:55] [========================>] 0/0 procedures (100.0%) end of procedures export.
Running: ora2pg -t PARTITION -o partition.sql -b ./sources/partitions -c ./config/ora2pg.conf
[2023-10-04 13:28:55] [========================>] 0/0 partitions (100.0%) end of output.
Running: ora2pg -t TYPE -o type.sql -b ./sources/types -c ./config/ora2pg.conf
[2023-10-04 13:28:55] [========================>] 0/0 types (100.0%) end of output.
Running: ora2pg -t MVIEW -o mview.sql -b ./sources/mviews -c ./config/ora2pg.conf
[2023-10-04 13:28:55] [========================>] 0/0 materialized views (100.0%) end of output.

To extract data use the following command:

ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf

I use the following DSN:

ORACLE_DSN      dbi:ODBC:driver=msodbcsql18;server=localhost;database=sakila;TrustServerCertificate=Yes

With the following declaration in /etc/odbcinst.ini

[msodbcsql18]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.1.1
UsageCount=1
TrustServerCertificate=Yes
TrustCertificate=Yes

If this is something else, please report the errors.

[bharanisvng]

whether all the bug or issues (specifically for ms sql server to postgresql ) reported in ora2pg ver 24.0 is fixed in ver 24.1 ??

[bharanisvng]

weird issue with the simple command

ora2pg -t COPY -o data.sql -c "c:\0_free_pgm_files\ora2pg-24.1\mss_ora2pg.conf"

refer enclosed document for the pictorial snapshots using MS Sql server 2019 with ora2pg ver 24.1 ver_1_1_bsv_laptop.docx

can you please guide on how to overcome the issue ?

[bharanisvng]

please note , this suggestion already implemented.

https://learn.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver16

[bharanisvng]

output of sqlserver configuration manager

output #1

<img width="414" alt="image" src="https://github.com/darold/ora2pg/assets/126074951/6f44bcad-170a-4948-b0c4-7831eaac02bc">

[err.pdf](https://github.com/darold/ora2pg/files/12830372/err.pdf)

[bharanisvng]

please note : i am not using any of the linux server. all are windows 11 based using my laptop

[bharanisvng]

Darold - any update on my query ?

[darold]

I have never tried MSSQL migration on a Microsoft OS so I can not help much more on this. Perhaps using a Linux distribution will be easier and actually it will be more efficient, on Windows you will not benefit of multiprocess for data export.

[bharanisvng]

understood your input. thanks will attempt will linux distribution favor for MS SQL Server installation

[bharanisvng]

and will update you. will follow your detailed steps on the linux front