Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Location
    Tokyo
    Posts
    6

    Question Unanswered: sqlstatus option trouble

    When I try the following SQL, I get the ERRORS
    listed below.

    ======== SQL =======
    begin

    declare @name varchar(20)

    declare nameC cursor
    for
    select name from dbtable
    where
    id >=2 and
    id <= 4

    open nameC
    fetch nameC into @name

    while (@@sqlstatus = 0 )
    begin
    select @name
    fetch nameC into @name
    end

    close nameC
    deallocate cursor nameC
    end
    =====================

    ========ERRORS =======
    Mg 7344, Level 15, State 2:
    Server 'SYBASE', Line 5:
    DECLARE CURSOR must be the only statement in a query batch.
    Msg 11721, Level 15, State 1:
    Server 'SYBASE', Line 15:
    Global variables not allowed as defaults.
    =====================

    My question is then, how do I set the default
    to allow global variables?

    I have tried using sp_dboption, but it does not work.

    ======== sp_dboption ERRORS =======
    1> use master
    2> sp_dboption testbd, "unique auto_identity index", true
    3> go
    Msg 102, Level 15, State 1:
    Server 'SYBASE', Line 2:
    Incorrect syntax near 'sp_dboption'.
    =====================

    BTW, when I do sp_helpdb testbd reads with "no options set".

    Any help/suggestions would be appreciated!

  2. #2
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    In plain Transact-SQL you must isolate a
    cursor definition in a batch for itself. Remind
    that a batch is the bunch of SQL statements
    from one "go" line to the next "go" line.
    So, you should code:

    declare nameC cursor
    for
    select name from dbtable
    where
    id >=2 and
    id <= 4
    go
    -- SEE THE "go" above
    declare @name varchar(20)
    open nameC
    ...

    This forbids the use of local variables in
    the cursor definition. IIRC, the only
    workaround is to create a stored proc;
    more functional cursors are allowed
    inside stored procedures.

    Regards,
    Mariano Corral

  3. #3
    Join Date
    Oct 2002
    Location
    Tokyo
    Posts
    6
    Creating a stored proc solved the problem very nicely.

    Thank you for the info!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •