October 24, 2007 at 11:25 am
In ss2k I would often create new tables by scripting an existing table to clipboard in QA, then alter column names, indexes etc. as needed and execute the command.
I am now using SSMS as the admin tool to the same ss2k db. When I script an existing table to a new query window as a CREATE TABLE script, it by default adds the following after the column definitions, etc.:
WITH PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 90 ON [PRIMARY]
Note: When I create the script from within QA, only the last line (fill factor) is included.
Trying to run the create table script as automatically generated by ssms will throw an error (something undefined like error near "(" line 30). The script will run if everything is commented out except the Fillfactor line thusly:
WITH --PAD_INDEX = OFF,
--STATISTICS_NORECOMPUTE = OFF,
--IGNORE_DUP_KEY = OFF,
--ALLOW_ROW_LOCKS = ON,
--ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 90 ON [PRIMARY]
I tried to run the create table script with only one of the above options enabled (commenting out all the others), and got specific error messages saying "Statistics_norecompute is invalid in a create table command" or some such. The same error for each option I tried to enable. The only one that will work is the fillfactor.
So, what gives? Anyone have any ideas? Why does the default script fail in ssms? TIA
October 24, 2007 at 1:29 pm
David,
I will have to confirm this, but off the top of my head I believe Management Studio will generate script for SQL 20005, even if you are scripting a SQL 2000 object. In fact, it appears some of those options in your script are for SQL2005.
Jon
October 24, 2007 at 1:42 pm
Ahhh. Of course. That would make likely be it... Never thought of that. thx.
October 24, 2007 at 5:29 pm
For those looking how to change this, in SQL Server Management Studio:
Tools | Options | Scripting
K. Brian Kelley
@kbriankelley
October 25, 2007 at 8:50 am
That was my next question. thx.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply