Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 950
  • Last Modified:

Syntax Error 7.0 - COLLATE

Can you tell me why I am getting this syntax error when I try to use COLLATE in creating a table? Thanks!
Kathy

CREATE TABLE [dbo].[ArchiveLog] (
     [Id] [int] NOT NULL ,
     [ArchiveDate] [datetime] NOT NULL ,
     [ArchiveRunDate] [datetime] NOT NULL ,
     [Flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [Success] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'COLLATE'.
0
ktylerconk
Asked:
ktylerconk
1 Solution
 
mnyeCommented:
that table creates fine on my system.  Check your availiable collations on your system

SELECT *
FROM ::fn_helpcollations()


that selects all valid collations.  make sure the one you are trying to use is availiable.

the syntax is correct, its probably just an option you have set on your installation that you have to change,

hth
late
matt
0
 
Brendt HessSenior DBACommented:
SQL Server 7.0 doesn't support COLLATE.  That is new in SQL Server 2000.
0
 
TheSpiritCommented:
I guess you created this script on a SQL2000 server and are now applying it to a SQL7 server... as bhess1 says SQL7 does not support COLLATE, in SQL2000 you can specify a default collation at install time then override this down to column level when you create objects (tables ect) to get the script to work you need to modify it as follows


CREATE TABLE [dbo].[ArchiveLog] (
    [Id] [int] NOT NULL ,
    [ArchiveDate] [datetime] NOT NULL ,
    [ArchiveRunDate] [datetime] NOT NULL ,
    [Flag] [char] (1) NOT NULL ,
    [Success] [varchar] (255) NULL
) ON [PRIMARY]
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now