Three results from SP to Excel

  • How would I export three result sets from one sql 2000 SP into three individual named ranges on three Excell worksheets

    (same workbook)

    From sql connection run SP

    Export rs(1) to workbook.sheet1

    Export rs(2) to workbook.sheet2

    Export rs(3) to workbook.sheet3

  • Is it not possible then, to use multiple results from a stored procedure in DTS 2000? Cant seem to properly use rs.nextrecordset.

  • I'm not sure how you'd do that from one single stored proc. As far as I know you'd need three separate sources, each yield one resultset, and three separate destinations (each pointing to a different page on the same Excel file).

    So - you would need to break up your SP into three pieces.

    I would also make sure to force the three step to fire in sequence (and not concurrently.)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • you can return multiple results from sp as

    create sp test as

    select * from a

    select * from b

    select * from c

    .

    .

    Then use rs.nextrecordset to move thru results. The first time you assign execution of sp to recordsewt, you get first results. Then by use rs = rs.nextrecordset for the remainder. Was hoping to use this method in DTS as the sp handles the three queries very effeciantly.

  • Oh - I understand the concept you're looking at - but you're using ADO terminology, and I'm not sure there's a way to leverage that within DTS.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 5 posts - 1 through 4 (of 4 total)

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