Using DTS to run a stored proc and export to Excel

[cwadams1955]

I have an existing stored procedure which runs 4 concatenated SQL SELECT statements, like this:

EXEC(@SqlState1 + @SqlState2 + @SqlState3 + @SqlState4)

It builds and returns the dataset just fine. But now I have a need to send this dataset to an Excel spreadsheet. I know how to use DTS to export table data, and I've found lots of info on using DTS to export a view or a single SQL statement. But is there an easy way to have DTS run this stored proc and send the resulting dataset to an Excel file? Or would I have to rewrite the procedure so that it does this task (which I'd rather not do, if I can avoid it.) Thanks.

 

[AlexCuse]

you just need to put "exec myProc @param" in the source for your data pump task (assumes you are only returning 1 result set).

Make sure you have nocount on inside the proc as well.

Hope this helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[JackVam]

Yes, for example , when you run the the Export Wizard, you get an option to run a "query" ,is it not possible to encase what you are trying to do into a stored procedure, and then just execute the stored procedure as part of the job?

Jack Vamvas

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[cwadams1955]

Alex: Sorry, you lost me. Where do I do that? I don't see any options in the DTS package that allows me to select a stored proc, just views and tables. It does return just a single dataset, so that should be okay, and I do have COCOUNT set ON.

Jack: The SQL statements are in a stored proc, that's the issue - I can't find where I would put the name of the proc in the DTS job.

I should mention that I'm not really all that familiar with DTS. I've done a few jobs, and I have a few live working examples I can refer to, but none of them reference stored procs for their data source.

 

[AlexCuse]

Are you using the wizard for this, or do you have a data pump task set up to export to excel?

When editing the data pump task, you simply need to check the radio button marked "SQL Query" and add the command there to execute the procedure (make sure your proc has SET NOCOUNT ON at the beginning).

HOpe this helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[cwadams1955]

Got it! Yeah, I had tried it both ways (wizard and manual setup,) but it hadn't clicked that I could put an execute command in the wizard's Query box. Worked great, thanks!

 

[AlexCuse]

Glad you got it working

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page