CODEWITHSUNDEEP
sql-server
l15a
l15a

Reputation: 2617

SQL Server 2005 Generated Create Table Script not working

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

Upvotes: 5

Views: 8565

Answers (7)

Zane
Zane

Reputation: 2870

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.

Upvotes: 0

ZenDraken
ZenDraken

Reputation:

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

Upvotes: 10

DJ.
DJ.

Reputation: 16247

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

Upvotes: 9

HLGEM
HLGEM

Reputation: 96552

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.

Upvotes: 2

Dave Baghdanov
Dave Baghdanov

Reputation: 2358

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.

Upvotes: 2

casperOne
casperOne

Reputation: 74530

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.

Upvotes: 0

Kyle B.
Kyle B.

Reputation: 5787

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

Upvotes: 1

Related Questions