• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

Trying to alter a table.

I'm trying to alter the below table and am getting the following error message.  Need to know how to get around the message and alter the table.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.

I added (This is line 1) and (This is line 5) to show which lines the error code is referring to.  These are not in the actual script.

Also, I only added the bottom two Alter Table lines.  Everything else was already there.

I appreciate any help.

Regards,
J.R.
USE [Ops]
GO
/****** Object:  Table [dbo].[ExceptionTicket]    Script Date: 10/19/2011 15:07:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
(This is line 1) Alter TABLE [dbo].[ExceptionTicket](
	[Id] [uniqueidentifier] NOT NULL,
	[ExceptionId] [int] NOT NULL,
	[StatusId] [uniqueidentifier] NOT NULL,
	[Problem] [varchar](500) NULL,
	[Resolution] [varchar](500) NULL,
	[Comments] [varchar](500) NULL,
	[Closed] [datetime] NULL,
	[OwnerId] [int] NULL,
	[Created] [datetime] NOT NULL,
	[Enabled] [bit] NOT NULL,
 CONSTRAINT [PK_ExceptionTicket] PRIMARY KEY CLUSTERED 
(
	[Id] 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
SET ANSI_PADDING OFF

GO
ALTER TABLE [dbo].[ExceptionTicket] CHECK CONSTRAINT [FK_ExceptionTicket_ExceptionTicketStatus]

ALTER TABLE [dbo].[ExceptionTicket] Drop CONSTRAINT [FK_ExceptionTicket_ExceptionTicketStatus]

(This is line 5) ALTER TABLE [dbo].[ExceptionTicket] MODIFY ([Id] int,[StatusId] int)

Open in new window

0
JRFromSoCal
Asked:
JRFromSoCal
  • 6
  • 4
  • 2
  • +2
1 Solution
 
Ephraim WangoyaCommented:
Use this syntax
Alter TABLE [dbo].[ExceptionTicket] alter column [Id] [uniqueidentifier] NOT NULL

Open in new window

0
 
JRFromSoCalAuthor Commented:
Thanks for responding ewanqoya.  This works without error, but I'm still getting the same error for line 1.  Any ideas?
0
 
Éric MoreauSenior .Net ConsultantCommented:
I would try

Alter TABLE [dbo].[ExceptionTicket](
      [Id] [uniqueidentifier] NOT NULL,
      [ExceptionId] [int] NOT NULL,
      [StatusId] [uniqueidentifier] NOT NULL,
      [Problem] [varchar](500) NULL,
      [Resolution] [varchar](500) NULL,
      [Comments] [varchar](500) NULL,
      [Closed] [datetime] NULL,
      [OwnerId] [int] NULL,
      [Created] [datetime] NOT NULL,
      [Enabled] [bit] NOT NULL,
 CONSTRAINT [PK_ExceptionTicket] PRIMARY KEY CLUSTERED
(
      [Id] ASC
)
)
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Ephraim WangoyaCommented:

each altered column has to be preceded by an alter table statement

Alter TABLE [dbo].[ExceptionTicket] alter column [Id] [uniqueidentifier] NOT NULL
Alter TABLE [dbo].[ExceptionTicket] alter column [ExceptionId] [int] NOT NULL
.....

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
@JRFromSoCal: - I am trying to explain more what ewangoya: already said

As far as I know, you cannot ALTER a table by just chaning the word 'CREATE' to 'ALTER' in create table script. (Like we are doing for Stored Procedures, Views etc..)

To be more clear,  this is one create table script
CREATE TABLE tempTable2
(
	namee varchar(20)
)
GO
-- Table 'tempTable2' created

Open in new window


Now if we try to modify this table like shown below, it won't work
ALTER TABLE tempTable2
(
	id		int,  -- new column
	namee	varchar(50) -- 20 changed to 50
)
GO
-- Msg 102, Level 15, State 1, Line 2
-- Incorrect syntax near '('.

Open in new window


This can be achieved by two ALTER TABLE queries as shown below (Better practice is use 'GO' statement between each sql queries)
ALTER TABLE tempTable2 ADD id		int
GO
ALTER TABLE tempTable2 ALTER COLUMN namee	varchar(50)
GO
-- Command(s) completed successfully.

Open in new window


Raj
0
 
Anil GolamariCommented:
Try using this for your line 5

ALTER TABLE [dbo].[ExceptionTicket] ALTER COLUMN [Id] int NOT NULL
ALTER TABLE [dbo].[ExceptionTicket] ALTER COLUMN [StatusId] int NOT NULL
0
 
JRFromSoCalAuthor Commented:
Hi Lucky85:

This is the same solution that ewangoya: gave.  Thanks to all for replying.  Our DBA gave me the solution.  Go to the table in Server Management Studio.  Highlight the table and right click.  Select Design.  Highlight the column and right click.  Delete the column and then add the column back with the attribute you want.  This is the only thing that has worked for me.  I don't have data in the table.  If I did, this is not the way I would go.

Regards,
J.R.
0
 
JRFromSoCalAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for JRFromSoCal's comment http:/Q_27405585.html#37008749

for the following reason:

The DBA where I work gave me the solution.
0
 
Rajkumar GsSoftware EngineerCommented:
Did you try our solutions ?

We provided the script for the same steps you have done through SQL Server Management Studio. That is the only difference!

Raj
0
 
JRFromSoCalAuthor Commented:
Honestly RajkumarGS: I didn't try your solution.  I had already fixed the problem.  I see now that you are correct.  I tried to give you the points for this.  However, I got an error that I had to cancel the previous close request.  I don't know how to do that.
0
 
Rajkumar GsSoftware EngineerCommented:
I think, you need to click on 'Object' button below and proceed.

If you really want to give points, please consider other expert comments before me. If that also works, split points.

Thanks
Rajkumar
0
 
JRFromSoCalAuthor Commented:
Closing this questions
0
 
JRFromSoCalAuthor Commented:
Issue closed.
0
 
Rajkumar GsSoftware EngineerCommented:
Thanks for the points.

Please take care to accept the exact comment which provided you the solution as the 'Accepted Solution'. I know that the solution you meant is http:#36998106

Regards
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 6
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now