• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1112
  • Last Modified:

Sybase (return status = -6)

The stored procedure I wrote is very simple, but it is going no where due to this error:

(return status = -6)

The procedure compiles, but is throwing this error and just sitting there.

Any ideas
0
dfr031260
Asked:
dfr031260
  • 4
  • 2
1 Solution
 
dfr031260Author Commented:
eems to be failing on the fetch.  When the fetch is removed the stored procedlure functions properly:

create       procedure dbo.p_deletebenetest
as
/*
// ________________________________________________________________
//
// Procedure Name:  dbo.p_deletebeneficiary
//Arguments:                
// Created by:  David Russell
// date: June 7, 2006
// comments  : delete rows from Beneficiary table that match rows from WorkArea.dbo.Narada_bene table
//        
//          
// ________________________________________________________________________
*/
 
 
--set nocount on
print 'Process started'


Declare @ConfigurationID int,
        @FormatID   int,
        @RepertoireTrackSeqNumber int,
        @OutboundContractID int
 
begin tran
     
declare deleteBeneList CURSOR for
        select b.ConfigurationID, b.FormatID, b.RepertoireTrackSeqNumber, b.OutBoundContractID
        from RMS.dbo.Beneficiary b
        inner join WorkArea.dbo.Narada_bene nb
        on b.ConfigurationID = nb.ConfigurationID
        and b.FormatID = nb.FormatID
        and b.RepertoireTrackSeqNumber = nb.RepertoireTrackSeqNumber
        and b.OutBoundContractID = nb.OutBoundContractID

print 'Cursor declared'
       
OPEN deleteBeneList

print 'Cursor Open'
 
FETCH deleteBeneList INTO @ConfigurationID,
        @FormatID,
        @RepertoireTrackSeqNumber,
        @OutboundContractID
       
print 'Cursor Fetched'

print 'Totally Done'
CLOSE deleteBeneList
DEALLOCATE cursor deleteBeneList
commit tran
return

0
 
TadHawkinsCommented:
The error message associated with this is usually something like:

Syntax error during explicit conversion of VARCHAR value '...' to a INT field.
(return status = -6)                                                        

One or more of your local variable do not match the type of the column you are fetching from.
0
 
dfr031260Author Commented:
But why would it just stop and spin with no error messages thrown, it just continued running....
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TadHawkinsCommented:
No idea.

I took your code and replaced the select with one of my tables and got...

Process started                                                                                              
Cursor declared                                                                                              
Cursor Open                                                                                                  
Msg 257, Level 16, State 1:                                                                                  
Server 'DEVA', Procedure 'p_wth', Line 39:                                                                    
Implicit conversion from datatype 'CHAR' to 'INT' is not allowed.  Use the CONVERT function to run this query.
Cursor Fetched                                                                                                
Totally Done                                                                                                  
(return status = -6)                                                                                          

... all quite quickly.

Now, the -6 is a general purpose return code described as "miscellaneous user error" [nice, heh] so it could be something else...
0
 
dfr031260Author Commented:
You gonna love this.

What the problem was is that Sybase needs the table being cursored to be indexed.

Does that make any sense?

Also, you column mismatch allegations are not correct.  I checked and made sure that we were matched properly.

Anywho, it is functioning properly.  I changed the cursor, and, well, check it out:

create       procedure dbo.p_deletebeneficiary
as
/*
// ________________________________________________________________
//
// Procedure Name:  dbo.p_deletebeneficiary
//Arguments:                
// Created by:  David Russell
// date: June 7, 2006
// comments  : delete rows from Beneficiary table that match rows from WorkArea.dbo.Narada_bene table
//        
//          
// ________________________________________________________________________
*/
 
 
--set nocount on

Declare @ConfigurationID int,
        @FormatID   int,
        @RepertoireTrackSeqNumber int,
        @OutboundContractID int
       

declare deleteBeneList CURSOR for
        select ConfigurationID, FormatID, RepertoireTrackSeqNumber,OutBoundContractID
        from WorkArea.dbo.Narada_bene nb
       
        print 'Starting process'

OPEN deleteBeneList
 
FETCH deleteBeneList INTO @ConfigurationID,
        @FormatID,
        @RepertoireTrackSeqNumber,
        @OutboundContractID
       
         
        WHILE (@@SQLSTATUS = 0)
 
        BEGIN
       
            select @OutboundContractID
           
           delete from RMS.dbo.Beneficiary
            where ConfigurationID = @ConfigurationID
            and FormatID = @FormatID
            and RepertoireTrackSeqNumber = @RepertoireTrackSeqNumber
            and OutboundContractID = @OutboundContractID
           
            select @ConfigurationID
           
            FETCH deleteBeneList INTO @ConfigurationID,
            @FormatID,
            @RepertoireTrackSeqNumber,
            @OutboundContractID
           
            select 'Get another row for delete'
        END
       
        CLOSE deleteBeneList
        DEALLOCATE cursor deleteBeneList

       
0
 
dfr031260Author Commented:
Put an index on table that was used in cursor and error is no more!
0
 
DarthModCommented:
PAQed with points refunded (50)

DarthMod
Community Support Moderator
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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