Msg 156, Level 15, State 1

Question

Msg 156, Level 15, State 1

mark.baker 24141

Old Hand

Points: 382
More actions
April 26, 2012 at 5:55 am

#267978

Hi,
i am quite new to SQL Server and i am trying to create a table when i keep getting:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'key'.
this is the create table string
create table #tr1
(
id int identity primary key,
spid varchar(12) COLLATE SQL_Latin1_General_CP1_CI_AS,
licensed_provider int,
test_date datetime, -- Connection/Dis-Connection or financial year limits
date_type char COLLATE SQL_Latin1_General_CP1_CI_AS, -- 'A' earliest, 'B' latest
reason varchar(1024) COLLATE SQL_Latin1_General_CP1_CI_AS -- Why spid failed, freetext
)
any help would be great

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

That code runs fine and without any error on my SQL 2008 server. Sure it's that exact piece of code that's causing the error?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

mark.baker 24141 Old Hand Points: 382 More actions · Answer 2

well heres the only reference to key in the script

create table #shortlist (spid varchar(12) COLLATE SQL_Latin1_General_CP1_CI_ASprimary key);

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Yup, that's the problem. There's a space missing between the end of the collation name and the word 'primary'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

mark.baker 24141 Old Hand Points: 382 More actions · Answer 4

haha i literally picked that up just before i had your reply

but many thanks anyway

how can i convert a string to a datetime

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Look up CAST or CONVERT.

btw, we prefer that new questions get asked in a new thread. Keeps things cleaner.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

jitendra.padhiyar SSCertifiable Points: 5824 More actions · Answer 6

Yes, the space was missing in between collate definatino and primary key.

It should be like below :

create table #shortlist (spid varchar(12) COLLATE SQL_Latin1_General_CP1_CI_AS primary key);

jitendra.padhiyar SSCertifiable Points: 5824 More actions · Answer 7

As GilaMonster suggested use the Convert function to convert date and time from string or reverse too, Below is an example

Select CONVERT(VARCHAR(19),GETDATE())

Result will be your sytems date and time:

Apr 26 2012 11:47PM

You can do reverse as well.