importing tables using 2005 express

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

importing tables using 2005 express

Author

Topic

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-04-27 : 12:26:09

Sorry, I don't know where to post this:
I created a DB using 2005 express, I scripted all the tables and I connected to the production DB (using 2005 express) and pasted the code in hit execute and it keeps saying this:
Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '('.

I'm using the correct DB
here's one of the scripts:


USE [CX]
GO
/****** Object:  Table [dbo].[Attachments]    Script Date: 04/27/2009 11:00:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Attachments](
	[AID] [int] IDENTITY(1,1) NOT NULL,
	[TL_ID] [int] NOT NULL,
	[Attachment] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_Attachments] PRIMARY KEY CLUSTERED 
(
	[AID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I manually created a table on the production side and it worked. I then scripted the table and can see NO difference.
What's going on?
Thanks

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-04-27 : 13:31:37

Ok, I've finally discovered that the production side doesn't like the constraint. I removed it and the create statement worked.
I would still like to know why I can't create a table with constraints.
I would like to perform this without having to resort to manually fixing each table.
Can anyone tell me what's wrong with the constraint above?

thanks

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-04-27 : 16:30:48

ok, I found the problem:
my dba lied to me! He said the DB was 2005 and it's really 2000!
ARGGH!
so I have to translate this into SQL 2000 syntax:


CONSTRAINT [PK_Attachments] PRIMARY KEY CLUSTERED 
(
	[AID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

oh what fun,

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-27 : 16:44:33

[code]CREATE TABLE [dbo].[Attachments](
[AID] [int] IDENTITY(1,1) NOT NULL,
[TL_ID] [int] NOT NULL,
[Attachment] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Attachments] PRIMARY KEY CLUSTERED
([AID] ASC)
)
[/code]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-04-27 : 17:28:36

Thank you very much Tara!
You're awesome!

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-27 : 17:51:38

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources