Solved

SQL Error 613

Posted on 1997-09-14
1
245 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

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 …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

808 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