Issues with SQL Server migration to PostgreSQL

[srinilakshmi]

Hi Darold,

I am working on migrating SQL Server scripts to PostgreSQL using windows version of Ora2PG with scripts migration (offline due to restrictions in our company).

Here are few things I am having issues with.

1. Since doing scripts migration, cannot use conf file for connectivity, but when I use
   ora2pg -t SHOW_Report --dump_as_json -i "c:\users\myid\input_scrpt.sql" gives me an error saying
   FATAL: bad export type using input file option

2. SQL server Stored procedures with parameters are not migrated to postgre SQL

3. I see foreign key constraints are being migrated. for a fact none of the constraints were converted

Any help for the above is really helpful and I am desperately need help with these.

TIA

[darold]

1. you can not generate a report using an input file, this is the sens of the error message
2. There be a bug here, please send a DDL stored procedure example that is not exported by Ora2Pg.
3. Same I need sources DDL to be able to reproduce the bug.

[srinilakshmi]

Thanks @darold for your response, sending here for the sample DDL for #2 and #3 below. For constraints gave one of them (clustered index, non clustered index, primary key, foreign key, unique keys). Please look into this sooner as we are in the critical path for the POC. Thanks in advance,

CREATE PROCEDURE [Company_GetList]

                @coverageID int = null,

                @companyID int = null

AS

BEGIN

                SET NOCOUNT ON;

       

                SELECT

                                CompanyID,

                                SubindustryID                 

                FROM

                                dbo.Company

                WHERE

                                IsDeleted = 0 and

                                (CoverageID = @coverageID or @coverageID IS NULL)               

                ORDER BY

                                Name

END;

CREATE NONCLUSTERED INDEX [IX_BBGRate] ON [dbo].[BBGRate]

(

                [BBGFieldID] ASC

)

INCLUDE([Rate],[RateDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

GO

[srinilakshmi]

@darold any response here is appreciated.