Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004

    Exclamation Unanswered: Stored Proc retuns no rows

    Please any help would be much appreciated !!

    I have developed many stored procedures which run off tables in my SQL database. The stored procedures take two criteria and return summary data for use in Access db reports. [I take the data via ADO to a mdb].

    I have just written a large sp which takes the criteria amends data in tables and then should produce results using a select statement at the end. The correct data has moved around, but no rows are returned.

    The really odd thing is that rows are returned to grid when run from analyzer, which is where I was developing it. Is this a problem with Access/ADO or something because it is going to drive me crazy....
    Last edited by sqlappboy; 04-28-04 at 11:08.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    What are you using to run the stored procedure?


  3. #3
    Join Date
    Apr 2004
    Thankyou for replying to a newbie ;-)

    At first I tried running it via an Access (ADP) linked to the database, but I have had problems in the past with the maxlocksperfile setting, so I assumed that this could be causing a problem.

    So then I ran it using ADO recordset in VBA. The recordset fails because it [the sp] returns no data at all.

    When the sp is run, all tables are updated correctly, and it runs perfectly. The only problem is that it does not return the results of the SELECT statement which is the final part of the sp.

  4. #4
    Join Date
    Nov 2002
    Can you post the select?

    Can you run the select in QA?

    Do you have the sql server tools installed?

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2004
    1.) An easier select you will not find

    select empno, montha, sorta, d01, d02, d03, d04, d05, d06, d07, d08, d09, d10, d11, d12, d13, d14, d15, d16, d17, d18, d19, d20, d21, d22, d23, d24, d25, d26, d27, d28, d29, d30, d31
    from tbl_emp_abs_matrix order by sorta asc

    2.) the select on its own runs fine in QA - as does the whole SP

    3.) re the tools. I do not think so, I have EM and QA and some other bits I do not use. What should I be looking for ?

  6. #6
    Join Date
    Jan 2004

    Cre... Bla-bla-bla
    set Nocount On

  7. #7
    Join Date
    Apr 2004

    Talking Thankyou

    Works a treat. Thankyou very much !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts