Error: 'fillfactor' is not a recognized CREATE TYPE option.

AdamY

SQL Packager 6.4.0.8, SQL 2008 R2

I have 2 User-Defined Table Types in my database. When SQL Packager builds a script, it includes this statement for the Primary Key in the script:

WITH (FILLFACTOR=90)

But that is not a valid option. Thus, when I run the package against a SQL server I get this error:

'fillfactor' is not a recognized CREATE TYPE option.

So I have to manually edit the script first, then use SQL Packager to build the exe. I don't want to set SQL Packager to ignore Fillfactor because I don't want Fillfactor ignored on my table indexes.
Is this a bug with SQL Packager or have I done something wrong?

Brian Donahue

Hi Adam,

I'm sorry SQL Packager is giving you problems. Can you please try updating to the latest patch, and see if that helps?

ftp://support.red-gate.com/patches/SQL_ ... .0.347.zip

AdamY

Thank you for the help. I installed v6.6.0.347 and the Fillfactor problem for User-Definied Table Types still occurs.

If it helps at all - this also used to be a problem in SQL Source Control, but it was fixed. I don't remember when or in what version it was fixed.

Brian Donahue

Here is the definition for the type that I successfully packaged... maybe yours differs somehow. But generally this should work in Packager.

CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50),
ID int PRIMARY KEY,
CostRate INT );
GO

AdamY

When I run SQL Packager to generate a script or package, it generates code that has WITH (FILLFACTOR=90) included. I have to manually remove the WITH (FILLFACTOR=90) code first and then package that script.

Here is definition of one of the types I'm having the problem with:

CREATE TYPE [LockNeedList] AS TABLE
(
[NeedKey] [varchar](9) NOT NULL,
[NeedType] [varchar](6) NOT NULL,
PRIMARY KEY CLUSTERED ([NeedKey] ASC)
)

Again, the code generated by SQL Packager has WITH (FILLFACTOR=90) added on.

Brian Donahue

Sorry to say again, I can't reproduce the same behavior. Either with ignore fillfactor on or off.

AdamY

SQL Source Control used to have this same issue and does not now - so it seems it was a bug they found and corrected.

Should I send a DB backup or is that a waste of time? I won't take the time to put one together if Red Gate isn't going to look at it.

Just so you know I'm not crazy:

Eddie D

Hi AdamY

Sorry that you have encountered a problem.

I can confirm that this is a bug, I was able to reproduce the error using both V6.4.0.8 and V6.6.0.347.

The bug reference number is SPA-601, sadly I cannot provide an ETA as to when this bug will be eventually fixed. I will be maintaining a "watch" on the bug report I submitted so I can be alerted on any changes to the Bug Report and provide an update.

The workaround I can advise is to continue editing the script to remove the WITH (FILLFACTOR=90) before building the exe file.

Many Thanks
Eddie