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

AdamYAdamY Posts: 55 Bronze 3
edited March 25, 2013 11:31AM in SQL Packager Previous Versions
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?
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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
    · Share on Twitter
  • AdamYAdamY Posts: 55 Bronze 3
    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.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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
    · Share on Twitter
  • AdamYAdamY Posts: 55 Bronze 3
    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.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Sorry to say again, I can't reproduce the same behavior. Either with ignore fillfactor on or off.
    · Share on Twitter
  • AdamYAdamY Posts: 55 Bronze 3
    edited February 25, 2014 11:37PM
    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:
    FnYhAtT.png
    · Share on Twitter
  • Eddie DEddie D Posts: 1,802 Rose Gold 5
    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
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
Sign In or Register to comment.