Simple script... doesn't work - 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

General SQL Server Forums

New to SQL Server Programming

Simple script... doesn't work

Author

Topic

vonehle
Starting Member

4 Posts

Posted - 2007-04-20 : 13:38:29

***Disclaimer***
This is homework, just need a hint.

I compare this to other scripts and can't find any differences. I'm getting a syntax error:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.

Any hints would be great! Thanks.


USE StateUBookstore

ALTER TABLE dbo.Classes
ADD( InstructorID char (5) NOT NULL,
	 Credits int NOT NULL);
GO

ALTER TABLE dbo.Students
ADD( PhoneNO char(14) NULL,
	 Email char (50) NULL),
DROP COLUMN BookStoreEmp;
GO

ALTER TABLE dbo.Enrollment
ADD( Semester int NOT NULL,
	 EndDate DateTime NOT NULL);

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-20 : 13:42:32

The error message gives you the answer already. Check in Books Online for the proper syntax of ALTER TABLE. They have an example there on adding a column.

SQL Server Helper
http://www.sql-server-helper.com

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-20 : 13:43:05

You can only add one column at a time. so separate them out and you should be fine.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

vonehle
Starting Member

4 Posts

Posted - 2007-04-20 : 13:44:59

This website said I could do more than 1 at a time.

http://www.techonthenet.com/sql/tables/alter_table.php

This is wrong?

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-20 : 13:45:35

quote:
Originally posted by dinakar

You can only add one column at a time. so separate them out and you should be fine.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

You can add more than one column at a time.

SQL Server Helper
http://www.sql-server-helper.com

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-20 : 13:46:37

Another hint, you have a character there that shouldn't be there.

SQL Server Helper
http://www.sql-server-helper.com

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-20 : 13:52:04

quote:
Originally posted by sshelper

quote:
Originally posted by dinakar

You can only add one column at a time. so separate them out and you should be fine.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

You can add more than one column at a time.

SQL Server Helper
http://www.sql-server-helper.com

Since when?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-20 : 13:57:29

quote:
Originally posted by X002548

quote:
Originally posted by sshelper

quote:
Originally posted by dinakar

You can only add one column at a time. so separate them out and you should be fine.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

You can add more than one column at a time.

SQL Server Helper
http://www.sql-server-helper.com

Since when?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Sorry about that. I take it back. You CAN add multiple columns..Dont know what I was thinking when I said that.. could it be the friday

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-20 : 14:00:58

Well in DB2 you can't, and for reasons here

http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html

I don't use ALTER to add columns

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

vonehle
Starting Member

4 Posts

Posted - 2007-04-20 : 14:18:58

I still don't see any differences between what I have and what the example shows on http://www.techonthenet.com/sql/tables/alter_table.php

I could give up and do it one line at a time, but I really want to understand what the problem is here.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-20 : 14:24:59

I couldn't get the DROP and ADD to occur in the same command, but I didn't try too hard either. This doesn't error out at least:

ALTER TABLE dbo.Classes
ADD InstructorID char (5) NOT NULL,
Credits int NOT NULL
GO

ALTER TABLE dbo.Students
ADD PhoneNO char(14) NULL,
Email char (50) NULL
GO

ALTER TABLE dbo.Students
DROP COLUMN BookStoreEmp
GO

ALTER TABLE dbo.Enrollment
ADD Semester int NOT NULL,
EndDate DateTime NOT NULL

Tara Kizer
http://weblogs.sqlteam.com/tarad/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-20 : 14:26:26

ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-20 : 14:32:13

quote:
Originally posted by dinakar

ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified

True, but that's not why he is getting syntax errors. Once he gets rid of the syntax errors, he'll have new errors that reference this.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

vonehle
Starting Member

4 Posts

Posted - 2007-04-20 : 14:38:33

I see now that the parantheses were the problem. I guess the examples on the website I was referencing were wrong. Thanks everybody.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources