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

Answer 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).

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

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".