Problem running running table script for MS SQL Server

I

isotope

Guppy
Hi,

I scripted all my tables on my local machine using SQL Server Management Studio 2005 and I'm trying to run the script on the JodoHost account in my corresponding database and it says there are errors. Is 2005 doing something different than SQL 2000, because it used to work fine before. My local database is 2000 compatiable. It was able to run the stored procedures script just fine, why is the tables not working and giving me syntax errors?

Thanks,
Jaime
 
Here's the actual sql script for the tables:

USE [realtou_agent]
GO
/****** Object: Table [dbo].[t_agent] Script Date: 09/28/2006 11:26:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_agent](
[agent_id] [int] IDENTITY(1,1) NOT NULL,
[agent_guid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_t_agent_agent_guid] DEFAULT (newid()),
[agent_sNameFirst] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sNameMiddle] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sNameLast] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sNameFull_assistant] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sEmail_assistant] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sPhone_assistant] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sTitle] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sAddress] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sCity] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sZip] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sPhone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sFax] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sWebsite] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sLogoURL] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sPhotoURL] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_boolActive] [bit] NOT NULL CONSTRAINT [DF_t_agent_agent_boolActive] DEFAULT ((1)),
[agent_boolRegistered] [bit] NOT NULL CONSTRAINT [DF_t_agent_agent_boolRegistered] DEFAULT ((0)),
[agentLogin_id] [int] NULL,
[state_id] [int] NULL,
CONSTRAINT [PK_t_agent] PRIMARY KEY CLUSTERED
(
[agent_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[t_agentLogin] Script Date: 09/28/2006 11:26:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_agentLogin](
[agentLogin_id] [int] IDENTITY(1,1) NOT NULL,
[agentLogin_sEmail] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[agentLogin_sUsername] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[agentLogin_sPassword] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_t_agentLogin] PRIMARY KEY CLUSTERED
(
[agentLogin_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[t_state] Script Date: 09/28/2006 11:26:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_state](
[state_id] [int] IDENTITY(1,1) NOT NULL,
[state_sName] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[state_sDesc] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_t_state] PRIMARY KEY CLUSTERED
(
[state_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF









Here's the errors:

Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near '('.
Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '('.
Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '('.
 
Hi Stephen,

Looks like the problems lies within this piece of code:

WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

As soon as you take it out, it works great. Might be a compatibility issue with sql 2000. They talk about installing sp 4 for sql 2000, maybe that will help. I've ran the same script under 2005 and it works great without doing anything to it.


Here's some more info on it:

http://www.mojoportal.com/ForumThreadView.aspx?thread=385&pageindex=3&pageid=5

Let me know if you find anything else on this subject.

Thanks,
Jaime
 
SP4 is already installed, so it is not the issue with that. I am going to have to look into this.
 
You must log in or register to reply here.
Back
Top