Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

t-sql alter problem Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2013 9:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 16, 2014 10:01 AM
Points: 365, Visits: 426
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
Posted Tuesday, February 5, 2013 9:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:27 AM
Points: 7,174, Visits: 13,616
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
Posted Tuesday, February 5, 2013 9:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 42,753, Visits: 35,840
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 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
Posted Tuesday, February 5, 2013 6:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 16, 2014 10:01 AM
Points: 365, Visits: 426
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
Posted Tuesday, February 5, 2013 11:59 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 42,753, Visits: 35,840
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
Posted Wednesday, February 6, 2013 5:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:07 AM
Points: 868, Visits: 2,775
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

@SeanPearceSQL

About Me
Post #1416429
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse