syntax near '(' - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

syntax near '('

Author

Topic

sqldummi
Starting Member

4 Posts

Posted - 2011-09-13 : 13:44:53

I keep getting following errors

Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 48
Line 48: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 28
Line 28: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 48
Line 48: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 19
Line 19: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 21
Line 21: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 11

Below is the sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SatT]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SatT](
[TorID] [varchar](50) NOT NULL,
[StartDate] [datetime] NOT NULL,
[StartTime] [datetime] NOT NULL,
[TName] [varchar](50) NOT NULL,
[Status] [int] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AffliatedPlayers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AffliatedPlayers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PlayerId] [varchar](50) NOT NULL,
[AffliateId] [varchar](20) NULL,
[AffliateCode] [int] NULL,
[BType] [varchar](4) NULL,
[BGiven] [varchar](10) NULL,
[BLimit] [varchar](10) NULL,
[LastPaid] [datetime] NULL,
[Status] [varchar](2) NULL,
CONSTRAINT [PK_AffliatedPlayers] PRIMARY KEY CLUSTERED
(
[PlayerId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SatT_D]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SatT_D](
[TorID] [varchar](50) NULL,
[StartTime] [datetime] NULL,
[TName] [varchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AllInHistory]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AllInHistory](
[PlayerID] [varchar](6) NOT NULL,
[PlayID] [varchar](25) NOT NULL,
[AllInAmt] [money] NULL,
[TimeStamp] [datetime] NULL,
CONSTRAINT [PK_AllInHistory] PRIMARY KEY CLUSTERED
(
[PlayerID] ASC,
[PlayID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServerDetails]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ServerDetails](
[Sid] [int] IDENTITY(1,1) NOT NULL,
[Status] [char](1) NULL,
[TimeDelay] [int] NULL,
[LogEntry] [int] NULL,
[LogDrive] [varchar](50) NULL,
[SitOutTime] [int] NULL,
[ListPlayers] [int] NULL,
[ServerIp] [varchar](254) NULL,

CONSTRAINT [PK_ServerDetails] PRIMARY KEY CLUSTERED
(
[Sid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 13:51:09

i dont think you've sys.objects in sql 2000. you should be using sysobjects instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqldummi
Starting Member

4 Posts

Posted - 2011-09-14 : 05:46:40

well sysobjects didnt not work either ..

strange thing is if i only execute

(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmountTransactions]') AND type in (N'U'))

it shows successful but when I use

GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmountTransactions]') AND type in (N'U'))

it shows error

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ')'.

is there an issue with "IF NOT EXISTS" ?

Kristen
Test

22859 Posts

Posted - 2011-09-14 : 06:27:03

Is your BATCH SEPARATOR set to something other than "GO" ?

Kristen
Test

22859 Posts

Posted - 2011-09-14 : 06:28:30

You would need something after the IF in your test, so for example:


GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmountTransactions]') AND type in (N'U'))
	SELECT 'Table does not exist'

sqldummi
Starting Member

4 Posts

Posted - 2011-09-14 : 07:02:37

tried that did not work

Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '('.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SatTour]') AND type in (N'U'))
SELECT 'Table does not exist'
BEGIN
CREATE TABLE [dbo].[SatTour](
[TourId] [varchar](50) NOT NULL,
[StartDate] [datetime] NOT NULL,
[StartTime] [datetime] NOT NULL,
[TourName] [varchar](50) NOT NULL,
[Status] [int] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AffliatedPlayers]') AND type in (N'U'))
SELECT 'Table does not exist'
BEGIN
CREATE TABLE [dbo].[AffliatedPlayers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PlayerId] [varchar](50) NOT NULL,
[AffliateId] [varchar](20) NULL,
[AffliateCode] [int] NULL,
[BonusType] [varchar](4) NULL,
[BonusGiven] [varchar](10) NULL,
[BonusLimit] [varchar](10) NULL,
[LastPaid] [datetime] NULL,
[Status] [varchar](2) NULL,
CONSTRAINT [PK_AffliatedPlayers] PRIMARY KEY CLUSTERED
(
[PlayerId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SatTour_D]') AND type in (N'U'))
SELECT 'Table does not exist'
BEGIN
CREATE TABLE [dbo].[SatTour_D](
[TourId] [varchar](50) NULL,
[StartTime] [datetime] NULL,
[TourName] [varchar](50) NULL
) ON [PRIMARY]
END

Kristen
Test

22859 Posts

Posted - 2011-09-14 : 08:03:03

Did you check your BATCH SEPARATOR ?

If this works:


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmountTransactions]') AND type in (N'U'))
	SELECT 'Table does not exist'

but this does not work:


GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmountTransactions]') AND type in (N'U'))
	SELECT 'Table does not exist'

then its probably the BATCH SEPARATOR.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 08:12:34

hmm...thats a good point Kristen

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqldummi
Starting Member

4 Posts

Posted - 2011-09-14 : 08:28:08

Its very strange .. following works with and without GO

quote:
below works

GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SatTour]') AND type in (N'U'))
SELECT 'Table does not exist'
BEGIN
CREATE TABLE [dbo].[SatTour](
[TourId] [varchar](50) NOT NULL,
[StartDate] [datetime] NOT NULL,
[StartTime] [datetime] NOT NULL,
[TourName] [varchar](50) NOT NULL,
[Status] [int] NOT NULL
) ON [PRIMARY]
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AffliatedPlayers]') AND type in (N'U'))
SELECT 'Table does not exist'
BEGIN

quote:
below does not work ..............!

CREATE TABLE [dbo].[AffliatedPlayers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PlayerId] [varchar](50) NOT NULL,
[AffliateId] [varchar](20) NULL,
[AffliateCode] [int] NULL,
[BonusType] [varchar](4) NULL,
[BonusGiven] [varchar](10) NULL,
[BonusLimit] [varchar](10) NULL,
[LastPaid] [datetime] NULL,
[Status] [varchar](2) NULL,
CONSTRAINT [PK_AffliatedPlayers] PRIMARY KEY CLUSTERED
(
[PlayerId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

quote:
below work ..............!

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SatTour_D]') AND type in (N'U'))
SELECT 'Table does not exist'
BEGIN
CREATE TABLE [dbo].[SatTour_D](
[TourId] [varchar](50) NULL,
[StartTime] [datetime] NULL,
[TourName] [varchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AllInHistory]') AND type in (N'U'))
SELECT 'Table does not exist'

quote:
below does not work ..............!

BEGIN
CREATE TABLE [dbo].[AllInHistory](
[PlayerID] [varchar](6) NOT NULL,
[GameID] [varchar](25) NOT NULL,
[AllInAmt] [money] NULL,
[TimeStamp] [datetime] NULL,
CONSTRAINT [PK_AllInHistory] PRIMARY KEY CLUSTERED
(
[PlayerID] ASC,
[GameID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

Kristen
Test

22859 Posts

Posted - 2011-09-14 : 13:46:23

You've changed the flow of the logic.


IF ...
SELECT ...
BEGIN
...
END

the BEGIN / END will always execute. So SQL will attempt to create it, even if it already exists - but you would get some sort of "Table already exists" syntax error - is that what you are seeing?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-14 : 14:51:49

WITH (IGNORE_DUP_KEY = OFF)

^ Not valid SQL 2000 syntax.

For 2000 it's just

WITH IGNORE_DUP_KEY

Kristen
Test

22859 Posts

Posted - 2011-09-14 : 14:53:11

If that's it that will be a game-winning-catch

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-14 : 14:56:09

http://msdn.microsoft.com/en-us/library/aa258260(v=sql.80).aspx