Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CREATE TABLE command error in SSMS vs EM


CREATE TABLE command error in SSMS vs EM

Author
Message
DavidL
DavidL
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 756
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



Jon Russell
Jon Russell
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 1166
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



DavidL
DavidL
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 756
Ahhh. Of course. That would make likely be it... Never thought of that. thx.



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6770 Visits: 1908
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
DavidL
DavidL
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 756
That was my next question. thx.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search