Aquadata studio's schema script generator produced invalid scripts for MSSQL tables that are partitioned and have data compression enabled.
NOTE: In scripts below I've redacted the table structs to reduce size of post.
Assume the following partition function and scheme for both aquadata generated script and enterprise mgr generated scripts.
IF NOT EXISTS (select 'exists' from sys.partition_functions where name = 'PARTFUNC_DISTRICT')
CREATE PARTITION FUNCTION "PARTFUNC_DISTRICT" (varchar) AS RANGE LEFT FOR VALUES (N'01',N'02')
GO
IF NOT EXISTS (select 'exists' from sys.partition_schemes where name = 'PARTSCHEME_DISTRICT_STAGING')
CREATE PARTITION SCHEME "PARTSCHEME_DISTRICT_STAGING" AS PARTITION "PARTFUNC_DISTRICT" TO ("K12INTEL_ST_DATA","K12INTEL_ST_DATA")
GO
Here's what Aquadata generated (invalid):
CREATE TABLE "STAGING"."MYTABLE" (
"STAGE_CREATEDATE" datetime NOT NULL,
"STAGE_MODIFYDATE" datetime NOT NULL,
"STAGE_DELETEFLAG" bit NOT NULL,
"STAGE_SCHOOL_YEAR" varchar(9) NOT NULL,
"STAGE_SOURCE" varchar(20) NOT NULL,
"STAGE_SCOPE" varchar(20) NOT NULL,
"YEAR" char(4) NOT NULL,
CONSTRAINT "PK_MYTABLE" PRIMARY KEY NONCLUSTERED("YEAR","STAGE_SCHOOL_YEAR","STAGE_SOURCE","STAGE_SCOPE") ON "PARTSCHEME_DISTRICT_STAGING" ("STAGE_SCOPE")
WITH FILLFACTOR = 100
)
WITH (DATA_COMPRESSION = PAGE)
GO
The error returned is:
>[Error] Script lines: 1-13 -------------------------
Incorrect syntax near the keyword 'WITH'.
Msg: 156, Level: 15, State: 1, Procedure: , Line: 11
Here's what MSSQL generated (valid):
CREATE TABLE [STAGING].[MYTABLE](
[STAGE_CREATEDATE] [datetime] NOT NULL,
[STAGE_MODIFYDATE] [datetime] NOT NULL,
[STAGE_DELETEFLAG] [bit] NOT NULL,
[STAGE_SCHOOL_YEAR] [varchar](9) NOT NULL,
[STAGE_SOURCE] [varchar](20) NOT NULL,
[STAGE_SCOPE] [varchar](20) NOT NULL,
[YEAR] [char](4) NOT NULL,
CONSTRAINT [PK_MYTABLE] PRIMARY KEY NONCLUSTERED
(
[YEAR] ASC,
[STAGE_SCHOOL_YEAR] ASC,
[STAGE_SOURCE] ASC,
[STAGE_SCOPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE) ON [PARTSCHEME_DISTRICT_STAGING]([STAGE_SCOPE])
) ON [PARTSCHEME_DISTRICT_STAGING]([STAGE_SCOPE])
WITH
(
DATA_COMPRESSION = PAGE
)