Not able to generate Table DDL for more than one table

[varun-dhawan]

Hi Team
I'm trying to generate Postgres compatible code for below SQL Server create table script. The input has two tables, however the output generates DDL only for first table. Not sure if this is an issue or I'm missing something. Please help / advise:

SQL-Server-Input-File
`### CREATE TABLE [dbo].[AWBuildVersion](
[SystemInformationID] [tinyint] IDENTITY(1,1) NOT NULL,
[Database Version] nvarchar NOT NULL,
[VersionDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_AWBuildVersion_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_AWBuildVersion_SystemInformationID] PRIMARY KEY CLUSTERED
([SystemInformationID] ASC));

CREATE TABLE [dbo].[DatabaseLog](
[DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
[PostTime] [datetime] NOT NULL,
[DatabaseUser] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[Schema] [sysname] NULL,
[Object] [sysname] NULL,
[TSQL] nvarchar NOT NULL,
[XmlEvent] [xml] NOT NULL,
[SRC_I] [int] NULL,
[ProtocolTypeID] [int] NOT NULL DEFAULT ('9'),
CONSTRAINT [PK_DatabaseLog_DatabaseLogID_ProtocolTypeID] PRIMARY KEY CLUSTERED
([DatabaseLogID] ASC,[ProtocolTypeID] ASC));`

Postgres Output Script
`\set ON_ERROR_STOP
\set ECHO all
BEGIN;

CREATE TABLE "public"."awbuildversion"(
"systeminformationid" smallint NOT NULL,
"database version" varchar(25) NOT NULL,
"versiondate" timestamp NOT NULL,
"modifieddate" timestamp NOT NULL);

COMMIT;
`

• Varun

[marco44]

Please attach the file, copying and pasting it will destroy spaces, indentation…

And please attach the complete SQL dump from SQL Server, not an edited one.

Regards

[varun-dhawan]

output_after_script.txt
output_before_script.txt
output_unsure_script.txt

Here's what I'm trying to do:

1. Trying to convert following CREATE SCHEMA IF NOT EXISTS #7 SQL Server tables to PostgreSQL format
   <adwork2014_7tbl_ddl.sql>
2. Using this command
   sqlserver2pgsql.pl -f mssql_tbl_cerate.sql -b output_before_script -a output_after_script -u output_unsure_script
3. It is ONLY converting the 1st table and ignoring remaining Use of uninitialized value in substitution (s///) at ./sqlserver2pgsql/sqlserver2pgsql.pl line 876. #6 table DDL. Refer the output files

adwork2014_7tbl_ddl.txt

[marco44]

It's the first time I see primary constraints in this form. How did you produce this dump ?

[varun-dhawan]

Hi, I simply scripted out all the tables for [AdventureWorks2014] sample database using SQL Server Management Studio. Here's the steps --> https://technet.microsoft.com/en-us/library/ms178078(v=sql.105).aspx

[marco44]

Did you use the exact procedure from sqlserver2pgsql's doc ?

[varun-dhawan]

yes! below's the command:

sqlserver2pgsql.pl -f mssql_tbl_cerate.sql -b output_before_script -a output_after_script -u output_unsure_script

[marco44]

No, I'm talking about the steps to get the sql dump from SQL Server:

To get this SQL Dump, follow this procedure, in SQL Server's management interface:

• Under SQL Server Management Studio, Right click on the database you want to export
• Select Tasks/Generate Scripts
• Click "Next" on the welcome screen (if it hasn't already been desactivated)
• Select your database
• In the list of things you can export, just change "Script Indexes" from False to True, then "Next"
• Select Tables then "Next"
• Select the tables you want to export (or select all), then "Next"
• Script to file, choose a filename, then "Next"
• Select unicode encoding (who knows…, maybe someone has put accents in objects names, or in comments)
• Finish

[varun-dhawan]

AdventureWorks2014_tbl_ddl.txt

Sure, I've scripted out the DDL following the exact same steps. Attached the SQL Dump file to this thread <AdventureWorks2014_tbl_ddl.txt>

[marco44]

There are two things problematic in this dump: the dbo.whatever types: these must be user defined data types, but they aren't in the dump. There is not that much I can do about it. You'd better correct them yourself in the source dump.

There are also in memory tables. Those don't exist in PostgreSQL, and the dump from SQL Server hasn't the same formatting as for normal tables. There is not a lot I can do for these either without rewriting a big part of the tool… which I won't for now :)

So it's the same for those in-memory tables… remove them from the dump and deal with them manually. Sorry about that, it's the best I can do for now :(

[varun-dhawan]

Thanks for the advise. Yes while debugging further into AdventureWork DB, I could see multiple user defined data-types.
So a an alternate, I took a simpler app DB that is using table with pre-defined data-types only. Next using the PERL script I was able to generate PostgreSQL compatible DDL (output_before_script). It also have lot of rows logged in (output_after_script), mostly related to adding PRIMARY KEY constraint and CREATE SEQUENCE. So far it is looking good.