Solved

SQL Error 613

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

770 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