target a database schema

[ghost]

Hello,

Amazing script you have written.

I would like to target a single postgres database with multiple schemas. So that I can load say database "A" from MS SQL into a master postres database containing multiple schemas (in this case into schema A). And then the same for B, C ... So that I have a master postgres database with schema A, B, C. Representing various databases I have loaded from various MS SQL databases.

I notice the target is a database, how would I specify a database and a schema here? -pd : postgresql database

I did run into some other issues (which I would love help with), but the one above is the most important

1. some columns in the MS SQL database are called "table". Yeah a reserved keyword, and amazingly bad form to name as a column.
2. During load I get the following messages 2014/03/27 01:46:54 - Table output.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : An error occurred intialising this step: 2014/03/27 01:46:54 - Table output.0 - Couldn't execute SQL: TRUNCATE TABLE "public".CountyDim 2014/03/27 01:46:54 - Table output.0 - 2014/03/27 01:46:54 - Table output.0 - ERROR: relation "public.countydim" does not exist
3. And then the script.sql I export from MS SQL does not like lines of the following form EXEC sys.sp_addextendedproperty @name=N'Dictionary', @value=N'Mast table of IDNs. Integrated Delivery Network, A network of facilities and providers that work together to offer a continuum of care to a specific geographic area or market. ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IDNDim' rem GO

Thanks for the help!

[marco44]

For the schema issue, there is not a lot you can do in the current status of sqlserver2pgsql. The problem is that the source sqlserver database may also have multiple schemas. I guess that your source database only has a dbo schema, and you'd like to remap it ? I have to give this a little bit of thinking, but remapping schemas shouldn't be hard to do. Anyway, if all your databases have only a dbo schema, you may simply import into public for each of them, then rename public and create a new public schema.

For the other issues:

• keywords aren't protected. I'll correct that asap
• I don't know where the 2nd error comes from. Does the table really not exist ? Can you try to find why kettle cannot find the table ?
• For the third issue, could you provide me with the full SQL Server schema dump ? (the html formatting may have changed spacing for instance). You can send it to cousinmarc at gmail dot com (I won't publish it anywhere of course).

Regards.

[marco44]

I pushed the functionnality to remap schemas. I didn't add anything to ignore them, on purpose, as I seem to remember that the export GUI from SQL Server already provides this option.

Now the SQL dump you provided parses correctly. I still have some messages about sequences (starting with 0). I hope I'll have time to solve this tomorrow.

I didn't work with protected keywords. I can't find one in the example you sent me.

[marco44]

Finally, the sequence problem was easy to solve. So I've had time to do this too. Only the reserved keywords left

[ghost]

Hello,

Oh yes I renamed the column name from "table" to "tablex" in mssql and then exported it. Around line 98 in the script.

Evan

marco44 wrote:

I pushed the functionnality to remap schemas. I didn't add anything to ignore them, on purpose, as I seem to remember that the export GUI from SQL Server already provides this option.

Now the SQL dump you provided parses correctly. I still have some messages about sequences (starting with 0). I hope I'll have time to solve this tomorrow.

I didn't work with protected keywords. I can't find one in the example you sent me.

— Reply to this email directly or view it on GitHub https://github.com/dalibo/sqlserver2pgsql/issues/2#issuecomment-38851071.

Evan C. Smith, MS, MD Evan.Smith.MS.MD@gmail.com Cell: 781-879-8736

[ghost]

Line 151 in the file I sent you CREATE TABLE [dbo].[ACOMetaDataFact]( [Dailytimedimid] [int] NOT NULL, [TableX] varchar NOT NULL, [Metric] varchar NOT NULL, [Value] [decimal](10, 4) NULL, CONSTRAINT [PK_HosptialRepositoryMetaData] PRIMARY KEY CLUSTERED ( [Dailytimedimid] ASC, [TableX] ASC, [Metric] ASC

I renamed "table" to "tablex" in ms sql studio hoping to get it to work. Naturally it did not help :-).

Thank you for all the help!!!

Evan

marco44 wrote:

I pushed the functionnality to remap schemas. I didn't add anything to ignore them, on purpose, as I seem to remember that the export GUI from SQL Server already provides this option.

Now the SQL dump you provided parses correctly. I still have some messages about sequences (starting with 0). I hope I'll have time to solve this tomorrow.

I didn't work with protected keywords. I can't find one in the example you sent me.

— Reply to this email directly or view it on GitHub https://github.com/dalibo/sqlserver2pgsql/issues/2#issuecomment-38851071.

Evan C. Smith, MS, MD Evan.Smith.MS.MD@gmail.com Cell: 781-879-8736

[ghost]

Hello,

So I ran the following through 4 scripts I exported from 4 different MS SQL databases.

Not sure what to make of the messages ... (the "file kettle exists" is just my script trying to make a kettle directory, not a problem).

And it looks like these are just warnings, though the uninitialized value might be an issue.

Thanks, Evan

d:\sqlserver2pgsql-master\sqlserver2pgsql.pl -f /port/%dbname%/%dbscript%.sql -b before.sql -a after.sql -u unsure.sql

d:\port>echo off Use of uninitialized value $relabel_schemas in split at D:\sqlserver2pgsql-master\sqlserver2pgsql.pl line 279. Use of uninitialized value $relabel_schemas in split at D:\sqlserver2pgsql-master\sqlserver2pgsql.pl line 279. A subdirectory or file kettle already exists. Use of uninitialized value $relabel_schemas in split at D:\sqlserver2pgsql-master\sqlserver2pgsql.pl line 279. Use of uninitialized value $relabel_schemas in split at D:\sqlserver2pgsql-master\sqlserver2pgsql.pl line 279. A subdirectory or file kettle already exists. Use of uninitialized value $relabel_schemas in split at D:\sqlserver2pgsql-master\sqlserver2pgsql.pl line 279. Warning: This index (dbo.DecisionResourceDerivedPopulationRates.INX_DecisionResourceDerivedPopulationRatesPopulationSc hemeId_PYERVPCPC) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationRatesPublished.INX_DecisionResourcePopulationRatesPublishedPopulati onSchemeId_PublishedVersionDateStamp_PYECRECERVPRPCPC) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemes.IX_DecisionResourcePopulationSchemesBegPopulationPublishYea r_EndPopulationPublishYear) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemes.IX_DecisionResourcePopulationSchemesDateLastModified_LastMo difiedBy_EPIMetric_TopicID) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemes.IX_DecisionResourcePopulationSchemesTopicIdActiveFlag) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemes.IX_DecisionResourcePopulationSchemes__TopicIdHasError) has so me include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemesPublished.INX_DecisionResourcePopulationSchemesPublishedInDi seaseRankings_ActiveFlag_BegPopPubYear_EndPopPubYear_PST_EPIMetric_SS) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemesPublished.INX_DecisionResourcePopulationSchemesPublished__InTo plineTotals_ActiveFlag_BegPopPubYear_EndPopPubYear_PST_SS) has some include columns. This isn't supported in PostgreSQL.

     The columns in the INCLUDE clause have been ignored.

Warning: This index (dbo.DecisionResourcePopulationSchemesPublished.INX_DecisionResourcePopulationSchemesPublishedPopu lationGroupId_ActiveFlag_BegPopPubYear_EndPopPubYear_PST_PGO) has some include columns. This isn't supported in PostgreS QL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemesPublished.IX_DecisionResourcePopulationSchemesPublishedBegPo pulationPublishYear_EndPopulationPublishYear) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemesPublished.IXNDecisionResourcePopulationSchemesPublishedDat eLastModified_LastModifiedBy_EPIMetric_TopicID) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourceTopicsPublished.IXNDecisionResourceTopicsPublished_TopicId_PublishedVersionDa testamp_TherapyAreaID_FirstDisplayYear_LastDisplayYear) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourceTopicsPublished.IXN__DecisionResourceTopicsPublished_TopicId_PublishedVersionDa testamp_TopicName_FirstPublishYear_LastPublishYear) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourceTopicsPublished.IXNDecisionResourceTopicsPublished_TopicId_PublishedVersionDa testampGeneralPopulationDatasetIDFirstDisplayYearLastDisplayYear) has some include columns. This isn't supported in Post greSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DRPVPCompanyTherapyAreaDrugSales.IXN_DRPVPCompanyTherapyAreaDrugSales_CompanyID_TherapyAreaID_S ales) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DRPVPCompanyTherapyAreaDrugSales.IXN_DRPVPCompanyTherapyAreaDrugSales_Year) has some include co lumns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DRPVPDrugProfileSales.IDX_DRPVPDrugProfileSales1) has some include columns. This isn't supporte d in PostgreSQL. The columns in the INCLUDE clause have been ignored. Use of uninitialized value $relabel_schemas in split at D:\sqlserver2pgsql-master\sqlserver2pgsql.pl line 279. Warning: This index (dbo.DecisionResourceDerivedPopulationRates.INX_DecisionResourceDerivedPopulationRatesPopulationSc hemeId_PYERVPCPC) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationRatesPublished.INX_DecisionResourcePopulationRatesPublishedPopulati onSchemeId_PublishedVersionDateStamp_PYECRECERVPRPCPC) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemes.IX_DecisionResourcePopulationSchemesBegPopulationPublishYea r_EndPopulationPublishYear) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemes.IX_DecisionResourcePopulationSchemesDateLastModified_LastMo difiedBy_EPIMetric_TopicID) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemes.IX_DecisionResourcePopulationSchemesTopicIdActiveFlag) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemes.IX_DecisionResourcePopulationSchemes__TopicIdHasError) has so me include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemesPublished.INX_DecisionResourcePopulationSchemesPublishedInDi seaseRankings_ActiveFlag_BegPopPubYear_EndPopPubYear_PST_EPIMetric_SS) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemesPublished.INX_DecisionResourcePopulationSchemesPublished__InTo plineTotals_ActiveFlag_BegPopPubYear_EndPopPubYear_PST_SS) has some include columns. This isn't supported in PostgreSQL.

     The columns in the INCLUDE clause have been ignored.

Warning: This index (dbo.DecisionResourcePopulationSchemesPublished.INX_DecisionResourcePopulationSchemesPublishedPopu lationGroupId_ActiveFlag_BegPopPubYear_EndPopPubYear_PST_PGO) has some include columns. This isn't supported in PostgreS QL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemesPublished.IX_DecisionResourcePopulationSchemesPublishedBegPo pulationPublishYear_EndPopulationPublishYear) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourcePopulationSchemesPublished.IXNDecisionResourcePopulationSchemesPublishedDat eLastModified_LastModifiedBy_EPIMetric_TopicID) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourceTopicsPublished.IXNDecisionResourceTopicsPublished_TopicId_PublishedVersionDa testamp_TherapyAreaID_FirstDisplayYear_LastDisplayYear) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourceTopicsPublished.IXN__DecisionResourceTopicsPublished_TopicId_PublishedVersionDa testamp_TopicName_FirstPublishYear_LastPublishYear) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DecisionResourceTopicsPublished.IXNDecisionResourceTopicsPublished_TopicId_PublishedVersionDa testampGeneralPopulationDatasetIDFirstDisplayYearLastDisplayYear) has some include columns. This isn't supported in Post greSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DRPVPCompanyTherapyAreaDrugSales.IXN_DRPVPCompanyTherapyAreaDrugSales_CompanyID_TherapyAreaID_S ales) has some include columns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DRPVPCompanyTherapyAreaDrugSales.IXN_DRPVPCompanyTherapyAreaDrugSales_Year) has some include co lumns. This isn't supported in PostgreSQL. The columns in the INCLUDE clause have been ignored. Warning: This index (dbo.DRPVPDrugProfileSales.IDX_DRPVPDrugProfileSales1) has some include columns. This isn't supporte d in PostgreSQL. The columns in the INCLUDE clause have been ignored. A subdirectory or file kettle already exists. Use of uninitialized value $relabel_schemas in split at D:\sqlserver2pgsql-master\sqlserver2pgsql.pl line 279. Warning: I had to rename index CompanyEnrollmentValidationFact.idxStatus to idxStatus2pgi because of naming conflicts in source schema dbo Use of uninitialized value $relabel_schemas in split at D:\sqlserver2pgsql-master\sqlserver2pgsql.pl line 279. Warning: I had to rename index CompanyEnrollmentValidationFact.idxStatus to idxStatus2pgi because of naming conflicts in source schema dbo A subdirectory or file kettle already exists.

marco44 wrote:

Finally, the sequence problem was easy to solve. So I've had time to do this too. Only the reserved keywords left

— Reply to this email directly or view it on GitHub https://github.com/dalibo/sqlserver2pgsql/issues/2#issuecomment-38851451.

Evan C. Smith, MS, MD Evan.Smith.MS.MD@gmail.com Cell: 781-879-8736

[ghost]

Hello,

So my port of the databases is looking darn good. I have done 2 of the 4 and am just looking them over.

Oddly enough the primary key's are not coming over from any of the tables. I wonder if when I generate the script from MS SQL design studio I am suppose to set something?

Evan

marco44 wrote:

Finally, the sequence problem was easy to solve. So I've had time to do this too. Only the reserved keywords left

— Reply to this email directly or view it on GitHub https://github.com/dalibo/sqlserver2pgsql/issues/2#issuecomment-38851451.

Evan C. Smith, MS, MD Evan.Smith.MS.MD@gmail.com Cell: 781-879-8736

[marco44]

For the message with the warnings: you have covering indexes (indexes with supplemental columns that are not sorted, but still stored in the index). This isn't supported in PostgreSQL. So the script prints a warning about this.

There is also the «Use of uninitialized value $relabel_schemas in split at». That's obviously a bug in the code I added yesterday. Can you give me the relabel option you used ?

About the primary keys : are they missing from the original SQL Server dump, or from the converted one (they should be in the "after" file) ?

I'll look into the reserved keyword issue today if I can.

[ghost]

Hello,

Cool, about indexes. I did not know this.

I am not using any relabel option, so must be the default. Below is the dos script for the calls I am making

I am not sure I call the after.sql, all I do is in the script below. I will look and see where primary keys are setup today.

Thanks, Evan

echo on rem the name of the ms sql database and the script exported by hand that represents that db set dbname=%1 set dbscript=%2

rem 0. rem By hand you export scrit.sql rem see readme at d:/sqlserver2pgsql-master/README.md echo We assume you have generated by by microsoft design studio %dbscript%.sql using generate script

rem 1. cd \port rm -fr \port\%dbname% mkdir \port\%dbname% cd \port\%dbname% cp \port\%dbscript%.sql \port\%dbname% d:\sqlserver2pgsql-master\sqlserver2pgsql.pl -f /port/%dbname%/%dbscript%.sql -b before.sql -a after.sql -u unsure.sql call d:\sqlserver2pgsql-master\sqlserver2pgsql.pl -f /port/%dbname%/%dbscript%.sql -b before.sql -a after.sql -u unsure.sql

rem 2. cd \port\%dbname% call d:\sqlserver2pgsql-master\sqlserver2pgsql.pl -b before.sql -a after.sql -u unsure.sql -k /port/%dbname%/kettle -sd %dbname% -sh localhost -sp 1433 -su root -sw password -pd mydatabase -ph localhost -pp 5432 -pu root -pw password -f %dbscript%.sql

rem 3. rem This will prompt for the username so run from command prompt cd \port\%dbname% psql -U root -d shadow -f before.sql rem when prompted for password enter "password", or I just bound the environement variable PGPASSWORD and then there is no prompt

goto DONE

rem 4. cd d:/pdi-ce-5.0.1.A-stable/data-integration/ kitchen.bat -file=/port/%dbname%/kettle/migration.kjb -level=detailed

:DONE cd \port

marco44 wrote:

For the message with the warnings: you have covering indexes (indexes with supplemental columns that are not sorted, but still stored in the index). This isn't supported in PostgreSQL. So the script prints a warning about this.

There is also the «Use of uninitialized value $relabel_schemas in split at». That's obviously a bug in the code I added yesterday. Can you give me the relabel option you used ?

About the primary keys : are they missing from the original SQL Server dump, or from the converted one (they should be in the "after" file) ?

I'll look into the reserved keyword issue today if I can.

— Reply to this email directly or view it on GitHub https://github.com/dalibo/sqlserver2pgsql/issues/2#issuecomment-38895325.

Evan C. Smith, MS, MD Evan.Smith.MS.MD@gmail.com Cell: 781-879-8736

[ghost]

Hello,

I am getting primary keys now and everything working much better.

My script was not calling after.sql. I will send you an updated dos copy shortly. Feel free to use it and post it. Please do not give me credit though.

Thanks Evan

marco44 wrote:

For the message with the warnings: you have covering indexes (indexes with supplemental columns that are not sorted, but still stored in the index). This isn't supported in PostgreSQL. So the script prints a warning about this.

There is also the «Use of uninitialized value $relabel_schemas in split at». That's obviously a bug in the code I added yesterday. Can you give me the relabel option you used ?

About the primary keys : are they missing from the original SQL Server dump, or from the converted one (they should be in the "after" file) ?

I'll look into the reserved keyword issue today if I can.

— Reply to this email directly or view it on GitHub https://github.com/dalibo/sqlserver2pgsql/issues/2#issuecomment-38895325.

Evan C. Smith, MS, MD Evan.Smith.MS.MD@gmail.com Cell: 781-879-8736

[ghost]

Hello,

Here is the script I am using with good results on windows.

Evan

echo on rem the name of the ms sql database and the script exported by hand that represents that db set dbname=%1 set dbscript=%2 rem cd \shadow-etl\mssql2psql

rem cleanup postgres from any past attempts call dropdb -U root %dbname% call createdb -U root %dbname%

rem 0. rem By hand you export scrit.sql rem see readme at d:/shadow-etl/sqlserver2pgsql-master/README.md echo We assume you have generated by by microsoft design studio %dbscript%.sql using generate script

rem 1. cd \shadow-etl\mssql2psql rm -fr \shadow-etl\mssql2psql\%dbname% mkdir \shadow-etl\mssql2psql\%dbname% cd \shadow-etl\mssql2psql\%dbname% cp \shadow-etl\mssql2psql\%dbscript%.sql \shadow-etl\mssql2psql\%dbname%

call d:\shadow-etl\sqlserver2pgsql-master\sqlserver2pgsql.pl -f /shadow-etl/mssql2psql/%dbname%/%dbscript%.sql -b before.sql -a after.sql -u unsure.sql

rem 2. cd \shadow-etl\mssql2psql\%dbname% rem warning using same password in ms sql as in postgres call d:\shadow-etl\sqlserver2pgsql-master\sqlserver2pgsql.pl -b before.sql -a after.sql -u unsure.sql -k /shadow-etl/mssql2psql/%dbname%/kettle -sd %dbname% -sh localhost -sp 1433 -su root -sw %PGPASSWORD% -pd %dbname% -ph localhost -pp 5432 -pu root -pw %PGPASSWORD% -f %dbscript%.sql

rem 3. rem This will prompt for the username so run from command prompt rem UNLESS you bind the environement variable in windows named PGPASSWORD to the postgres password cd \shadow-etl\mssql2psql\%dbname% call psql -U root -d %dbname% -f before.sql

rem 4. cd d:/shadow-etl/pdi-ce-5.0.1.A-stable/data-integration/ call kitchen.bat -file=/shadow-etl/mssql2psql/%dbname%/kettle/migration.kjb -level=detailed

rem 5. rem This will prompt for the username so run from command prompt cd \shadow-etl\mssql2psql\%dbname% call psql -U root -d %dbname% -f after.sql

goto DONE

:DONE cd \shadow-etl\mssql2psql

marco44 wrote:

For the message with the warnings: you have covering indexes (indexes with supplemental columns that are not sorted, but still stored in the index). This isn't supported in PostgreSQL. So the script prints a warning about this.

There is also the «Use of uninitialized value $relabel_schemas in split at». That's obviously a bug in the code I added yesterday. Can you give me the relabel option you used ?

About the primary keys : are they missing from the original SQL Server dump, or from the converted one (they should be in the "after" file) ?

I'll look into the reserved keyword issue today if I can.

— Reply to this email directly or view it on GitHub https://github.com/dalibo/sqlserver2pgsql/issues/2#issuecomment-38895325.

Evan C. Smith, MS, MD Evan.Smith.MS.MD@gmail.com Cell: 781-879-8736

[marco44]

Hi,

I just pushed a new version. It should work with reserved keywords. And can keep the case of identifiers if asked. I couldn't test the kettle part, as I don't have a SQL Server environment right now. Can you give it a try and tell me ?

Regards

[ghost]

Hello,

Sure will try it out this evening. I have a big run going now and will see how that does. This run is using a ms sql that is "local". Next I will try connecting to a remote ms sql instance. My username/password works remotely and is a domain name and user name, hopefully that will work. (so domain\esmith is my username).

I am wondering what would be involved in supporting say MY SQL as a source. Where would I need to change your script or do you have thoughts of doing this?

Also do you have a list of things that do not work with this port from MS SQL to postgres? Would be helpful when I think about what is missing. I have not tried the third script you generate (I use before and after.sql). Is this other script that is generated something I should run? Was not sure what do do with it.

really exciting to have this working so quickly.

Thanks, Evan

marco44 wrote:

Hi,

I just pushed a new version. It should work with reserved keywords. And can keep the case of identifiers if asked. I couldn't test the kettle part, as I don't have a SQL Server environment right now. Can you give it a try and tell me ?

Regards

— Reply to this email directly or view it on GitHub https://github.com/dalibo/sqlserver2pgsql/issues/2#issuecomment-38936000.

Evan C. Smith, MS, MD Evan.Smith.MS.MD@gmail.com Cell: 781-879-8736

[marco44]

Supporting MySQL with this script would be very hard. Data Definition Language is what's most different between all databases. Moreover, there are already plenty of other programs to migrate from MySQL (I never tried one myself, as I'm mostly working on migrations from Oracle and SQL Server). Anyway, you can give a try to pgloader for this, it seems to be quite active these days.

I don't have a list of what will not work. Partitioning for sure, as I still haven't met a SQL Server database with partitioning. I'm not that familier with SQL Server anyway, so I'm just improving the parser as I get new dumps :)

[ghost]

Hello,

Cool. Thanks for the info, will be helpful if someone asks for MySQL, who knows.

My run has "hung" seems some of the names being generated are to long.

Below is the messages

Evan

ALTER TABLE public.UniversalWidgetDerivedPopulationRates ADD CONSTRAINT PKCC_DRDerivedPopRatesSchemeIdPopYearSpec1Val Spec2ValSpec3ValSpec4ValSpec5ValSpec6ValSpec7ValSpec8Val UNIQUE (PopulationSchemeId,PopulationYear,Specificity1Value,Spe cificity2Value,Specificity3Value,Specificity4Value,Specificity5Value,Specificity6Value,Specificity7Value,Specificity8Val ue); psql:after.sql:109: NOTICE: identifier "pkcc_drderivedpoprates__schemeidpopyearspec1valspec2valspec3valspec4valspec5val spec6valspec7valspec8val" will be truncated to "pkcc_drderivedpopratesschemeidpopyearspec1valspec2valspec3val" ALTER TABLE ALTER TABLE public.UniversalWidgetPopulationDirectDescendantRelationshipsPublished ADD CONSTRAINT PKC_UniversalWidgetP opDirDescRelationPublishedParentPopulationSchemeId_ChildPopulationSchemeId_PublishedVersionDatestamp UNIQUE (ParentPop ulationSchemeId,PublishedVersionDatestamp,ChildPopulationSchemeId); psql:after.sql:110: NOTICE: identifier "decisionresourcepopulationdirectdescendantrelationshipspublished" will be trunc ated to "decisionresourcepopulationdirectdescendantrelationshipspublishe" psql:after.sql:110: NOTICE: identifier "pkc_decisionresourcepopdirdescrelationpublished__parentpopulationschemeid_child populationschemeid_publishedversiondatestamp" will be truncated to "pkc_decisionresourcepopdirdescrelationpublishedpar entpopulati" ALTER TABLE ALTER TABLE public.UniversalWidgetPopulationGroupsPublished ADD CONSTRAINT PKC_UniversalWidgetPopulationGroupsPublishe dTopicID_GroupId_PublishedVersionDateStamp UNIQUE (TopicID,PublishedVersionDateStamp,GroupID); psql:after.sql:111: NOTICE: identifier "pkc_decisionresourcepopulationgroupspublished__topicid_groupid_publishedversion datestamp" will be truncated to "pkc_decisionresourcepopulationgroupspublishedtopicidgroupid" ALTER TABLE ALTER TABLE public.UniversalWidgetPopulationRates ADD CONSTRAINT PKCC_DRPopulationRatesPopulationSchemeIdPopulation Year_AllSpecificities UNIQUE (PopulationSchemeId,PopulationYear,EpiRateValue,Specificity1Value,Specificity2Value,Specifi city3Value,Specificity4Value,Specificity5Value,Specificity6Value,Specificity7Value,Specificity8Value); psql:after.sql:112: NOTICE: identifier "pkcc_drpopulationratespopulationschemeidpopulationyear_allspecificities" wi ll be truncated to "pkcc_drpopulationratespopulationschemeidpopulationyear_alls" ALTER TABLE ALTER TABLE public.UniversalWidgetPopulationRatesPublished ADD CONSTRAINT PKCC_DRPopulationRatesPublishedPopulationSc hemeId_PublishedVersionDatestamp_PopulationYear_AllSpecificities UNIQUE (PopulationSchemeId,PublishedVersionDatestamp,Po pulationYear,Specificity1Value,Specificity2Value,Specificity3Value,Specificity4Value,Specificity5Value,Specificity6Value ,Specificity7Value,Specificity8Value); psql:after.sql:113: NOTICE: identifier "pkcc_drpopulationratespublishedpopulationschemeid_publishedversiondatestamp_p opulationyear_allspecificities" will be truncated to "pkcc_drpopulationratespublished__populationschemeid_publishedve"

marco44 wrote:

Supporting MySQL with this script would be very hard. Data Definition Language is what's most different between all databases. Moreover, there are already plenty of other programs to migrate from MySQL (I never tried one myself, as I'm mostly working on migrations from Oracle and SQL Server). Anyway, you can give a try to pgloader for this, it seems to be quite active these days.

I don't have a list of what will not work. Partitioning for sure, as I still haven't met a SQL Server database with partitioning. I'm not that familier with SQL Server anyway, so I'm just improving the parser as I get new dumps :)

— Reply to this email directly or view it on GitHub https://github.com/dalibo/sqlserver2pgsql/issues/2#issuecomment-38949438.

Evan C. Smith, MS, MD Evan.Smith.MS.MD@gmail.com Cell: 781-879-8736

[ghost]

Hello,

So it is the after.sql script that is hanging on the line

ALTER TABLE public.UniversalWidgetPopulationRatesPublished ADD CONSTRAINT PKCC_DRPopulationRatesPublishedPopulationSc hemeId_PublishedVersionDatestamp_PopulationYear_AllSpecificities UNIQUE (PopulationSchemeId,PublishedVersionDatestamp,Po pulationYear,Specificity1Value,Specificity2Value,Specificity3Value,Specificity4Value,Specificity5Value,Specificity6Value ,Specificity7Value,Specificity8Value); psql:after.sql:113: NOTICE: identifier "pkcc_drpopulationratespublishedpopulationschemeid_publishedversiondatestamp_p opulationyear_allspecificities" will be truncated to "pkcc_drpopulationratespublished__populationschemeid_publishedve"

Evan

marco44 wrote:

Supporting MySQL with this script would be very hard. Data Definition Language is what's most different between all databases. Moreover, there are already plenty of other programs to migrate from MySQL (I never tried one myself, as I'm mostly working on migrations from Oracle and SQL Server). Anyway, you can give a try to pgloader for this, it seems to be quite active these days.

I don't have a list of what will not work. Partitioning for sure, as I still haven't met a SQL Server database with partitioning. I'm not that familier with SQL Server anyway, so I'm just improving the parser as I get new dumps :)

— Reply to this email directly or view it on GitHub https://github.com/dalibo/sqlserver2pgsql/issues/2#issuecomment-38949438.

Evan C. Smith, MS, MD Evan.Smith.MS.MD@gmail.com Cell: 781-879-8736

[marco44]

This shouldn't be hanging. It just tells you that the constraint name has been truncated to a shorter length (64 chars). The SQL standard says 128 if I remember correctly, that's one of the places where PG isn't perfectly standards compliant. But it isn't hanging. It is building the constraint. As it is a UNIQUE constraint, it is building an index...

[ghost]

Ah likely it was just working. I will grab you new changes and try again.

Thanks! Evan

marco44 wrote:

This shouldn't be hanging. It just tells you that the constraint name has been truncated to a shorter length (64 chars). The SQL standard says 128 if I remember correctly, that's one of the places where PG isn't perfectly standards compliant. But it isn't hanging. It is building the constraint. As it is a UNIQUE constraint, it is building an index...

— Reply to this email directly or view it on GitHub https://github.com/dalibo/sqlserver2pgsql/issues/2#issuecomment-38950433.

Evan C. Smith, MS, MD Evan.Smith.MS.MD@gmail.com Cell: 781-879-8736

[marco44]

Oh, and by the way, the "unsure" script is what it means: "give it a try, and correct it manually if it fails". It contains things I cannot parse reliably (without rewriting the whole SQL Server query parser, which of course I wont :) )

[ghost]

cool, ok.

fun

Evan

marco44 wrote:

Oh, and by the way, the "unsure" script is what it means: "give it a try, and correct it manually if it fails". It contains things I cannot parse reliably (without rewriting the whole SQL Server query parser, which of course I wont :) )

— Reply to this email directly or view it on GitHub https://github.com/dalibo/sqlserver2pgsql/issues/2#issuecomment-38951076.

[ghost]

Hello,

Downloaded the new current code.

I am running an after.sql script and get the following error ...

ALTER TABLE "public"."momsa" ADD FOREIGN KEY ("fips code") REFERENCES "public"."countydim" ( "fipsstatecountycd"); psql:after.sql:642: ERROR: there is no unique constraint matching given keys for referenced table "countydim"

Evan

marco44 wrote:

Oh, and by the way, the "unsure" script is what it means: "give it a try, and correct it manually if it fails". It contains things I cannot parse reliably (without rewriting the whole SQL Server query parser, which of course I wont :) )

— Reply to this email directly or view it on GitHub https://github.com/dalibo/sqlserver2pgsql/issues/2#issuecomment-38951076.

Evan C. Smith, MS, MD Evan.Smith.MS.MD@gmail.com Cell: 781-879-8736

[marco44]

Can you provide me this SQL script ?

[marco44]

I deleted your message as it contained the script. Anyway, I need SQL Server's script, not the one produced by sqlserver2pgsql. Please sent it to me directly via email.

[ghost]

What is your email again. Thnks

On Mar 28, 2014, at 3:28 PM, marco44 notifications@github.com wrote:

I deleted your message as it contained the script. Anyway, I need SQL Server's script, not the one produced by sqlserver2pgsql. Please sent it to me directly via email.

— Reply to this email directly or view it on GitHub.

[marco44]

That was an ordering problem: the script tried to build foreign keys before some unique indexes. I pushed the correction.