table scripted by Redgate with FILLFACTOR has syntax problem
rgribble
Posts: 88
I am using the work item to script out creation scripts for objects to store in source control. I notice that on a table that has an index or primarykey with a FillFactor, the generated creation script encloses the fillfactor in brackets which causes a syntax error if you actually try and run the script.
The brackets around the (FILLFACTOR = 90) cause a syntax error when the script is run.
For the moment i have got around it by setting the options to ignore fill factors and index padding, however ultimately i would like to have as little ignore options set as possible.[/b]
ALTER TABLE [dbo].[Venue] ADD CONSTRAINT [PK_Venue] PRIMARY KEY CLUSTERED ([VenueId]) WITH (FILLFACTOR = 90) ON [PRIMARY]
The brackets around the (FILLFACTOR = 90) cause a syntax error when the script is run.
For the moment i have got around it by setting the options to ignore fill factors and index padding, however ultimately i would like to have as little ignore options set as possible.[/b]
Comments
Is this an issue caused by migrating from SQL 2005 back to SQL 2000? SQL Compare simply gets the SQL by examining the database's system tables, therefore you'd assume if the syntax was incorrect, the server should not have accepted it, you'd assume.
When i script tables using SQLDMO or EnterpriseManager i get the correct syntax, but when scripted by Redgate Toolkit (or incidentally if ido a scan with SQL Compare and look in the SQL script section at the bottom), the FILLFACTOR statement has brackets around it.
The one thing i can think of, is something i came across recently, in that _something_ was setting all fill factors to 90%, even though i did not have any specified in the scripts.
I tracked it down to a maintenance plan our DBA had running on the server - one of the options to do with re-indexing on the maintenance plan gives radio buttons to choose between "set all to x%" or "leave each as is". He had the "set all to 10%", which then hardset every index in the database to a fillfactor of 90, which is the default anywya. But this was the difference between an index using the default of 90 but NOT scripting it out vs having it hardset to 90 and thus it then gets scripted out.
Ive since fixed the maintenance plan and will be removing any hardset fillfactors from our scripts... they are in some scripts currently as any object that had been changed and rescripted out, after a maintenance plan had run obviously then had the fillfactor set to 90 in it.
I guess my point is though, that no matter who/what is responsible for setting the fillfactor, SQLDMO scripts the statement out correctly, and redgate tools seemingly dont.
I will check the DDL creation statement in the system tables to see what it shows about one of these items... but im wondering if it may be something in the redgate engine adding brackets when it shouldn't!!
I'll check it out at this end, but I think if this relatively common SQL was throwing this error when it's generated by SQL Compare I would have heard a lot more complaints about it!
I guess this could be the difference between w.ScriptObject() and w.CreateFromDifferences() perhaps?
It's only because i am using redgate toolkit to script creation scripts and then storing these in source control, then building them into an empty staging database that i may have come across this syntax error in the creation SQL.
I tried looking around in syscomments etc but couldnt manage to find anything containing creation script for indexes. Any hints where i can look?
You have a point. The sysindexes table doesn't contain the original DDL, the information is constructed from the meta information and turned into a script by SQL Compare. Whenever you script a table that has a clustered index, SQL Compare's work.ScriptObject() method created the fill factor in paranthesis.
I think you have managed to convince me that this is a bug that needs to be fixed!
I forgot to mention... if you use the IngoreFillFactor or Options.Default options in the options you specify to the ScriptObject method, the offinding bit of code will not appear. You can use that as a temporary workaround.