Newbie needs help on creating a table in a database

Question

Newbie needs help on creating a table in a database

Frank Boonen

SSC Enthusiast

Points: 134
More actions
January 25, 2007 at 12:41 am

#171999

Hi,
I am very new to SQL server, and hope someone can help me out.
I use the following script (which I found on this website) to create a table CarianTouchPosEj in the database CarianTouchPos I managed to create the CarianTouchPos on the SQL server express
USE master
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE NAME = 'CarianTouchPosEj')
CREATE TABLE CarianTouchPosEj
(
DateID INT NOT NULL
, TheDate DATETIME NOT NULL
, DayOfMonth INT NOT NULL
, DayOfYear INT NOT NULL
, DayOfWeek INT NOT NULL
, DayName VARCHAR(9) NOT NULL
, WeekOfYear INT NOT NULL
, WeekName VARCHAR(7) NOT NULL
, MonthOfYear INT NOT NULL
, MonthName VARCHAR(9) NOT NULL
, Quarter INT NOT NULL
, QuarterName CHAR(6) NOT NULL
, Year INT NOT NULL
,
)
GO
When I execute this script I get an error 102 Syntax error near Go.
My idea is to get this script running and then adapt it to my own needs. I hope someone can help me out here
best regards
Frank

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 1

Hi Frank,

That script executes fine on instances of SQL 2000, SQL 2005 and SQL Express for me - are you sure thats the exact script you are running?

- James

--
James Moore
Red Gate Software Ltd

Tim Mitchell SSCoach Points: 15681 More actions · Answer 2

Frank,

You have one comma too many within your parentheses:

, Year INT NOT NULL

,

)

Take out the last comma and you're good to go.

hth

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Frank Boonen SSC Enthusiast Points: 134 More actions · Answer 3

Hi Tim,

thanks for spending time to look at my problem, unfortunately it didn't help. The same error message is displayed on the screen. In my code I store the follwing string in my variable strcmd:

IF NOT EXISTS (SELECT * FROM sysobjects WHERE NAME = 'CarianTouchPosEj')

CREATE TABLE CarianTouchPosEj

(

DateID INT NOT NULL

, TheDate DATETIME NOT NULL

, DayOfMonth INT NOT NULL

, DayOfYear INT NOT NULL

, DayOfWeek INT NOT NULL

, DayName VARCHAR(9) NOT NULL

, WeekOfYear INT NOT NULL

, WeekName VARCHAR(7) NOT NULL

, MonthOfYear INT NOT NULL

, MonthName VARCHAR(9) NOT NULL

, Quarter INT NOT NULL

, QuarterName CHAR(6) NOT NULL

, Year INT NOT NULL

)

GO

Then I use the following to pass the string to the sql server:

sqlCmd.CommandText = strCmd;

sqlCmd.ExecuteNonQuery();

then I get the error, as I removed the last comma as you suggested there must be something else I'm doing wrong.

Looking forward to your reply!

best regards

Frank

Tim Mitchell SSCoach Points: 15681 More actions · Answer 4

Frank,

Try removing the GO statement altogether. This statement is simply a batch terminator, and assuming this is the only command you are running in this batch it is unnecessarily verbose to include it.

hth

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

The 'GO' batch terminator is configurable at the client. Depending on what is running the statement, this may not be a valid terminator. The script should work without the GO's.

Your script is creating a table in the Master database which is typically not a good idea.