Solved

SQL Error 613

Posted on 1997-09-14
1
243 Views
Last Modified: 2008-03-17
Does anyone know what causes a stored procedure to work every other time
and give error 613 when it doesn't?
I've included the system message text below from SQL Books on line. I don't
know how I could be doing what it says.

"613      21      Request made to retrieve more rows from an already completed scan
of object '%.*s' in database '%.*s'."

============ Execution results =========================

********** Get cycle description
********** Build amounts work table
********** Main select into work table
********** Select FROM work table
Msg 613, Level 20, State 0

The SQL Server is terminating this process.

============ Here's the stored procedure ==================

CREATE PROCEDURE sp_test
            @cycle_id char(3)
AS


DECLARE
      @cy_desc varchar(20)


PRINT '********** Get cycle description'

SELECT @cy_desc = cycle_desc
            FROM cycle
            WHERE cycle_id = @cycle_id

/*
** Build work table of all grant fiscal amounts for this cycle - one field
for each term number.
*/
PRINT '********** Build amounts work table'

SELECT
      ga.grant_id,
      grant_term_1_amt =      CASE term_no
                        WHEN 1 THEN fiscal_amount
                        ELSE NULL
                  END,
      grant_term_2_amt =      CASE term_no
                        WHEN 2 THEN fiscal_amount
                        ELSE NULL
                  END,
      grant_term_3_amt =      CASE term_no
                        WHEN 3 THEN fiscal_amount
                        ELSE NULL
                  END,
      grant_term_4_amt =      CASE term_no
                        WHEN 4 THEN fiscal_amount
                        ELSE NULL
                  END
INTO
      #grantamountwork
FROM
      application a,
      grant_amount ga

WHERE
      a.cycle_id = @cycle_id
      AND a.appl_id = ga.grant_id

PRINT '********** Main select into work table'

SELECT
      appl_inst_name = ai.inst_name,
      a.appl_id,
      g.inst_id,
      a.comm_code_id,
      a.cycle_id,
      a.renewal_type_id,
      a.resp_grant_appl_id,
      a.appl_type_id,
      a.req_amount,
      a.commitment_amount,
      a.commitment_removed_reason_id,
      a.req_start_date,
      a.req_end_date,
      investigator_name = ap.last_name + ', ' + ap.first_name,
      inv_last_name = ap.last_name,
      inv_first_name = ap.first_name,
      inv_degree_desc = id.degree_desc,
      a.comm_tab_no,
      cycle_desc = @cy_desc,
      mentorname = mp.last_name + ', ' + mp.first_name,
      invinstname = invinst.inst_name
      
INTO
      #reswork
FROM
      application a,
      person ap,
      person mp,
      degree id,
      institution invinst,
      institution ai,
      grants g
            
WHERE
      a.cycle_id = @cycle_id
      AND a.resp_grant_appl_id = g.grant_id      
      AND ap.person_id = g.investigator_id
      AND id.degree_id =* ap.degree_id
      AND invinst.inst_id =* g.inst_id
      AND g.mentor_id *= mp.person_id
      AND g.inst_id *= ai.inst_id

PRINT '********** Select FROM work table'


SELECT
      aw.appl_inst_name,
      aw.appl_id,
      aw.comm_code_id,
      aw.cycle_id,
      aw.renewal_type_id,
      aw.resp_grant_appl_id,
      aw.appl_type_id,
      aw.req_amount,
      aw.req_start_date,
      aw.req_end_date,
      aw.commitment_amount,
      aw.commitment_removed_reason_id,
      a.project_title,      
      aw.investigator_name,
      aw.inv_last_name,
      aw.inv_first_name,
      aw.inv_degree_desc,
      aw.comm_tab_no,
      aw.cycle_desc,
      aw.invinstname,
      appltypedesc = at.description,
      at.mentor_required,
      aw.mentorname,
      meninstadd1 = madd.street_1,
      meninstadd2 = madd.street_2,
      meninstcity = madd.city,
      meninststate = madd.state_id,
      meninstzip = madd.postal_code,
      meninst_id = madd.inst_id,
      g.grant_id,
      g.begin_date,
      g.end_date,
      g.grant_no,
      g.grant_amount,
      g.commitment_amount,
      gaw.grant_term_1_amt,
      gaw.grant_term_2_amt,
      gaw.grant_term_3_amt,
      gaw.grant_term_4_amt,
      invinstadd1 = iadd.street_1,
      invinstadd2 = iadd.street_2,
      invinstcity = iadd.city,
      invinststate = iadd.state_id,
      invinstzip = iadd.postal_code,
      invinst_id = iadd.inst_id,
      inv_inst_department = idept.dept_name
FROM
      department idept,
      address iadd,
      address madd,
      application a,
      grants g,
      appl_type at,
      #reswork aw,
      #grantamountwork gaw
WHERE
      aw.resp_grant_appl_id = g.grant_id
      AND aw.appl_id = a.appl_id
      AND aw.resp_grant_appl_id = gaw.grant_id
      AND g.investigator_id = iadd.person_id
      AND aw.appl_type_id = at.appl_type_id
      AND g.canceled_reason_id IS NULL
      AND g.mentor_id *= madd.person_id
      AND iadd.dept_id *= idept.dept_id
      AND aw.appl_type_id <> 'ROG'
      AND madd.is_primary = 1
      AND iadd.is_primary = 1
ORDER BY
       aw.comm_tab_no, appltypedesc, aw.inv_last_name, aw.inv_first_name

PRINT '********** DROP work table'
      
DROP TABLE #reswork
DROP TABLE #grantamountwork

GO
0
Comment
Question by:mlangley
1 Comment
 
LVL 4

Accepted Solution

by:
vvk earned 200 total points
ID: 1088953
I think you need to use same alias for one table in request (table address has aliaces iadd,madd) and one of them used as right part of outer join condition.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now