SQL Server 2005 Generated Create Table Script not working

Question

I'm in the process of creating database scripts for an existing database. I'm trying to use SQL Server to generate the scripts for me, but the generated scripts are failing. I'm using the 'Script Table As'>'CREATE To'>'New Query Editor Window' option. I then change the table and constraint names and execute the script. I'm stumped. Can anyone see the issue here?

I get this error:

Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '('.

The generated SQL:

USE [MyDatabase]
GO
/****** Object:  Table [dbo].[MyTable2]    Script Date: 01/06/2009 14:40:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTable2](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [u_id] [int] NOT NULL,
    [prog_number] [varchar](5) NOT NULL,
    [trans_id] [varchar](50) NULL,
    [code] [varchar](7) NULL,
    [user_num] [char](9) NULL,
    [is_found] [char](9) NULL,
    [status] [char](1) NULL,
    [status2] [char](1) NULL,
    [inserted_timestamp] [datetime] NULL CONSTRAINT [DF_MyTable2_inserted_timestamp]  DEFAULT (getdate()),
    [s_id] [varchar](10) NULL,
    [p_value] [char](4) NULL,
 CONSTRAINT [PK_MyTable2] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

ZenDraken · Accepted Answer · 2009-02-16 17:51:53Z

10

I've got the same error. SQL Sever Management Studio defaults to generate scripts for SQL Server 2005, which are not compatible with 2000.

My solution: In SQL Sever Management Studio, go to Tools>>Options>>Scripting. Under "General Scripting Options, set "Script for server version" to "SQL Server 2000".

answered Feb 16, 2009 at 17:51

ZenDraken

Sign up to request clarification or add additional context in comments.

2 Comments

Flea Over a year ago

This was very helpful! I have SQL 2008 Management Studio hitting a SQL 2000 database and it was generating SQL targeted to 2008. By switching the setting it generated a SQL script targeted for SQL 2000.

Kasaku Over a year ago

This appears to be the better answer as it actually confirms how to solve this issue.

DJ. · Accepted Answer · 2009-01-06 21:56:37Z

9

If your target DB is SQL 2000 then it complains about this line

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

SQL 2000 only supports

WITH FILLFACTOR = 90

So there is probably some compatibility setting you can use to only generate SQL 2000 syntax

answered Jan 6, 2009 at 21:56

DJ.

16.3k3 gold badges45 silver badges46 bronze badges

1 Comment

Spidermain50 Over a year ago

Can you tell me where this setting is located?

Dave Baghdanov · Accepted Answer · 2009-01-06 21:44:07Z

2

I get this exact same problem when i use SQL Server Management Studio 2005 to generate a Create Table script on a SQL Server 2000 database.

answered Jan 6, 2009 at 21:44

Dave Baghdanov

2,3686 gold badges25 silver badges35 bronze badges

1 Comment

JoJo Over a year ago

That was my problem. In SQL Server Managment Studio, just got to Tools/ Options/ Scripting / Script for server version ( I changed this temporarily to SQL Server 2000) and I was able to clone the table.

HLGEM · Accepted Answer · 2009-01-06 21:52:47Z

2

If you are running this on a SQL Server 2000 database I believe it has code in it that SQL Server 2000 does not support. I've never seen a SQL SErver 2000 script that sets up the constraint as part of the table definition.

answered Jan 6, 2009 at 21:52

HLGEM

97k15 gold badges120 silver badges191 bronze badges

1 Comment

Johnno Nolan Over a year ago

Yeah I've had this problem before using SSMS on a sql 2000 instance

Kyle B. · Accepted Answer · 2009-01-06 20:53:23Z

1

I copied/pasted this exact SQL statement and it executed properly for me. The only thing I can think is that you have something highlighted and it is executing only that portion of it (which may not be a complete statement).

answered Jan 6, 2009 at 20:53

Kyle B.

5,7976 gold badges42 silver badges57 bronze badges

1 Comment

l15a Over a year ago

I've tried multiple times and definitely don't have anything highlighted. Thanks for the suggestion though.

casperOne · Accepted Answer · 2009-01-06 20:55:29Z

0

With the exception of the use [MyDatabase] statement (I changed it to tempdb), this statement executes correctly when I run it against SQL Server 2005 using Microsoft SQL Server Management Studio.

answered Jan 6, 2009 at 20:55

casperOne

74.7k19 gold badges189 silver badges262 bronze badges

Comments

Zane · Accepted Answer · 2011-01-11 14:20:47Z

0

Using Management Studio 2008, you can create a script that is compatible with a lower version of SQL server like this:

Right click on the database >> Tasks >> Generate Scripts

In that wizard you can access the option "Script for server version". Just select the version of SQL Server that you want the script to run on.

answered Jan 11, 2011 at 14:20

Zane

2,8702 gold badges27 silver badges24 bronze badges

Collectives™ on Stack Overflow

SQL Server 2005 Generated Create Table Script not working

7 Answers 7

2 Comments

1 Comment

1 Comment

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

1 Comment

1 Comment

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related