MyLittleAdmin Error -2147217900 - Incorrect syntax near ''(''.

Hi,
I'm trying to create various tables on a hosted environment. Unfortunately the hosting company (lets just say 1 + 1 don't = 2) don't support remote connections so I can't access the DB using MS SQL Management Studio. So I have to use a 3rd party tool - MyLittleAdmin.

I can import scripts, and the majority of the time its ok, but I can't create certain tables that uses the generated scripts from Management Studio.

The error I received is:

Error -2147217900

Line 14: Incorrect syntax near '('.

The Script is:

Code Snippet

USE
[dbName]
GO
/****** Object: Table [dbo].[tfs_AreaManager] Script Date: 08/06/2007 14:29:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tfs_AreaManager](
[armId] [bigint] IDENTITY(1,1) NOT NULL,
[armUserId] [uniqueidentifier] NOT NULL,
[armAreaName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[armRgmId] [bigint] NULL,
[armFName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[armSName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[armEmail] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[armPhone] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tfs_AreaManagers] PRIMARY KEY CLUSTERED
(
[armId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tfs_AreaManager] WITH CHECK ADD CONSTRAINT [FK_tfs_AreaManager_tfs_RegionManager] FOREIGN KEY([armRgmId])
REFERENCES [dbo].[tfs_RegionManager] ([rgmId])
GO
ALTER TABLE [dbo].[tfs_AreaManager] CHECK CONSTRAINT [FK_tfs_AreaManager_tfs_RegionManager]

I think line 14 refers to the 14th line counting from the 3rd GO instruction (code highlighted in RED).

Can anyone advise me as to what the problem is. I can create the table if I only use the script before the constraint operation. If I create the table, and then just script the constraints after, then they fail too. So I know it is the constraint opertaion that is the problem. Any help would be greatly appreciated!

Seems that you create the scripts with a SQL Server 2005 instance and the target one is a SQL Server 2000< one (or pretends to be in terms of compability level). So either change the compat level (if the target instance is a 2005 server) or create the scripts with compat level of your target database.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Oh that could be it! Do you know how I generate scripts for a SQL Server 2000 target?

Thanks for the help.

|||

If you are using the script generator you can use the "Script for Server Version" > SQL Server 2000

Jens K. Suessmeyer

http://www.sqlserver2005.de