Problem running running table script for MS SQL Server

General Web Coding Microsoft Development
1

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

2

can you paste the errors it is giving?

3

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 COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sNameMiddle] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sNameLast] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sNameFull_assistant] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sEmail_assistant] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sPhone_assistant] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sTitle] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sAddress] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sCity] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sZip] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sPhone] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sFax] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sWebsite] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sLogoURL] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[agent_sPhotoURL] varchar 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 COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[agentLogin_sUsername] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[agentLogin_sPassword] varchar 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 COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[state_sDesc] varchar 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 ‘(’.

4

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

5

SP4 is already installed, so it is not the issue with that. I am going to have to look into this.