× Heads up!

Aqua Data Studio / nhilam

Follow
IDE for Relational Databases
×
westers reported 2015-04-10T19:59:14Z  · tariqrahiman last modified 2015-04-17T21:29:57Z

Aquadata Studio incorrectly scripts MSSQL tables with data compression and table partitioning


customer request
Priority Critical
Complexity Unknown
Component Tools - Schema Script Gen
Version 16.0

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
)
 
1 attachment

Issue #13234

Closed
Fixed
Resolved 2015-04-14T01:12:42Z
 
 
Completion
No due date
Fixed Build v16.0.3-16
No time estimate

About AquaClusters Privacy Policy Support Version - 19.0.2-4 AquaFold, Inc Copyright © 2007-2017