Post reply

Running batches in Stored Proc

  • November 7, 2003 at 7:57 am

    #83408

    When I tried to run the stored proc below, only the first select line runs. Any idea why the rest of the lines are skipped.

    =======================================

    create PROCEDURE bsp_file_load

    AS

    set nocount on

    select * from hdrrecord

    select * from lcustrecord

    select * from street_load

    order by proorder

    select * from uload

    order by proorder

    select * from vload

    order by proorder

    set nocount off

    GO

    ====================================

    Thanks.

  • November 7, 2003 at 8:01 am

    #481261

    why don't u try by putting the statements inside a BEGIN --- END block ?

  • November 7, 2003 at 9:25 am

    #481283

    What are you using to get the result datasets? When you run this in QA, can you run each select and obtain results?

    If you are reading the data with a recordset object, you may need to use the appropriate command to get the next recordset (sorry - can't think of the proper command right now )

    Guarddata-

    Edited by - guarddata on 11/07/2003 09:25:13 AM

  • November 7, 2003 at 4:06 pm

    #481335

    Hi,

    The queries run successfully together in QA but when the stored proc is run, only the first select is executed. The output is sent to a text file.

    I will try using the begin and end to see what happens.

  • November 7, 2003 at 4:23 pm

    #481336

    eletuw...when you way the output is sent to a text file, what method are you using to send it? Is this a DTS routine? a query in bcp?

    Guarddata-

  • November 10, 2003 at 4:46 am

    #481388

    If I'm not mistaken Stored Procedures return only one RecordSet. You may need to use the NextRecordset method on the returned Recordset to access multiple RecordSets returned from a Stored Procedure.

     

  • November 10, 2003 at 5:16 am

    #481392

    Thanks all. The BEGIN...END worked.

    The challenge now is to run the stored proc from a job and get the output into a csv file.

  • November 10, 2003 at 5:03 pm

    #481501

    from where you are calling your stored procedure ?

    Is there any configuration changes than the normal settings ? Earlier, i forced to put BEGIN--END and these days its not required in SQL 2000.

    Linto

  • November 10, 2003 at 9:51 pm

    #481534

    If you are executing the procedure from front end then after the 1st select statement is executed the stored procedure skips all the other lines. I would suggest to get the values of the select statement into some variable and then use the print command to display the values.

    Santosh Tiwari

    Directions

    Mumbai - INDIA

    santosh@direct2s.com


    Santosh Tiwari
    Directions
    Mumbai - INDIA
    santosh@direct2s.com

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply