t-sql alter problem
-
I am getting the following error on a sql server 2008 r2 database:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Msg 319, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Can you tell me what is wrong with the following sql:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[eRPT_Transaction_Tracking](
[eRpt_Tracking_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[Package_ID] [varchar](50) NOT NULL,
[Received_Date] [datetime] NULL,
[Download_Date] [datetime] NULL,
CONSTRAINT [PK_eRpt_Package] PRIMARY KEY CLUSTERED
(
[eRpt_Tracking_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
-
That's because it's a CREATE TABLE script, with CREATE changed to ALTER. You will need to be more specific and respect the rules of the command you wish to use. Have a look at ALTER TABLE in BOL. Most folks tend to alter one or a very few attributes per statement for clarity.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
wendy elizabeth (2/5/2013)
Can you tell me what is wrong with the following sql:Err.. everything. 🙂
That's a create table statement's form. Alter has very different syntax: http://msdn.microsoft.com/en-us/library/ms190273%28v=SQL.100%29.aspx
What are you trying to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I am trying to alter the datatype of the primary key of the table. The primary key has the identity seed set on and the table has 3 triggers.
-
ALTER TABLE <table name> DROP CONSTRAINT <primary key name>ALTER TABLE <table name> ALTER COLUMN <column name> <new data types>
ALTER TABLE <table name> ADD CONSTRAINT <primary key name> PRIMARY KEY (<columns for the primary key>)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply