SQL Clone
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 771
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1343 Visits: 1177
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 771
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 (39K reputation)

Group: Moderators
Points: 39428 Visits: 1917
For those looking how to change this, in SQL Server Management Studio:

Tools | Options | Scripting

K. Brian Kelley
@‌kbriankelley
DavidL
DavidL
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 771
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