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

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

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

Postby AdamY » Fri Mar 15, 2013 10:11 pm

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:
Code: Select all
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?
AdamY
 
Posts: 41
Joined: Fri Oct 15, 2010 8:24 pm

Postby Brian Donahue » Tue Mar 19, 2013 12:06 pm

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
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

v6.6.0.347 did not resolve the problem

Postby AdamY » Tue Mar 19, 2013 5:46 pm

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.
AdamY
 
Posts: 41
Joined: Fri Oct 15, 2010 8:24 pm

Postby Brian Donahue » Thu Mar 21, 2013 1:34 pm

Here is the definition for the type that I successfully packaged... maybe yours differs somehow. But generally this should work in Packager.
Code: Select all
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50),
ID int PRIMARY KEY,
CostRate INT );
GO
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Follow-up

Postby AdamY » Thu Mar 21, 2013 5:36 pm

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:
Code: Select all
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.
AdamY
 
Posts: 41
Joined: Fri Oct 15, 2010 8:24 pm

Postby Brian Donahue » Fri Mar 22, 2013 3:02 pm

Sorry to say again, I can't reproduce the same behavior. Either with ignore fillfactor on or off.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

So that's it?

Postby AdamY » Fri Mar 22, 2013 7:10 pm

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:
Image
Last edited by AdamY on Wed Feb 26, 2014 5:37 am, edited 1 time in total.
AdamY
 
Posts: 41
Joined: Fri Oct 15, 2010 8:24 pm

Postby eddie davis » Mon Mar 25, 2013 4:31 pm

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
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 942
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software


Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 0 guests