t-sql alter problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » t-sql alter problem

t-sql alter problem

Rate Topic

Display Mode

Topic Options

Author

Message

wendy elizabeth

Posted Tuesday, February 05, 2013 9:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:58 AM
Points: 137, Visits: 159

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

Post #1415924

ChrisM@Work

Posted Tuesday, February 05, 2013 9:16 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

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 Shaw}

For 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
Exploring Recursive CTEs by Example Dwain Camps

Post #1415934

GilaMonster

Posted Tuesday, February 05, 2013 9:35 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 38,086, Visits: 30,381

wendy elizabeth (2/5/2013)

Can you tell me what is wrong with the following sql:

Err.. everything. Smile

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1415951

wendy elizabeth

Posted Tuesday, February 05, 2013 6:48 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:58 AM
Points: 137, Visits: 159

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.

Post #1416200

GilaMonster

Posted Tuesday, February 05, 2013 11:59 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 38,086, Visits: 30,381

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1416264

Sean Pearce

Posted Wednesday, February 06, 2013 5:19 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 386, Visits: 1,424

You need to make sure that the new data type is compatible with IDENTITY.

Identity column must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.

The SQL Guy @ blogspot

About Me

Post #1416429

« Prev Topic | Next Topic »

Permissions