Solved

SQL Error 613

Posted on 1997-09-14
1
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

735 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