[RESOLVED] SQL Server 2005 AutoGenerated Create Table Problem

[rjbudz]

Hi,

In SQL Server 2005 Management Studio, I'm trying to copy an existing table from one database to another (DEV to PROD).

My first thought is to right click the table name, select Edit, copy the generated SQL for create Table, and use it in a New Query in the PROD database.

However, when I check the syntax of the SQL statement, I get the following error:

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

I've looked over the SQL statement. All parens match, so it's not that. Being somewhat unfamiliar with creating tables this way, I just can not locate the error. I would think that SQL generated code would be syntax correct.

I've attempted to rename the table inthe SQL statement in caes it is breaking because the table exists. That doesn't do it.

Here is the SQL Statement:

USE [dbHMDS2004]
GO
/****** Object:  Table [dbo].[tblInvoiceImportCommentsx]    Script Date: 02/12/2008 09:26:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblInvoiceImportCommentsx](
	[nvchrInternalComment] [nvarchar](50) NOT NULL,
	[bitInactive] [bit] NULL,
	[bitAdminOnly] [bit] NULL,
	[dtmCreatedDate] [smalldatetime] NOT NULL CONSTRAINT [DF_tblFrmRowCommentAdmin_dtmCreatedDate]  DEFAULT (getdate()),
	[dtmModifiedDate] [smalldatetime] NOT NULL CONSTRAINT [DF_tblFrmRowCommentAdmin_dtmModifiedDate]  DEFAULT (getdate()),
	[nvchrUserName] [nvarchar](50) NULL,
	[intBusinessRule] [int] NULL,
 CONSTRAINT [PK_tblFrmRowCommentAdmin] PRIMARY KEY CLUSTERED 
(
	[nvchrInternalComment] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Any ideas? Much thanks. I need to do the update to PROD today.

[si_the_geek]

Check the last two lines.. you've got this part twice: ) ON [PRIMARY]

[szlamany]

My first thought is to right click the table name, select Edit, copy the generated SQL for create Table, and use it in a New Query in the PROD database.

Although that is a novel approach...

Next time simply right-click the table - SCRIPT TABLE AS > CREATE TO > ...

and then it's your choice.

Send it to a query window (that's what we always do).

Send it to a file.

or send it directly to the clipboard.

[rjbudz]

Thank you for the fast responses!

I tried both suggestions. I attempted to remove each On Primary in turn without success. Also tried the SCRIPT TABLE AS > CREATE TO > route (which does seem more practical, thank you), but still get the same error.

Weird, huh?

[szlamany]

You are saying that you SCRIPT'd the CREATE to a query window...

And without any other typing - it's got a syntax error?

Basically you SCRIPT the CREATE and then you can click the CHECK MARK to check the syntax...

If you are truly getting a syntax error on that SCRIPT'd CREATE then I believe you have found a bug in MGT STUDIO.

What version/SP are you running?

[rjbudz]

After further tickering, I got it to syntax check successfully this way:

CREATE TABLE [dbo].[tblInvoiceImportComments](
	[nvchrInternalComment] [nvarchar](50) NOT NULL,
	[bitInactive] [bit] NULL,
	[bitAdminOnly] [bit] NULL,
	[dtmCreatedDate] [smalldatetime] NOT NULL CONSTRAINT [DF_tblFrmRowCommentAdmin_dtmCreatedDate]  DEFAULT (getdate()),
	[dtmModifiedDate] [smalldatetime] NOT NULL CONSTRAINT [DF_tblFrmRowCommentAdmin_dtmModifiedDate]  DEFAULT (getdate()),
	[nvchrUserName] [nvarchar](50) NULL,
	[intBusinessRule] [int] NULL,
 CONSTRAINT [PK_tblFrmRowCommentAdmin] PRIMARY KEY CLUSTERED)

I added a peren afre the CLUSTERD and removed

(
	[nvchrInternalComment] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
) ON [PRIMARY]

Now I get this error:

Msg 8135, Level 16, State 0, Line 1
Table level constraint does not specify column list, table 'dbo.tblInvoiceImportCommentsx'.

So I'm assuming the error is in the last part. It seems to be needed, but how?

[rjbudz]

Thanks szlamany,

Yes I did it exactly as you asked.

A bug? Oh joy.

Here's teh SQL Server About Box info:


Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

[szlamany]

Well - that looks just like the version I've got here on my laptop.

A google for this:

create table script syntax errors with management studio

shows that you are not alone...

[rjbudz]

Somehow not being alone is little comfort. I'll research via Google and see what they're attempting. I'll be back. Thanks.

If you find anything in the meantime. please post it!

[szlamany]

See above

Yeah - I did - and then I deleted that post...

A little cross posting going on here - make sure you see my google post...

[rjbudz]

Ok,

I removed the default values for the table. They really aren't needed. I dropped the part after the closing quote on CREATE TABLE

The SQL Statement reads:

CREATE TABLE [dbo].[tblInvoiceImportCommentsx](
	[nvchrInternalComment] [nvarchar](50) NOT NULL,
	[bitInactive] [bit] NULL,
	[bitAdminOnly] [bit] NULL,
	[dtmCreatedDate] [smalldatetime] NULL ,
	[dtmModifiedDate] [smalldatetime] NULL ,
	[nvchrUserName] [nvarchar](50) NULL,
	[intBusinessRule] [int] NULL)

This passes the Syntax check AND seems to have created the table successfully.

The remaining question is: Have I set myself up for a flaming explosion when I go live?

[techgnome]

This is what you need:

CREATE TABLE [dbo].[tblInvoiceImportComments](
	[nvchrInternalComment] [nvarchar](50) NOT NULL,
	[bitInactive] [bit] NULL,
	[bitAdminOnly] [bit] NULL,
	[dtmCreatedDate] [smalldatetime] NOT NULL CONSTRAINT [DF_tblFrmRowCommentAdmin_dtmCreatedDate]  DEFAULT (getdate()),
	[dtmModifiedDate] [smalldatetime] NOT NULL CONSTRAINT [DF_tblFrmRowCommentAdmin_dtmModifiedDate]  DEFAULT (getdate()),
	[nvchrUserName] [nvarchar](50) NULL,
	[intBusinessRule] [int] NULL,
 CONSTRAINT [PK_tblFrmRowCommentAdmin] PRIMARY KEY CLUSTERED(
	[nvchrInternalComment] ASC
)
) ON [PRIMARY]

The WITH statement causes issues sometimes.... I've found that scripts that include it don't work when you move them around.

I've never been able to find why, but then I havcen't looked too hard either.

-tg

[szlamany]

The WITH issue seems to be mentioned in some of the links that I googled for.

I'm sure we will see that corrected in future releases of Mgt Studio.

[rjbudz]

techgnome,

That worked PERFECTLY! Thank you. My headache should subside soon.

szlamany,

I look forward to the day Microsoft fixes the bug. Perhaps they will name it after me!

Thanks everyone!