Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Error 613

Posted on 1997-09-14
1
Medium Priority
?
250 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 400 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

688 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