Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CREATE TABLE command error in SSMS vs EM Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2007 11:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 9:56 AM
Points: 145, Visits: 593
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



Post #414557
Posted Wednesday, October 24, 2007 1:29 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 6, 2014 11:34 AM
Points: 327, Visits: 1,014
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



Post #414598
Posted Wednesday, October 24, 2007 1:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 9:56 AM
Points: 145, Visits: 593
Ahhh. Of course. That would make likely be it... Never thought of that. thx.


Post #414604
Posted Wednesday, October 24, 2007 5:29 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:50 AM
Points: 6,624, Visits: 1,874
For those looking how to change this, in SQL Server Management Studio:

Tools | Options | Scripting



K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #414694
Posted Thursday, October 25, 2007 8:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 9:56 AM
Points: 145, Visits: 593
That was my next question. thx.


Post #415002
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse