[RESOLVED] SQL Server 2005 AutoGenerated Create Table Problem
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:
Code:
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.
Re: SQL Server 2005 AutoGenerated Create Table Problem
Check the last two lines.. you've got this part twice: ) ON [PRIMARY]
Re: SQL Server 2005 AutoGenerated Create Table Problem
Quote:
Originally Posted by rjbudz
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.
Re: SQL Server 2005 AutoGenerated Create Table Problem
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?
Re: SQL Server 2005 AutoGenerated Create Table Problem
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?
Re: SQL Server 2005 AutoGenerated Create Table Problem
After further tickering, I got it to syntax check successfully this way:
Code:
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
Code:
(
[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?
Re: SQL Server 2005 AutoGenerated Create Table Problem
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
Re: SQL Server 2005 AutoGenerated Create Table Problem
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...
Re: SQL Server 2005 AutoGenerated Create Table Problem
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!
Re: SQL Server 2005 AutoGenerated Create Table Problem
Quote:
Originally Posted by rjbudz
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...
Re: SQL Server 2005 AutoGenerated Create Table Problem
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:
Code:
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?
Re: SQL Server 2005 AutoGenerated Create Table Problem
This is what you need:
Code:
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
Re: SQL Server 2005 AutoGenerated Create Table Problem
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.
Re: SQL Server 2005 AutoGenerated Create Table Problem
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! :bigyello:
Thanks everyone!