Sybase Sequence

Question

I am creating a sequence in sybase but getting mentioned exception, can anyone please help?

CREATE OR REPLACE SEQUENCE dbo.event_id_sequence
    START WITH 100
    INCREMENT BY 1
    MINVALUE 100
    NO MAXVALUE
    NO CACHE  
    NO CYCLE    
go

GRANT USAGE ON SEQUENCE dbo.event_id_sequence TO userID maintenance
GRANT USAGE ON SEQUENCE dbo.event_id_sequence TO userID readonly
GRANT USAGE ON SEQUENCE dbo.event_id_sequence TO userID reports
go

Exception:

[Error] Script lines: 1-14 ------------------------- Incorrect syntax near the keyword 'REPLACE'. Msg: 156, Level: 15, State: 2 [Executed: 7/7/14 2:06:02 PM EDT ] [Execution: 0/ms]

[Error] Script lines: 15-19 ------------------------ Incorrect syntax near 'USAGE'. Msg: 102, Level: 15, State: 84

[Error] Script lines: 15-19 ------------------------ Incorrect syntax near 'USAGE'. Msg: 102, Level: 15, State: 84

[Error] Script lines: 15-19 ------------------------ Incorrect syntax near 'USAGE'. Msg: 102, Level: 15, State: 84

[Executed: 7/7/14 2:06:02 PM EDT ] [Execution: 0/ms]

Answer 1

Sybase ASE does not have sequence, the code you found is likely pulled from Sybase IQ or possible SQL Anywhere.

Instead of using sequence, you should look at using IDENTITY columns instead.

If that does not suit your needs, Sybase suggests a number of other approaches that may give you what you are looking for.

Methods:

Methods which can be used to produce a monotonic series are:

Max Plus One

Enhanced Max Plus One

Next Key Table

Identity Property

All of these are detailed in the linked document.

I would also suggest reviewing page 20 of this presentation on Migration from Oracle to ASE from SAP Techwave. There is some sample code on how to emulate SEQUENCE using a sequence table/procedure.

CREATE TABLE my_seq (seq int) 
go 
//initialize the sequence 
INSERT INTO my_seq select 0 
go 
CREATE PROCEDURE get_seq (@seq int OUTPUT) 
AS UPDATE my_seq SET seq = seq+1 
SELECT @seq = seq FROM my_seq 
go 
// execute the sp to get the next sequence number 
DECLARE @seq int 
EXEC get_seq @seq OUTPUT 
INSERT INTO m_table VALUES (@seq,..) 
go

Outdated Link - http://www.sybase.com/detail?id=860