February 25, 2008 at 8:08 pm
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
February 27, 2008 at 12:19 pm
Is it not possible then, to use multiple results from a stored procedure in DTS 2000? Cant seem to properly use rs.nextrecordset.
February 27, 2008 at 1:25 pm
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?
February 27, 2008 at 3:21 pm
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.
February 27, 2008 at 4:31 pm
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